-------+嘿，请不要问数据库服务器有什么不同。不管怎样，他们都表现出同样的问题。应用程序正在运行.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语句，它容易导致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;
        }

我省略了上面的很多代码（例如结束部分）。请忽略缺少的部分 using {} 声明（我99%确定所有连接都已关闭。）
在不保存数据的时间内，将在中显示以下内容： select * from information_schema.innodb_trx ```

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_query: NULL` ... 我有一个脚本，每0.1秒打印一次这个表，它只会显示 `trx_query: NULL` 当数据未保存到数据库时（但报告它已保存）
在这段时间里 `show engine innodb status` 在事务部分生成此。。。

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

我没有看到任何僵局。
我试过的。。。
由于db的操作系统和版本不同，我很难相信这是mysql（mariadb）的问题。
我已经尝试将mysql连接器dll从6.9.4更改为6.9.10和6.9.12
我已经将应用程序设置为在iis中每1小时回收一次应用程序池，问题在回收1分钟后出现。
我已经把那本书注解掉了 `.beginTransaction()` 填充，以便在每次 `ExecuteNonQuery()` 有人有什么想法吗？
谢谢您

**edit here是记录到文件中的查询日志（为了隐私起见，我编辑了sql以删除表/列名）

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

疯狂地看到这么多的回滚。。。是什么导致了这一切。在我下面的评论中，我在app\u server>db\u server的tcpdump中显示了rollback，因此不是db发起这些回滚。

SELECT  COLUMN
    FROM  TABLE
    WHERE  COLUMN IN (
        SELECT  COLUMN
            FROM  TABLE
            WHERE  STATUSCODE = 3
                     )
    ORDER BY  COLUMN

-->

SELECT column
    FROM table
    WHERE statuscode = 3
    ORDER BY column;

（如果真的有两个不同的表格，那么请在你的问题中说明这一点。在这种情况下，改进后的查询将需要 JOIN .)

SELECT  COLUMN
    FROM  TABLE
    GROUP BY  COLUMN
    ORDER BY  COLUMN

-->

SELECT DISTINCT column
    FROM table
    ORDER BY column

和

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

-->

SELECT column AS Status
    FROM table
    WHERE column NOT IN (1,2,4,10,11,12,13,15)
    ORDER BY rec_num

和

Why do this; the answer is obviously 'DB_NAME':

SELECT column FROM table WHERE column = 'DB_NAME'.

14条答案

按热度按时间

6uxekuva1#

-----+

赞(0）回复(0）举报 2021-06-18

6xfqseft2#

fzwojiic3#

----+另一个是软呢帽 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+

yruzcnhs4#

b1payxdu5#

tjrkku2a6#

-------+ | Variable_name | Value | +

wa7juj8i7#

g6ll5ycj8#

好吧，我想我找到问题了。
应用程序经常调用的一个非常常见的存储过程中包含以下语句。。。

START TRANSACTION;
SET AUTOCOMMIT = 0;

我从另一个帖子里得知 SET AUTOCOMMIT = 0 是不必要的（很可能是我的罪魁祸首）
在我运行sp之前， show variables like 'autocommit' 返回 ON sp运行后，它说 OFF 我的理论是称为sp的连接将其会话变量autocommit设置为 OFF 然后回到游泳池。稍后，当连接从池中获取会话时，它开始回滚每个语句，因为 autocommit 仍然关闭，没有明确的 COMMIT; 曾经被派过。
有人有这方面的经验吗？