即使 MysqlClient 返回成功并更新行数，生产中的间歇性(在本地、开发或 UAT 环境中不可重复)数据也不会保存到数据库中。

用于生产的应用服务器是 IIS 7 on Windows Server 2008
此应用程序服务器与 2 个独立的数据库服务器进行通信。

Ubuntu 上的一个 Linux servername 4.15.0-23-generic #25-Ubuntu SMP Wed May 23 18:02:16 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux +-------------------------+----------------------------------+ | Variable_name | Value | +-------------------------+----------------------------------+ | innodb_version | 5.6.39-83.1 | | protocol_version | 10 | | slave_type_conversions | | | version | 10.1.34-MariaDB-0ubuntu0.18.04.1 | | version_comment | Ubuntu 18.04 | | version_compile_machine | x86_64 | | version_compile_os | debian-linux-gnu | | version_malloc_library | system jemalloc | | version_ssl_library | YaSSL 2.4.4 | | wsrep_patch_version | wsrep_25.23 | +-------------------------+----------------------------------+
另一个在 Fedora 上 Linux servername 4.8.13-100.fc23.x86_64 #1 SMP Fri Dec 9 14:51:40 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux +-------------------------+-----------------+ | Variable_name | Value | +-------------------------+-----------------+ | innodb_version | 5.6.32-79.0 | | protocol_version | 10 | | slave_type_conversions | | | version | 10.0.28-MariaDB | | version_comment | MariaDB Server | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_malloc_library | system | +-------------------------+-----------------+
嘿，请不要询问截然不同的数据库服务器。无论如何，他们都表现出相同的问题。

该应用程序位于 .NET 4.5 中，并使用 MysqlConnector Mysql.Data dll 6.9.4 与两个数据库进行通信。

偶尔(在重负载下(就系统通常获得的内容而言)；大约 25 个并发用户)系统将开始不保存对数据库的更改，即使应用程序从诸如 int x = Sqlcmd.ExecuteNonQuery(); 的代码中获得成功，其中 x 是数字已更新的行数。

这将发生在非常基本的 Mysql 更新中，例如
MySqlConnection conn = new MySqlConnection(TheConnectionString()); conn.Open(); try { string Query = "DELETE FROM A_TABLE WHERE USERID = '" + UserID + "'"; MySqlCommand Sqlcmd = new MySqlCommand(Query, conn); Sqlcmd.CommandText = Query; Sqlcmd.ExecuteNonQuery(); } finally { if (conn != null) { conn.Close(); conn.Dispose(); } }
请忽略那些很容易出现sql注入(inject)的明显写得不好的sql语句。

其他数据库交互方式(使用事务)也表现出相同的行为。

    //Create and Instantiate the Connection
        sqlConnection = new MySqlConnection(strConnect);
        sqlConnection.Open();

        //With Transaction
        if (bWithTrans == true)
        {
            sqlTransaction = sqlConnection.BeginTransaction();
            //sqlTransaction.IsolationLevel = IsolationLevel.
            bRollBack = false; // Reset indicator
        }

        sqlCommand = new MySqlCommand(qryString, sqlConnection);
        sqlCommand.CommandText = qryString;

        //With Transaction
        if (bWithTrans == true)
        {
            sqlCommand.Transaction = sqlTransaction;
        }
...
...
       if (IsInTransaction())
        {
            if (bRollBack == true)
            {
                sqlTransaction.Rollback();
            }
            else
            {
                sqlTransaction.Commit();
            }
            sqlTransaction.Connection.Close();
            sqlTransaction.Connection.Dispose();
            sqlTransaction = null;
        }

*************************** 1. row ***************************
                    trx_id: 302303150
                 trx_state: RUNNING
               trx_started: 2018-09-27 08:56:45
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 117343
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 360
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 1. row ***************************
                    trx_id: 302303150
                 trx_state: RUNNING
               trx_started: 2018-09-27 08:56:45
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 117343
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 360
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

------------
TRANSACTIONS
------------
Trx id counter 147254697
Purge done for trx's n:o < 147254674 undo n:o < 0 state: running but idle
History list length 30
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 222904, OS thread handle 0x7f7a6e08b700, query id 617593737 localhost root init
show engine innodb status
---TRANSACTION 147254688, not started
MySQL thread id 222902, OS thread handle 0x7f7a23a5f700, query id 617593732 10.22.18.39 DB_NAME
---TRANSACTION 147254696, not started
MySQL thread id 222901, OS thread handle 0x7f7a239c9700, query id 617593736 10.22.18.39 DB_NAME
---TRANSACTION 147254644, not started
MySQL thread id 222900, OS thread handle 0x7f7a6e027700, query id 617593526 10.22.18.39 DB_NAME
---TRANSACTION 147254684, not started
MySQL thread id 222897, OS thread handle 0x7f7a6b4e9700, query id 617593709 10.22.18.39 DB_NAME
---TRANSACTION 147240473, not started
MySQL thread id 126445, OS thread handle 0x7f7a23af5700, query id 617593614 10.22.18.41 DB_NAME
---TRANSACTION 84024323, not started
MySQL thread id 1, OS thread handle 0x7f7a6e185700, query id 0 Waiting for background binlog tasks
---TRANSACTION 147254695, ACTIVE 1 sec fetching rows
mysql tables in use 1, locked 0
MySQL thread id 222898, OS thread handle 0x7f7a239fb700, query id 617593734 10.22.18.39 DB_NAME Sending data
SELECT COUNT(*) FROM TABLE I'M HIDING FOR PRIVACY
Trx read view will not see trx with id >= 147254696, sees < 147254696
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC

245133 Query    ROLLBACK
                245671 Connect  root@localhost as anonymous on
                245671 Query    select * from information_schema.innodb_trx
                244093 Query    ROLLBACK
                245671 Quit
                245133 Init DB  DB_NAME
                245133 Query    SELECT `DESC` as Status FROM TABLE WHERE REC_NUM != 2 ORDER BY `REC_NUM`
                245133 Query    ROLLBACK
                244093 Init DB  DB_NAME
                244093 Query    SELECT COLUMN FROM TABLE WHERE COLUMN IN (SELECT COLUMN FROM TABLE WHERE STATUSCODE = 3) ORDER BY COLUMN
                244093 Query    ROLLBACK
                245133 Init DB  DB_NAME
                245133 Query    SELECT COLUMN FROM TABLE GROUP BY COLUMN ORDER BY COLUMN
                245133 Query    ROLLBACK
                244093 Init DB  DB_NAME
                244093 Query    SELECT COLUMN as Status FROM TABLE WHERE COLUMN <> 1 AND COLUMN <> 2 AND COLUMN <> 4 AND COLUMN <> 10 AND COLUMN <> 11
AND COLUMN <> 12 AND COLUMN <> 13  AND COLUMN <> 15 ORDER BY REC_NUM
                244093 Query    ROLLBACK
                245133 Init DB  DB_NAME
                245133 Query    SELECT COLUMN FROM TABLE WHERE COLUMN = 'DB_NAME'
                245133 Query    ROLLBACK
                245248 Query    ROLLBACK
                244093 Init DB  DB_NAME
                245133 Init DB  DB_NAME

最佳答案

好的，我相信我发现了问题。

应用程序经常调用的一个非常常见的存储过程中有这个语句......

START TRANSACTION;
SET AUTOCOMMIT = 0;

关于C# .NET MySQL (MariaDB) 不保存数据但返回成功，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/52542197/