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锁时，子表的操作会被阻塞。

点击(此处)折叠或打开

1. --主表
   
2. CREATE TABLE `wwj`.`t1` (
   
3.   `deptno` INT NOT NULL,
   
4.   `deptname` VARCHAR(45) NOT NULL,
   
5.   `address` VARCHAR(45) NOT NULL,
   
6.   PRIMARY KEY (`deptno`));
   
7. 
   
8. --子表
   
9.  CREATE TABLE `wwj`.`t2` (
   
10.   `empno` INT NOT NULL,
    
11.   `empname` VARCHAR(45) NOT NULL,
    
12.   `age` INT NOT NULL,
    
13.   `deptno` INT NOT NULL,
    
14.   PRIMARY KEY (`empno`),
    
15.   INDEX `deptno_idx` (`deptno` ASC),
    
16.   CONSTRAINT `deptno`
    
17.     FOREIGN KEY (`deptno`)
    
18.     REFERENCES `wwj`.`t1` (`deptno`)
    
19.     ON DELETE NO ACTION
    
20.     ON UPDATE NO ACTION);
    
21. 
    
22. insert into wwj.t1 values(1,'it','北京');
    
23. insert into wwj.t1 values(2,'product','天津');
    
24. insert into wwj.t1 values(3,'haha','上海');

点击(此处)折叠或打开

1. mysql> select * from information_schema.innodb_locks\G;
2. *************************** 1. row ***************************
3. lock_id: 1303:26:3:2
4. lock_trx_id: 1303
5. lock_mode: S
6. lock_type: RECORD
7. lock_table: `wwj`.`t1`
8. lock_index: PRIMARY
9. lock_space: 26
10. lock_page: 3
11. lock_rec: 2
12. lock_data: 1
13. *************************** 2. row ***************************
14. lock_id: 1298:26:3:2
15. lock_trx_id: 1298
16. lock_mode: X
17. lock_type: RECORD
18. lock_table: `wwj`.`t1`
19. lock_index: PRIMARY
20. lock_space: 26
21. lock_page: 3
22. lock_rec: 2
23. lock_data: 1
24. 2 rows in set, 1 warning (0.00 sec)

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

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

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

--场景模拟

点击(此处)折叠或打开

1. CREATE TABLE `wwj`.`t3` (
   
2.   `idt3` INT NOT NULL,
   
3.   `idt4` INT NOT NULL,
   
4.   PRIMARY KEY (`idt3`),
   
5.   INDEX `idx-1` (`idt4` ASC));
   
6. 
   
7.  insert into wwj.t3 values(1,10); 
8.  insert into wwj.t3 values(3,30); 
9.  insert into wwj.t3 values(5,50);

点击(此处)折叠或打开

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

唯一索引的锁定范围

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

辅助索引的锁定范围


五.一条sql的加锁范围
对于各种情况下加锁的分析

delete from t1 where id = 10; 

组合一:id列是主键，RC隔离级别
id = 10的记录加上X锁


组合二:id列是二级唯一索引，RC隔离级别
若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录， 另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。

组合三:id列是二级非唯一索引，RC隔离级别
id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组 合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。

组合四:id列上没有索引，RC隔离级别
若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记 录，无论是否满足条件，都会被加上X锁。

组合五:id列是主键，RR隔离级别
与组合一相同

组合六:id列是二级唯一索引，RR隔离级别
与组合五类似，组合六的加锁，与组合二:[id唯一索引，Read Committed]一致。两个X锁，id唯一索引满足条件的 记录上一个，对应的聚簇索引上的记录一个。

组合七:id列是二级非唯一索引，RR隔离级别
其实这个多出来的GAP锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。确实，GAP锁锁住的位 置，也不是记录本身，而是两条记录之间的GAP。所谓幻读，就是同一个事务，连续做两次当前读 (例如:select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一 致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。


组合八:id列上没有索引，RR隔离级别
Repeatable Read隔离级别下的最后一种情况，id列上没有索引。此时SQL:delete from t1 where id = 10; 没有 其他的路径可以选择，只能进行全表扫描

 
 六.阻塞

因为锁的兼容性关系，有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。

innodb_lock_wait_timeout #静态参数，控制阻塞等待的时间

innodb_rollback_on_timeout  #动态参数，用来设定是否在等待超时时对进行中的事务进行回滚操作
 
 
 七.关于锁的相关命令

点击(此处)折叠或打开

1. mysql> show full processlist;
   
2. +----+------+-----------+------+---------+------+----------+-----------------------+
   
3. | Id | User | Host | db | Command | Time | State | Info |
   
4. +----+------+-----------+------+---------+------+----------+-----------------------+
   
5. | 11 | root | localhost | NULL | Sleep | 99 | | NULL |
   
6. | 12 | root | localhost | NULL | Sleep | 81 | | NULL |
   
7. | 13 | root | localhost | NULL | Query | 0 | starting | show full processlist |
   
8. +----+------+-----------+------+---------+------+----------+-----------------------+
   
9. 杀掉线程:
   
10. KILL [CONNECTION | QUERY] thread_id
    
11. kill 13 --杀掉线程连接
    
12. kill query 13 --杀掉正在执行的语句，保留连接
    
13. 
    
14. mysql> show engine innodb status\G;
    
15. ------------
    
16. TRANSACTIONS
    
17. ------------
    
18. Trx id counter 1296
    
19. Purge done for trx's n:o < 1294 undo n:o < 0 state: running but idle
    
20. History list length 2
    
21. LIST OF TRANSACTIONS FOR EACH SESSION:
    
22. ---TRANSACTION 421324408397424, not started
    
23. 0 lock struct(s), heap size 1136, 0 row lock(s)
    
24. ---TRANSACTION 1295, ACTIVE 396 sec inserting
    
25. mysql tables in use 1, locked 1
    
26. LOCK WAIT 2 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
    
27. MySQL thread id 12, OS thread handle 139848225883904, query id 61 localhost root executing
    
28. insert into wwj.t3 select 4,20
    
29. ------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
    
30. 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
    
31. Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
    
32.  0: len 4; hex 8000001e; asc ;;
    
33.  1: len 4; hex 80000003; asc ;;
    
34. 
    
35. ---------------------
    
36. TRANSACTION 1294, ACTIVE 449 sec
    
37. 4 lock struct(s), heap size 1136, 3 row lock(s)
    
38. MySQL thread id 11, OS thread handle 139848226150144, query id 50 localhost root
    
39. Trx read view will not see trx with id >= 1294, sees < 1294
    
40. 
    
41. 
    
42. mysql> select * from information_schema.INNODB_LOCK_WAITS;
    
43. +-------------------+-------------------+-----------------+------------------+
    
44. | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
    
45. +-------------------+-------------------+-----------------+------------------+
    
46. | 1302 | 1302:26:4:3 | 1301 | 1301:26:4:3 |
    
47. +-------------------+-------------------+-----------------+------------------+
    
48. 1 row in set, 1 warning (0.00 sec)
    
49. 
    
50. mysql> select lock_id,lock_trx_id,lock_mode,lock_type,lock_table,lock_index from information_schema.INNODB_LOCKs;
    
51. +-------------+-------------+-----------+-----------+------------+------------+
    
52. | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index |
    
53. +-------------+-------------+-----------+-----------+------------+------------+
    
54. | 1302:26:4:3 | 1302 | X,GAP | RECORD | `wwj`.`t3` | idx-1 |
    
55. | 1301:26:4:3 | 1301 | X | RECORD | `wwj`.`t3` | idx-1 |
    
56. +-------------+-------------+-----------+-----------+------------+------------+
    
57. 2 rows in set, 1 warning (0.00 sec)
    
58. 
    
59. SELECT      
60.         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;

参考书籍:
MySQL技术内幕：InnoDB存储引擎

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15412087/viewspace-2152826/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15412087/viewspace-2152826/