Oracle数据库C#代码中UpdateCommand.ExecuteNonQuery()无响应排查

问题描述 #

我编写了一段用于更新Oracle表的C#代码，当执行oracleDataAdapter.UpdateCommand.ExecuteNonQuery();时，程序突然停止运行，既不抛出任何异常，也无法进入finally块。代码如下：

OracleTransaction oracleTransaction = null;
OracleConnection oracleConnection = null;
OracleDataAdapter oracleDataAdapter = null;
OracleCredential oracleCredential = new OracleCredential((string)args["Username"], (SecureString)args["SecurePassword"]);
string CustomerID = (string)args["CustomerID"];
string FromUserID = (string)args["FromUserID"];
XElement ToUserIDsElement = (XElement)args["ToUserIDsElement"];

try
{
    using (oracleConnection = new OracleConnection((string)args["OracleConnectionString"], oracleCredential))
    {
        oracleConnection.Open();
        using (oracleTransaction = oracleConnection.BeginTransaction(IsolationLevel.ReadCommitted))
        {
            string commandText = @"
                            MERGE INTO T_USER_AUTHORIZATION_BASE TGT
                            USING (
                                SELECT  C.CUSTOMER_NAME
                                    ,   U1.USER_NAME AS U1_USER_NAME
                                    ,   UA1.USER_ID AS UA1_USER_ID
                                    ,   UA1.CUSTOMER_ID AS UA1_CUSTOMER_ID
                                    ,   UA1.ENTITY AS UA1_ENTITY
                                    ,   UA1.SIGNATURE_LEVEL AS UA1_SIGNATURE_LEVEL
                                    ,   UA1.INQUIRY AS UA1_INQUIRY
                                    ,   UA1.TRANSACTION_AUTHORIZATION AS UA1_TRANSACTION_AUTHORIZATION
                                    ,   UA1.IS_MANAGE_INTERNAL_DEPARTMENTS AS UA1_IS_MANAGE_INTERNAL_DEPARTMENTS
                                    ,   UA1.IS_CREATE_INTERNAL_DEPARTMENTS AS UA1_IS_CREATE_INTERNAL_DEPARTMENTS
                                    ,   UA1.CUSTOMER_SUPERVISOR AS UA1_CUSTOMER_SUPERVISOR
                                    ,   UA1.IS_AUDIT AS UA1_IS_AUDIT
                                    ,   UA1.INTERNAL_LIMIT AS UA1_INTERNAL_LIMIT
                                    ,   UA1.PURGE AS UA1_PURGE
                                    ,   UA1.STATIC_DATA AS UA1_STATIC_DATA
                                    ,   UA1.BANK_COMMUNICATION AS UA1_BANK_COMMUNICATION
                                    ,   U2.USER_ID AS U2_USER_ID
                                    ,   UA2.USER_ID AS UA2_USER_ID
                                    ,   UA2.CUSTOMER_ID AS UA2_CUSTOMER_ID
                                    ,   UA2.ENTITY AS UA2_ENTITY
                                    ,   UA2.SIGNATURE_LEVEL AS UA2_SIGNATURE_LEVEL
                                    ,   UA2.INQUIRY AS UA2_INQUIRY
                                    ,   UA2.TRANSACTION_AUTHORIZATION AS UA2_TRANSACTION_AUTHORIZATION
                                    ,   UA2.IS_MANAGE_INTERNAL_DEPARTMENTS AS UA2_IS_MANAGE_INTERNAL_DEPARTMENTS
                                    ,   UA2.IS_CREATE_INTERNAL_DEPARTMENTS AS UA2_IS_CREATE_INTERNAL_DEPARTMENTS
                                    ,   UA2.CUSTOMER_SUPERVISOR AS UA2_CUSTOMER_SUPERVISOR
                                    ,   UA2.IS_AUDIT AS UA2_IS_AUDIT
                                    ,   UA2.INTERNAL_LIMIT AS UA2_INTERNAL_LIMIT
                                    ,   UA2.PURGE AS UA2_PURGE
                                    ,   UA2.STATIC_DATA AS UA2_STATIC_DATA
                                    ,   UA2.BANK_COMMUNICATION AS UA2_BANK_COMMUNICATION
                                FROM        T_CUSTOMER_PROFILE_BASE C
                                JOIN        T_USER_PROFILE_BASE U1          ON  C.ENTITY                = U1.ENTITY    
                                                                            AND C.CUSTOMER_ID           = U1.DEFAULT_CUSTOMER_ID
                                JOIN        T_USER_PROFILE_BASE U2          ON  C.ENTITY                = U2.ENTITY
                                                                            AND C.CUSTOMER_ID           = U2.DEFAULT_CUSTOMER_ID
                                JOIN        T_USER_AUTHORIZATION_BASE UA1   ON  C.ENTITY                = UA1.ENTITY
                                                                            AND C.CUSTOMER_ID           = UA1.CUSTOMER_ID
                                                                            AND U1.USER_ID              = UA1.USER_ID    
                                LEFT JOIN   T_USER_AUTHORIZATION_BASE UA2   ON  C.ENTITY                = UA2.ENTITY
                                                                            AND C.CUSTOMER_ID           = UA2.CUSTOMER_ID
                                                                            AND U2.USER_ID              = UA2.USER_ID    
                                WHERE       C.ENTITY        IN ('030')                                                                                                                
                                AND         C.CUSTOMER_ID   IN (:CustomerID)
                                AND         U1.USER_ID      IN (:FromUserID)
                                AND         U2.USER_ID      IN (SELECT IDS.ToUserID FROM XMLTABLE('/ToUserIDs/ToUserID' PASSING XMLTYPE (:ToUserIDsElement) COLUMNS ToUserID VARCHAR(100) PATH '/ToUserID/.') IDS)
                            )   SRC
                            ON (    
                                            SRC.UA1_ENTITY      = TGT.ENTITY
                                        AND SRC.UA1_CUSTOMER_ID = TGT.CUSTOMER_ID
                                        AND SRC.U2_USER_ID      = TGT.USER_ID
                            ) 
                            WHEN MATCHED THEN UPDATE SET    TGT.SIGNATURE_LEVEL                 =   SRC.UA1_SIGNATURE_LEVEL
                                                        ,   TGT.INQUIRY                         =   SRC.UA1_INQUIRY
                                                        ,   TGT.TRANSACTION_AUTHORIZATION       =   SRC.UA1_TRANSACTION_AUTHORIZATION
                                                        ,   TGT.IS_MANAGE_INTERNAL_DEPARTMENTS  =   SRC.UA1_IS_MANAGE_INTERNAL_DEPARTMENTS
                                                        ,   TGT.IS_CREATE_INTERNAL_DEPARTMENTS  =   SRC.UA1_IS_CREATE_INTERNAL_DEPARTMENTS
                                                        ,   TGT.CUSTOMER_SUPERVISOR             =   SRC.UA1_CUSTOMER_SUPERVISOR
                                                        ,   TGT.IS_AUDIT                        =   SRC.UA1_IS_AUDIT
                                                        ,   TGT.INTERNAL_LIMIT                  =   SRC.UA1_INTERNAL_LIMIT
                                                        ,   TGT.PURGE                           =   SRC.UA1_PURGE
                                                        ,   TGT.STATIC_DATA                     =   SRC.UA1_STATIC_DATA
                                                        ,   TGT.BANK_COMMUNICATION              =   SRC.UA1_BANK_COMMUNICATION
                            WHEN NOT MATCHED THEN INSERT(   ENTITY
                                                        ,   USER_ID
                                                        ,   CUSTOMER_ID
                                                        ,   SIGNATURE_LEVEL
                                                        ,   INQUIRY
                                                        ,   TRANSACTION_AUTHORIZATION
                                                        ,   IS_MANAGE_INTERNAL_DEPARTMENTS
                                                        ,   IS_CREATE_INTERNAL_DEPARTMENTS
                                                        ,   CUSTOMER_SUPERVISOR
                                                        ,   IS_AUDIT
                                                        ,   INTERNAL_LIMIT
                                                        ,   PURGE
                                                        ,   STATIC_DATA
                                                        ,   BANK_COMMUNICATION
                                                        )
                                                  VALUES(   SRC.UA1_ENTITY
                                                        ,   SRC.U2_USER_ID
                                                        ,   SRC.UA1_CUSTOMER_ID
                                                        ,   SRC.UA1_SIGNATURE_LEVEL
                                                        ,   SRC.UA1_INQUIRY
                                                        ,   SRC.UA1_TRANSACTION_AUTHORIZATION
                                                        ,   SRC.UA1_IS_MANAGE_INTERNAL_DEPARTMENTS
                                                        ,   SRC.UA1_IS_CREATE_INTERNAL_DEPARTMENTS
                                                        ,   SRC.UA1_CUSTOMER_SUPERVISOR
                                                        ,   SRC.UA1_IS_AUDIT
                                                        ,   SRC.UA1_INTERNAL_LIMIT
                                                        ,   SRC.UA1_PURGE
                                                        ,   SRC.UA1_STATIC_DATA
                                                        ,   SRC.UA1_BANK_COMMUNICATION
                                                        )";
            using (oracleDataAdapter = new OracleDataAdapter())
            {
                oracleDataAdapter.UpdateCommand = new OracleCommand(commandText, oracleConnection);
                oracleDataAdapter.UpdateCommand.Transaction = oracleTransaction;
                oracleDataAdapter.UpdateCommand.Parameters.Add(new OracleParameter(":CustomerID", OracleDbType.NVarchar2)).Value = CustomerID;
                oracleDataAdapter.UpdateCommand.Parameters.Add(new OracleParameter(":FromUserID", OracleDbType.NVarchar2)).Value = FromUserID;
                oracleDataAdapter.UpdateCommand.Parameters.Add(new OracleParameter(":ToUserIDsElement", OracleDbType.NVarchar2)).Value = ToUserIDsElement.ToString();
                oracleDataAdapter.UpdateCommand.ExecuteNonQuery();
            }
            oracleTransaction.Commit();
            return true;
        }
    }
}
catch (Exception e1)
{
    oracleTransaction.Rollback();
    MessageBox.Show($"{MethodBase.GetCurrentMethod().DeclaringType.Name} : {MethodBase.GetCurrentMethod().Name} : {e1.Message}");
    return false;
}
finally
{
    if (oracleConnection.State == ConnectionState.Open)
    {
        oracleTransaction.Dispose();
        oracleConnection.Close();
        oracleDataAdapter.UpdateCommand.Dispose();
        oracleDataAdapter.Dispose();
    }
}

可能的原因及排查方向 #

• 数据库阻塞或死锁：MERGE语句涉及多表关联查询和写操作，若目标表或关联表被其他事务持有锁，当前会话会进入无限等待状态。可通过Oracle系统视图v$session和v$lock查询是否存在阻塞会话，或用ALTER SYSTEM KILL SESSION终止阻塞进程。另外，若操作数据量过大，MERGE执行时间过长也会表现为程序无响应。

• XML参数解析异常：代码将XElement直接转为字符串传入:ToUserIDsElement参数，Oracle的XMLTYPE解析若遇到格式不合法的XML，可能导致内部资源耗尽或无限循环。建议单独在Oracle客户端执行XML解析子查询，验证是否能正常返回结果。

• ODP.NET驱动BUG：旧版本的Oracle数据访问组件（ODP.NET）可能存在处理MERGE语句的兼容性问题，导致程序卡死。尝试升级到最新的ODP.NET Managed Driver，同时注意：此处无需使用OracleDataAdapter，直接用OracleCommand执行SQL更高效，也能避免DataAdapter带来的额外资源开销。

• UI线程阻塞：如果这段代码在UI线程执行，长时间的数据库操作会导致UI线程挂起，看起来像程序停止。应将数据库操作放到异步线程中，使用ExecuteNonQueryAsync()方法避免阻塞主线程。

• 事务与资源释放逻辑缺陷：finally块中仅在连接状态为Open时才释放资源，若程序卡在ExecuteNonQuery，连接可能处于挂起状态，导致资源无法正常释放；此外catch块中直接调用Rollback()未判断事务状态，若事务已失效会引发新异常，但这不会直接导致卡死，属于代码健壮性问题。

代码优化建议 #

• 移除不必要的OracleDataAdapter，直接使用OracleCommand执行MERGE语句：

using (var cmd = new OracleCommand(commandText, oracleConnection, oracleTransaction))
{
    cmd.Parameters.Add(new OracleParameter(":CustomerID", OracleDbType.NVarchar2)).Value = CustomerID;
    cmd.Parameters.Add(new OracleParameter(":FromUserID", OracleDbType.NVarchar2)).Value = FromUserID;
    cmd.Parameters.Add(new OracleParameter(":ToUserIDsElement", OracleDbType.NVarchar2)).Value = ToUserIDsElement.ToString();
    cmd.ExecuteNonQuery();
}

• 简化资源释放逻辑：using语句会自动释放oracleConnection、oracleTransaction和OracleCommand，无需在finally块中手动Dispose/Close。
• 完善catch块的事务回滚逻辑：

catch (Exception e1)
{
    if (oracleTransaction != null && oracleTransaction.Connection != null)
    {
        try
        {
            oracleTransaction.Rollback();
        }
        catch (Exception rollbackEx)
        {
            // 记录回滚异常日志
        }
    }
    MessageBox.Show($"{MethodBase.GetCurrentMethod().DeclaringType.Name} : {MethodBase.GetCurrentMethod().Name} : {e1.Message}");
    return false;
}

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