my38_MySQL事务知识点零记-优快云博客

本文深入解析MySQL InnoDB存储引擎中事务的运行机制，包括事务状态、锁结构、行锁数量及undo日志等关键概念。通过具体示例，展示了不同事务下MySQL线程ID、用户、操作状态及资源占用情况，揭示了事务与行锁之间的关系。

从innodb中查看事务信息

show engine innodb status\G;

------------

TRANSACTIONS
------------
Trx id counter 3153146
Purge done for trx's n:o < 3143722 undo n:o < 0 state: running but idle
History list length 31
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421182442263040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421182442260304, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 3153145, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 936, OS thread handle 139706768389888, query id 9470005 localhost 127.0.0.1 root query end
insert into test(tid,tname,tvalue) values(i,'aaabbb',concat('有张有驰有分寸',i))
---TRANSACTION 3142243, ACTIVE 3 sec fetching rows
mysql tables in use 1, locked 1
2405 lock struct(s), heap size 286928, 544898 row lock(s), undo log entries 542495
MySQL thread id 941, OS thread handle 139706768119552, query id 9437308 localhost 127.0.0.1 root updating
delete from test where tid < 717337

MySQL thread对应 show full processlist的ID，即MySQL线程ID，常说的应用到MySQL的连接，一个连接可以运行多个事务；

比如thread id 936里面依次N个insert语句，每个语句都是一个事务，他们由root用户执行，当前的状态是query end

每个insert 语句占用一个lock struct，有一个undo log entry

下面的事务是delete语句

mysql> delete from test where tid < 717337 ;
Query OK, 1697989 rows affected (12.68 sec)

它对应的MySQL线程为941，由root用户执行，状态为updating；占用2405个lock struct，有54万个行锁，54万个undo log entries，实际删除数据169万行；

tid上没有索引，应该锁全表，那么不是应该全表有多少行记录就会有多少个行锁吗？为什么删除的数据量有169万，但行锁却只有54万？

现在再重试一下

mysql> select count(*) from test where tid < 2000000;
+----------+
| count(*) |
+----------+
|  1282663 |
+----------+
1 row in set (1.03 sec)

mysql> delete from test where tid < 2000000;
Query OK, 1282663 rows affected (11.56 sec)

mysql> select * from information_schema.innodb_trx order by trx_started desc limit 5\G;
*************************** 1. row ***************************
                    trx_id: 5985123
                 trx_state: RUNNING
               trx_started: 2019-07-05 10:57:35
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 2
       trx_mysql_thread_id: 936
                 trx_query: insert into test(tid,tname,tvalue) values(i,'aaabbb',concat('有张有驰有分寸',i))
       trx_operation_state: NULL
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 1
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 1
   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: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 5979390
                 trx_state: RUNNING
               trx_started: 2019-07-05 10:57:33
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 287724
       trx_mysql_thread_id: 944
                 trx_query: delete from test where tid < 5000000
       trx_operation_state: fetching rows
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 1293
     trx_lock_memory_bytes: 155856
           trx_rows_locked: 287722
         trx_rows_modified: 286431
   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: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)

------------
TRANSACTIONS
------------
Trx id counter 5990951
Purge done for trx's n:o < 5981847 undo n:o < 0 state: running but idle
History list length 5
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421182442263040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421182442260304, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 5990950, ACTIVE (PREPARED) 0 sec
mysql tables in use 1, locked 1
1 lock struct(s), heap size 1136, 0 row lock(s), undo log entries 1
MySQL thread id 936, OS thread handle 139706768389888, query id 17983264 localhost 127.0.0.1 root query end
insert into test(tid,tname,tvalue) values(i,'aaabbb',concat('有张有驰有分寸',i))
---TRANSACTION 5979390, ACTIVE 4 sec fetching rows
mysql tables in use 1, locked 1
2820 lock struct(s), heap size 319696, 628384 row lock(s), undo log entries 625566
MySQL thread id 944, OS thread handle 139706900186880, query id 17948592 localhost 127.0.0.1 root updating
delete from test where tid < 5000000
--------

实际上删除128万行记录，通过information_schema.innodb_trx查看只有28万个行锁，通过innodb status查看有62万个行锁

最后一行记录当前innodb每秒处理多少个行记录

--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=15950, Main thread ID=139706813634304, state: sleeping
Number of rows inserted 6040828, updated 104, deleted 3680663, read 24882106
3107.91 inserts/s, 0.00 updates/s, 18899.79 deletes/s, 66695.26 reads/s

转载于:https://www.cnblogs.com/perfei/p/11137050.html