MYSQL 锁
锁的类型
共享锁(S)
共享锁可以被多个事务持有，事务T1获取了行r的共享锁，那么事务T2也可以获得行r的共享锁，但是获取不到事务T1的排它锁
显示的加共享锁：select * from xx lock in share mode;
排它锁(X)
排他锁只能被一个事务持有，事务T1持有了行r的排它锁，那么事务T2获取不到行r的排它锁，也获取不到行r的共享锁
显式的加排它锁：select * from xx for update;
|
S |
X | |
|---|---|---|
|
S |
兼容 |
不兼容 |
|
X |
不兼容 |
不兼容 |
创建一张测试表a,id是主键，a2为唯一索引，a3为普通所以
CREATE TABLE `a` ( `id` int(11) NOT NULL AUTO_INCREMENT , a1 int(11) NOT NULL , a2 int(11) not NULL, a3 int(11) not null, PRIMARY KEY (`id`), unique key uniq_a2(a2), index idx_a3(a3) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ; insert into a(a1,a2,a3) values(1,2,3),(2,4,6),(3,6,12);
事务1获取共享锁，事务2获取排它锁，事务2将获取失败
|
事务1 |
事务2 |
|---|---|
|
start TRANSACTION; select * from a where id=2 lock in share mode; |
|
|
获取S锁成功 |
|
|
start TRANSACTION; select * from a where id=2 for update; |
|
|
等待... |
|
|
获取X锁超时（Lock wait timeout exceeded; try restarting transaction） |
|
|
commit; |
|
|
start TRANSACTION; select * from a where id=2 for update; |
|
在事务2等待锁过程中执行 select * from information_schema.innodb_trx; 查看锁当前状态，可以看到事务1处于RUNNING状态，事务2处于LOCK WAIT状态
其中trx_weight表示事务的权重，反应了一个事务修改和锁住的行数。在INNODB中，当发生死锁需要回滚时，InnoDB会选择该值最小的进行回滚，因为这个值越小需要进行回滚操作的数据越少
在事务2等待锁过程中执行 select * from information_schema.innodb_locks 查看锁信息，lock_data表示事务锁定的主键的值，由于查询条件是id=2,因此lock_data=2,由于两个事务对同一行记录分别申请S所和X锁，因此会发生等待
lock_rec 事务锁定行的数量（明明是按主键锁主了一条记录，为什么这里的lock_rec是3）
lock_page 事务锁定页的数量
使用 select * from information_schema.innodb_lock_waits; 查看信息，可以看到那些事务获取到了锁，那些事务在阻塞中
一致性非阻塞读（consistent nonblocking read）
一致性非阻塞读表示存储引擎通过MVCC方式读取当前时间数据库中的数据，如果正在读取的行正在执行delete或者update操作，读操作被会被阻塞，而是会读取该行记录的快照数据。
在事务隔离级别为READ COMMITTED 和 REPEATABLE READ 下，InnoDB采用非阻塞读方式。但是在READ COMMITTED隔离级别下，读取到的快照数据是最新的一个快照数据(不可重复读)，而在REPEATABLE READ模式下，读取到的快照数据总是在事务开始时读取到的快照数据（可重复读）
REPEATABLE READ隔离级别下，在同一个事务内，读取到的数据总是一致的（因为msyql默认的隔离级别是RR,因此我们使用RR测试）
|
会话A |
会话B |
|---|---|
|
start TRANSACTION; |
|
|
select * from a where id=1;(a1=1) |
|
|
start TRANSACTION; |
|
|
select * from a where id=1; (a1=10) |
|
|
select * from a where id=1;(a1=1) |
|
|
commit; |
|
|
select * from a where id=1;(a1=1) |
|
|
commit; |
|
|
select * from a where id=1;(a1=10) |
在READ COMMITTED 隔离级别下，在事务内总是读取到最新的快照，也就是可以读取到别的事务提交的数据。(需要手动设置隔离级别为RC,同时将列a1的值重置为1)
|
会话A |
会话B |
|---|---|
|
start TRANSACTION; |
|
|
select * from a where id=1;(a1=1) |
|
|
start TRANSACTION; |
|
|
select * from a where id=1; (a1=10) |
|
|
select * from a where id=1;(a1=1) |
|
|
commit; |
|
|
select * from a where id=1;(a1=10) |
|
|
commit; |
|
|
select * from a where id=1;(a1=10) |
一致性阻塞读
行锁的3种算法
Record Lock: 单行记录上的锁，锁住的是索引记录，如果表没有任何创建索引，那么InnoDB默认使用隐式的主键进行锁定
Gap Lock:间隙锁，锁定一个范围，不包含记录本身
Next-Key Lock:Gap+Record Lock,锁定一个范围，包括记录本身
如果采用Next-Key Lock算法，那么针对表a中的列a3(普通索引，值分为为 3,6,12)，那么可能被Next-Key Locking的区间为：
(-∞, 3], (3,6], (6,12], (12,+∞)
采用Next-Key Lock的锁定技术称为Next-Key Locking,目的是为了解决幻读问题(Phantom Problem),除了next-key locking ,还有previous-key locking 技术，如果采用previous-key locking,那么上述a3可锁定的区间为：
(-∞, 3), [3,6), [6,12), [12,+∞)
当查询的索引是唯一索引时，InnoDB会对Next-Key Locking进行优化，降级为Record Lock,值锁住索引本身，而不是范围
以下针对列a2进行测试，a2为唯一索引，a2的值为 2，4，6
在Next-Key Lock算法下，可锁住的区间为(-∞, 2], (2,4], (4,6], (6,+∞)，5和3处于索引范围内，会话B获取5和3的锁应该要等待，但是由于a2是唯一索引，因此降级为Record Lock，仅锁住a2=4本身，所以会话B不会被阻塞
|
会话A |
会话B |
|---|---|
|
start TRANSACTION; |
|
|
select * from a where a2=4 for update; |
|
|
start TRANSACTION; |
|
|
select * from a where a2=5 for update;(成功，不需要等待) |
|
|
select * from a where a2=3 for update;(成功，不需要等待) |
|
|
commit; |
|
|
commit; |
|
看下普通索引在Next-key Lock算法下的情况
使用表a的列a1测试，a1为普通索引
采用Next-Key Lock 算法，可锁定的区间为(-∞, 1), (1,2), (2,3),(3,9), (9,+∞),
|
会话A |
会话B |
|---|---|
|
start TRANSACTION; |
|
|
select * from a where a1=3 for update; |
|
|
start TRANSACTION; |
|
|
select * from a where a1=5 for update;(获取锁不成功，因为5处于(3,9]之间) |
|
|
select * from a where a1=2 for update;(获取锁不成功，因为2处于锁定范围内) |
|
|
select * from a where a1=3 lock in share mode; (获取锁不成功，因为3本身已被锁定) |
|
|
select * from a where id=1 for update;(获取锁不成功) 为什么1会被锁定 |
|
|
select * from a where id=10 for update;(获取锁成功，因为会话A锁定的范围是[1,9),10不在锁定范围内，因此获取锁成功)为什么锁定范围不是(2,9) |
|
|
select * from a where id=0 for update;(获取锁成功) |
死锁
死锁是由于两个或两个以上的事务在执行过程中，因争夺资源而造成的一种相互等待的现象。
解决死锁最简单的方式是不要有等待，将任何的等待转化为回滚。两个事务相互等待，当其中一个等待时间超过设置的某一个阈值，根据权重，将其中一个事务回滚，另外一个事务就可以正常运行。
权重在前面提到过(txr_weight),权重的大小取决于事务修改和锁定的行数，更新的行数越小，占用的undo log 也就越少，回滚需要的时间也就越少
测试下死锁
|
会话A |
会话B |
|---|---|
|
会话A |
会话B |
|
start TRANSACTION; |
|
|
delete from a where id=3; (成功) |
|
|
start TRANSACTION; |
|
|
delete from a where id=2;（成功） |
|
|
delete from a where id=2;（等待） |
|
|
delete from a where id=3; (等待) |
|
|
超时回滚其中一个事务 |
show engine innodb status ; 查看死锁日志
===================================== 2019-07-26 02:02:56 0x70000b18b000 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 15 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 84 srv_active, 0 srv_shutdown, 167877 srv_idle srv_master_thread log flush and writes: 167921 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 155 OS WAIT ARRAY INFO: signal count 154 RW-shared spins 0, rounds 270, OS waits 133 RW-excl spins 0, rounds 666, OS waits 6 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 270.00 RW-shared, 666.00 RW-excl, 0.00 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2019-07-26 02:02:42 0x70000b037000 *** (1) TRANSACTION: TRANSACTION 53206, ACTIVE 10 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 25, OS thread handle 123145487921152, query id 2377 localhost 127.0.0.1 root updating delete from a where id=3 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 246 page no 3 n bits 80 index PRIMARY of table `hotel_mint`.`a` trx id 53206 lock_mode X locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 32 0: len 4; hex 80000003; asc ;; 1: len 6; hex 00000000cfd1; asc ;; 2: len 7; hex 220000012822e4; asc " (" ;; 3: len 4; hex 80000003; asc ;; 4: len 4; hex 80000006; asc ;; 5: len 4; hex 8000000c; asc ;; *** (2) TRANSACTION: TRANSACTION 53201, ACTIVE 13 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1 MySQL thread id 23, OS thread handle 123145487085568, query id 2378 localhost 127.0.0.1 root updating delete from a where id=2 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 246 page no 3 n bits 80 index PRIMARY of table `hotel_mint`.`a` trx id 53201 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 6; compact format; info bits 32 0: len 4; hex 80000003; asc ;; 1: len 6; hex 00000000cfd1; asc ;; 2: len 7; hex 220000012822e4; asc " (" ;; 3: len 4; hex 80000003; asc ;; 4: len 4; hex 80000006; asc ;; 5: len 4; hex 8000000c; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 246 page no 3 n bits 80 index PRIMARY of table `hotel_mint`.`a` trx id 53201 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 32 0: len 4; hex 80000002; asc ;; 1: len 6; hex 00000000cfd6; asc ;; 2: len 7; hex 250000030f0d82; asc % ;; 3: len 4; hex 80000002; asc ;; 4: len 4; hex 80000004; asc ;; 5: len 4; hex 80000006; asc ;; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 53212 Purge done for trx's n:o < 53212 undo n:o < 0 state: running but idle History list length 52 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 281479538886448, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281479538887352, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 53206, ACTIVE 24 sec 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2 MySQL thread id 25, OS thread handle 123145487921152, query id 2380 localhost 127.0.0.1 root -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (write thread) I/O thread 7 state: waiting for i/o request (write thread) I/O thread 8 state: waiting for i/o request (write thread) I/O thread 9 state: waiting for i/o request (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 2182 OS file reads, 1151 OS file writes, 505 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 1.53 writes/s, 0.80 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 1 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) Hash table size 34673, node heap has 0 buffer(s) 1.53 hash searches/s, 1.13 non-hash searches/s --- LOG --- Log sequence number 3022251932 Log flushed up to 3022251932 Pages flushed up to 3022251932 Last checkpoint at 3022251923 0 pending log flushes, 0 pending chkp writes 306 log i/o's done, 0.47 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 137428992 Dictionary memory allocated 439113 Buffer pool size 8191 Free buffers 5999 Database pages 2191 Old database pages 818 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 2109, created 82, written 769 0.00 reads/s, 0.07 creates/s, 1.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 2191, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=103, Main thread ID=123145481420800, state: sleeping Number of rows inserted 5879, updated 11, deleted 11, read 798826 0.00 inserts/s, 0.00 updates/s, 0.07 deletes/s, 0.07 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
避免死锁的方法
1、系统中事务的数量尽量少
2、避免大事务，事务尽量小
3、避免大sql,能拆则拆，避免长时间占用资源，导致其他事务等待或者与其他事务冲突发生死锁
