mysql innodb lock机制原理

2024-07-24 12:34:57

字体：大中小

来源：转载

供稿：网友

mysql innodb lock机制原理：

　　一.lock 和 latch
　　latch 一般称为闩锁，目的是用来保证并发线程操作临界资源的正确性，无死锁检测机制。分为：mutex(互斥量)，rwlock(读写锁)
　　lock 的对象是事务，用来锁定的数据库中的对象。

　　二.lock 种类
　　1.行级锁
　　共享锁(S lock)：允许事务读取一行数据
　　排他锁(X lock)：允许事务修改或删除一行数据
　　2.表级锁
　　意向共享锁(IS lock):在对行加S锁之前，先对其表追加IS锁
　　意向排他锁(IX lock):在对行加X锁之前，先对其表追加IX锁

　　三.锁的应用场景
　　1.一致性非锁定读

　　2.一致性锁定读
　　通过select * from table for update; 或 select * from table lock in share mode; 来锁定读取数据，在数据读取过程中其他事务不能修改该数据。

　　3.自增长与锁
　　含有自增长值的表，都有一个自增长计数器，当对该表进行插入操作时，执行如下语句来得到计数器的值。
　　select max(auto_inc_col) from table for update;
　　该锁不是事务完成之后才释放，而是insert命令执行完成后就释放该锁。

　　mysql5.1.22之前，该模式对于有自增值列的表的并发插入性能较差。
　　mysql5.1.22开始，innodb提供了一种轻量级的互斥量的自增长实现机制，这种机制大大提高了自增长值的插入性能。
　　相关参数，innodb_autoinc_lock_mode,默认值为1

　　4.外键和锁
　　在innodb存储引擎下，外键列如果没有显式的建立index，mysql会为该列自动添加index，避免发生表锁。
　　对于外键值的插入和更新，会先select父表，但该select操作并不是一致性非锁定读，而是一致性锁定读（对父表追加S锁）。因此当父表被其他事务加上X锁时，子表的操作会被阻塞。

　　--主表
　　CREATE TABLE `wwj`.`t1` (
　　 `deptno` INT NOT NULL,
　　 `deptname` VARCHAR(45) NOT NULL,
　　 `address` VARCHAR(45) NOT NULL,
　　 PRIMARY KEY (`deptno`));

　　--子表
　　 CREATE TABLE `wwj`.`t2` (
　　 `empno` INT NOT NULL,
　　 `empname` VARCHAR(45) NOT NULL,
　　 `age` INT NOT NULL,
　　 `deptno` INT NOT NULL,
　　 PRIMARY KEY (`empno`),
　　 INDEX `deptno_idx` (`deptno` ASC),
　　 CONSTRAINT `deptno`
　　    FOREIGN KEY (`deptno`)
　　    REFERENCES `wwj`.`t1` (`deptno`)
　　    ON DELETE NO ACTION
　　    ON UPDATE NO ACTION);

　　insert into wwj.t1 values(1,'it','北京');
　　insert into wwj.t1 values(2,'product','天津');
　　insert into wwj.t1 values(3,'haha','上海');
　　mysql innodb lock机制原理

　　mysql> select * from information_schema.innodb_locks/G;
　　*************************** 1. row ***************************
　　lock_id: 1303:26:3:2
　　lock_trx_id: 1303
　　lock_mode: S
　　lock_type: RECORD
　　lock_table: `wwj`.`t1`
　　lock_index: PRIMARY
　　lock_space: 26
　　lock_page: 3
　　lock_rec: 2
　　lock_data: 1
　　*************************** 2. row ***************************
　　lock_id: 1298:26:3:2
　　lock_trx_id: 1298
　　lock_mode: X
　　lock_type: RECORD
　　lock_table: `wwj`.`t1`
　　lock_index: PRIMARY
　　lock_space: 26
　　lock_page: 3
　　lock_rec: 2
　　lock_data: 1
　　2 rows in set, 1 warning (0.00 sec)

　　四.锁的算法
　　行锁的三种算法：
　　1.record lock
　　单个记录上的锁

　　2.gap lock
　　间隙锁，锁定一个范围，但不包含记录本身

　　3.next-key lock
　　record lock+gap lock 锁定一个范围，但不包含记录本身

　　--场景模拟
　　CREATE TABLE `wwj`.`t3` (
　　 `idt3` INT NOT NULL,
　　 `idt4` INT NOT NULL,
　　 PRIMARY KEY (`idt3`),
　　 INDEX `idx-1` (`idt4` ASC));

　　 insert into wwj.t3 values(1,10);
　　 insert into wwj.t3 values(3,30);
　　 insert into wwj.t3 values(5,50);

　　mysql> select * from wwj.t3;
　　+------+------+
　　| idt3 | idt4 |
　　+------+------+
　　| 1 | 10 |
　　| 3 | 30 |
　　| 5 | 50 |
　　+------+------+

　　因为idt3上有唯一索引，因此锁定的只是idt3=3这个值，而不是(1,3)这个范围，即锁定由next-key lock降级为record lock

　　五.一条sql的加锁范围
　　对于各种情况下加锁的分析
　　mysql> show full processlist;
　　+----+------+-----------+------+---------+------+----------+-----------------------+
　　| Id | User | Host | db | Command | Time | State | Info |
　　+----+------+-----------+------+---------+------+----------+-----------------------+
　　| 11 | root | localhost | NULL | Sleep | 99 | | NULL |
　　| 12 | root | localhost | NULL | Sleep | 81 | | NULL |
　　| 13 | root | localhost | NULL | Query | 0 | starting | show full processlist |
　　+----+------+-----------+------+---------+------+----------+-----------------------+
　　杀掉线程:
　　KILL [CONNECTION | QUERY] thread_id
　　kill 13 --杀掉线程连接
　　kill query 13 --杀掉正在执行的语句，保留连接

　　mysql> show engine innodb status/G;
　　------------
　　TRANSACTIONS
　　------------
　　Trx id counter 1296
　　Purge done for trx's n:o < 1294 undo n:o < 0 state: running but idle
　　History list length 2
　　LIST OF TRANSACTIONS FOR EACH SESSION:
　　---TRANSACTION 421324408397424, not started
　　0 lock struct(s), heap size 1136, 0 row lock(s)
　　---TRANSACTION 1295, ACTIVE 396 sec inserting
　　mysql tables in use 1, locked 1
　　LOCK WAIT 2 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
　　MySQL thread id 12, OS thread handle 139848225883904, query id 61 localhost root executing
　　insert into wwj.t3 select 4,20
　　------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
　　RECORD LOCKS space id 26 page no 4 n bits 72 index idx-1 of table `wwj`.`t3` trx id 1295 lock_mode X locks gap before rec insert intention waiting
　　Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
　　 0: len 4; hex 8000001e; asc ;;
　　 1: len 4; hex 80000003; asc ;;

　　---------------------
　　TRANSACTION 1294, ACTIVE 449 sec
　　4 lock struct(s), heap size 1136, 3 row lock(s)
　　MySQL thread id 11, OS thread handle 139848226150144, query id 50 localhost root
　　Trx read view will not see trx with id >= 1294, sees < 1294

　　mysql> select * from information_schema.INNODB_LOCK_WAITS;
　　+-------------------+-------------------+-----------------+------------------+
　　| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
　　+-------------------+-------------------+-----------------+------------------+
　　| 1302 | 1302:26:4:3 | 1301 | 1301:26:4:3 |
　　+-------------------+-------------------+-----------------+------------------+
　　1 row in set, 1 warning (0.00 sec)

　　mysql> select lock_id,lock_trx_id,lock_mode,lock_type,lock_table,lock_index from information_schema.INNODB_LOCKs;
　　+-------------+-------------+-----------+-----------+------------+------------+
　　| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index |
　　+-------------+-------------+-----------+-----------+------------+------------+
　　| 1302:26:4:3 | 1302 | X,GAP | RECORD | `wwj`.`t3` | idx-1 |
　　| 1301:26:4:3 | 1301 | X | RECORD | `wwj`.`t3` | idx-1 |
　　+-------------+-------------+-----------+-----------+------------+------------+
　　2 rows in set, 1 warning (0.00 sec)

　　SELECT
　　        p2.`HOST` Blockedhost, #被阻塞方host
　　        p2.`USER` BlockedUser, #被阻塞方用户
　　        r.trx_id BlockedTrxId, #被阻塞方事务id
　　        r.trx_mysql_thread_id BlockedThreadId,      #被阻塞方线程号
　　        TIMESTAMPDIFF(
　　            SECOND,
　　            r.trx_wait_started,
　　            CURRENT_TIMESTAMP
　　        ) WaitTime,   #等待时间
　　        r.trx_query BlockedQuery,         #被阻塞的查询
　　        l.lock_table BlockedTable,        #阻塞方锁住的表
　　        m.`lock_mode` BlockedLockMode,    #被阻塞方的锁模式
　　        m.`lock_type` BlockedLockType,    #被阻塞方的锁类型(表锁还是行锁)
　　        m.`lock_index` BlockedLockIndex, #被阻塞方锁住的索引
　　        m.`lock_space` BlockedLockSpace, #被阻塞方锁对象的space_id
　　        m.lock_page BlockedLockPage,      #被阻塞方事务锁定页的数量
　　        m.lock_rec BlockedLockRec,        #被阻塞方事务锁定行的数量
　　        m.lock_data BlockedLockData,      #被阻塞方事务锁定记录的主键值
　　        p.`HOST` blocking_host,           #阻塞方主机
　　        p.`USER` blocking_user,           #阻塞方用户
　　        b.trx_id BlockingTrxid,           #阻塞方事务id
　　        b.trx_mysql_thread_id BlockingThreadId, #阻塞方线程号
　　        b.trx_query BlockingQuery,        #阻塞方查询
　　        l.`lock_mode` BlockingLockMode,   #阻塞方的锁模式
　　        l.`lock_type` BlockingLockType,   #阻塞方的锁类型(表锁还是行锁)
　　        l.`lock_index` BlockingLockIndex, #阻塞方锁住的索引
　　        l.`lock_space` BlockingLockSpace, #阻塞方锁对象的space_id
　　        l.lock_page BlockingLockPage,     #阻塞方事务锁定页的数量
　　        l.lock_rec BlockingLockRec,       #阻塞方事务锁定行的数量
　　        l.lock_data BlockingLockData,     #阻塞方事务锁定记录的主键值
　　       IF (p.COMMAND = 'Sleep', CONCAT(p.TIME,' seconds'), 0) idel_in_trx #阻塞方事务空闲的时间
　　    FROM
　　        information_schema.INNODB_LOCK_WAITS w
　　    INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
　　    INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id
　　    INNER JOIN information_schema.INNODB_LOCKS l ON w.blocking_lock_id = l.lock_id AND l.`lock_trx_id`=b.`trx_id`
　　      INNER JOIN information_schema.INNODB_LOCKS m ON m.`lock_id`=w.`requested_lock_id` AND m.`lock_trx_id`=r.`trx_id`
　　    INNER JOIN information_schema. PROCESSLIST p ON p.ID = b.trx_mysql_thread_id
　　 INNER JOIN information_schema. PROCESSLIST p2 ON p2.ID = r.trx_mysql_thread_id
　　    ORDER BY
　　        WaitTime DESC;

（编辑：武林网）

上一篇：GDB调试MySQL

下一篇：mysqldump备份时如何保持数据的一致性