.NET6 Razor页面跨请求保持Oracle连接与事务的实现难题

首先明确核心原则：数据库连接和事务对象无法跨HTTP请求存储，这类对象不可序列化，且HTTP的无状态特性导致每个请求的上下文完全独立，强行存储会引发资源泄漏、对象失效等问题。以下是针对你的场景的可行方案：

方案1：调整业务逻辑（推荐） #

这是最安全、性能最优的方案，避免长时间持有事务引发的锁竞争和资源问题：

• 打开模态框时，OnPostGetOrder仅读取展示所需的数据，不启动事务
• 用户在模态框内完成操作并确认提交时，触发OnPostSubmitOrder请求：
  1. 创建OracleConnection并打开
  2. 启动OracleTransaction
  3. 执行所有数据库操作
  4. 提交事务并关闭连接
• 用户取消/关闭模态框时，无需执行任何数据库操作，自然无需回滚事务

方案2：使用SignalR维持长连接管理事务（适合必须跨请求保事务的场景） #

通过SignalR建立前端与后端的长连接，将事务与前端连接绑定：

1. 配置SignalR服务：

   // Program.cs
   builder.Services.AddSignalR();
   app.MapHub<TransactionHub>("/transactionHub");
   

2. 创建事务管理Hub：

   public class TransactionHub : Hub
   {
       private static readonly Dictionary<string, (OracleConnection Connection, OracleTransaction Transaction)> _activeTransactions = new();
   
       public async Task StartTransaction(string connectionString)
       {
           var connection = new OracleConnection(connectionString);
           await connection.OpenAsync();
           var transaction = connection.BeginTransaction();
           _activeTransactions[Context.ConnectionId] = (connection, transaction);
       }
   
       public async Task RollbackTransaction()
       {
           if (_activeTransactions.TryGetValue(Context.ConnectionId, out var data))
           {
               try
               {
                   await data.Transaction.RollbackAsync();
               }
               finally
               {
                   await data.Connection.CloseAsync();
                   await data.Connection.DisposeAsync();
                   _activeTransactions.Remove(Context.ConnectionId);
               }
           }
       }
   
       // 可扩展：添加模态框内执行数据库操作的方法
       public async Task ExecuteSql(string sql)
       {
           if (_activeTransactions.TryGetValue(Context.ConnectionId, out var data))
           {
               using var command = data.Connection.CreateCommand();
               command.Transaction = data.Transaction;
               command.CommandText = sql;
               await command.ExecuteNonQueryAsync();
           }
       }
   
       public override async Task OnDisconnectedAsync(Exception? exception)
       {
           // 连接断开时自动回滚事务
           await RollbackTransaction();
           await base.OnDisconnectedAsync(exception);
       }
   }
   

3. 前端绑定SignalR逻辑：

   <script src="~/lib/signalr/dist/browser/signalr.js"></script>
   <script>
       const connection = new signalR.HubConnectionBuilder()
           .withUrl("/transactionHub")
           .build();
   
       // 打开模态框时启动事务
       document.getElementById('openModalBtn').addEventListener('click', async () => {
           await connection.start();
           await connection.invoke("StartTransaction", "@Model.ConnectionString");
           // 执行打开模态框的逻辑
       });
   
       // 关闭模态框时回滚事务
       document.getElementById('closeModalBtn').addEventListener('click', async () => {
           await connection.invoke("RollbackTransaction");
           await connection.stop();
           // 执行关闭模态框的逻辑
       });
   </script>
   

方案3：Oracle会话级事务标识（依赖Oracle特性） #

通过Oracle系统视图关联事务，需数据库用户具备查询系统视图的权限：

1. OnPostGetOrder中存储事务会话标识：

   public async Task<IActionResult> OnPostGetOrder()
   {
       var connectionString = Configuration.GetConnectionString("Oracle");
       var connection = new OracleConnection(connectionString);
       await connection.OpenAsync();
       var transaction = connection.BeginTransaction();
   
       // 获取Oracle会话ID
       using var cmd = connection.CreateCommand();
       cmd.Transaction = transaction;
       cmd.CommandText = "SELECT SYS_CONTEXT('USERENV','SID') FROM DUAL";
       var sid = cmd.ExecuteScalar().ToString();
   
       // 将可序列化信息存入Session
       HttpContext.Session.SetString("OracleConnStr", connectionString);
       HttpContext.Session.SetString("OracleTransactionSID", sid);
   
       return new JsonResult(new { success = true, data = ... });
   }
   

2. OnPostCloseModal中终止会话回滚事务：

   public async Task<IActionResult> OnPostCloseModal()
   {
       var connStr = HttpContext.Session.GetString("OracleConnStr");
       var sid = HttpContext.Session.GetString("OracleTransactionSID");
   
       if (!string.IsNullOrEmpty(connStr) && !string.IsNullOrEmpty(sid))
       {
           using var connection = new OracleConnection(connStr);
           await connection.OpenAsync();
   
           // 查询会话是否存在未提交事务
           using var checkCmd = connection.CreateCommand();
           checkCmd.CommandText = "SELECT COUNT(*) FROM V$TRANSACTION WHERE SES_ADDR IN (SELECT SADDR FROM V$SESSION WHERE SID = :sid)";
           checkCmd.Parameters.Add("sid", OracleDbType.Varchar2).Value = sid;
           var hasTransaction = Convert.ToInt32(checkCmd.ExecuteScalar()) > 0;
   
           if (hasTransaction)
           {
               // 获取会话Serial#并终止会话（自动回滚事务）
               using var serialCmd = connection.CreateCommand();
               serialCmd.CommandText = "SELECT SERIAL# FROM V$SESSION WHERE SID = :sid";
               serialCmd.Parameters.Add("sid", OracleDbType.Varchar2).Value = sid;
               var serial = serialCmd.ExecuteScalar().ToString();
   
               using var killCmd = connection.CreateCommand();
               killCmd.CommandText = $"ALTER SYSTEM KILL SESSION '{sid},{serial}'";
               await killCmd.ExecuteNonQueryAsync();
           }
   
           // 清理Session
           HttpContext.Session.Remove("OracleConnStr");
           HttpContext.Session.Remove("OracleTransactionSID");
       }
   
       return new JsonResult(new { success = true });
   }
   

内容的提问来源于stack exchange，提问作者mofoGasy