• my38_MySQL事务知识点零记


    从innodb中查看事务信息

    show engine innodb statusG;

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

    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 5G;
    *************************** 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

  • 相关阅读:
    2017/3/27 morning
    2017/3/24 afternoon
    2017/3/24 morning
    2017/3/21 afternoon
    2017/3/21 morning
    2017/3/20 afternoon
    2017/3/20 morning
    2017/3/16 afternoon
    2017/3/16 morning
    2017/3/15afternoon
  • 原文地址:https://www.cnblogs.com/perfei/p/11137050.html
Copyright © 2020-2023  润新知