SQL大神之--（1）数据库的死锁模拟
目前真是更新的太少了，工作的时候不能连外网，连记录点东西都没办法
今天的专题是数据库死锁，这里要提前说的是这是个探索系列，摸着石头过河，
死锁 就是 执行语句的时候提示：DeadLock found when trying to get lock； try restarting transaction
给出死锁的条件：开两个客户端A和B
A：start transaction
A： select * from product where id=56 lock in share mode；
B：start transaction
B： select * from product where id=56 lock in share mode；
B：update product set product_name='abc123' where id=56
A：update product set product_name='abc123' where id=56
以上是执行顺序，A开启事物--->A 加共享锁---> A 需要加独占锁（等待获取共享锁，但是B占有）--》显示死锁
B开启事物--->B加共享锁--->B需要加独占锁（等待获取共享锁，但是A占有）-->
以下的解释来自于https://www.cnblogs.com/javalyy/p/8875715.html
解释
例4：（死锁的发生） ---------------------------------------- T1: begin tran select * from table (holdlock) (holdlock意思是加共享锁，直到事物结束才释放) update table set column1='hello' T2: begin tran select * from table(holdlock) update table set column1='world' 假设T1和T2同时达到select，T1对table加共享锁，T2也对加共享锁，当T1的select执行完，准备执行update时，根据锁机制，T1的共享锁需要升 级到排他锁才能执行接下来的update.在升级排他锁前，必须等table上的其它共享锁释放，但因为holdlock这样的共享锁只有等事务结束后才释放， 所以因为T2的共享锁不释放而导致T1等(等T2释放共享锁，自己好升级成排他锁），同理，也因为T1的共享锁不释放而导致T2等。死锁产生了。
而数据库规定同一资源上不能同时共存共享锁和排他锁。
所以T2必须等T1执行完，释放了共享锁，才能加上排他锁，然后才能开始执行update语句。
----------
SEMAPHORES // 信号
----------
OS WAIT ARRAY INFO: reservation count 118146 //os wait 的信息 ，reservation count 表示InnoDB产生了多少次OS WAIT
OS WAIT ARRAY INFO: signal count 186714 // 进行OS WAIT线程，接收到多少次信号(single)被唤醒，如果这个single数值越大，几十万或者几百万，可能是很多I/0等待或者是InnoDB争用问题(关于争用问题可能与OS调度有关，可以尝试减少innodb_thread_concurrency参数)
Mutex spin waits 1664035, rounds 4276317, OS waits 20348 // Mutex spin线程无法获取锁而进入Spin wait ,rounds是spin wait 进行轮询检查mutextes的次数，os wait 线程放弃spin-wait 进入挂起状态
RW-shared spins 302454, rounds 11667281, OS waits 69050 //RW-shared 共享锁，
RW-excl spins 83942, rounds 4021896, OS waits 28377 // RW-excl 排他锁
Spin rounds per wait: 2.57 mutex, 38.58 RW-shared, 47.91 RW-excl
// 备注：要明白Innodb如何处理互斥量(Mutexes)，以及什么是两步获得锁(two-step approach)。首先进程，
试图获得一个锁，如果此锁被它人占用。它就会执行所谓的spin wait,即所谓循环的查询”锁被释放了吗？”。
如果在循环过程中，一直未得到锁释放的信息，则其转入OS WAIT，即所谓线程进入挂起(suspended)状态。
直到锁被释放后，通过信号(singal)唤醒线程
Spin wait的消耗远小于OS waits。Spinwait利用cpu的空闲时间，检查锁的状态，
OS Wait会有所谓content switch，从CPU内核中换出当前执行线程以供其它线程使用。
你可以通过innodb_sync_spin_loops参数来平衡spin wait和os wait
------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-05-11 18:52:09 2b6677e07700 //死锁发生的时间
*** (1) TRANSACTION:
TRANSACTION 495116414, ACTIVE 0.092 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 14 lock struct(s), heap size 6544, 20 row lock(s), undo log entries 6
LOCK BLOCKING MySQL thread id: 870003 block 876753
MySQL thread id 876753, OS thread handle 0x2b6685903700, query id 315677415 10.168.152.132 dsc Searching rows for update
update aaaa
set xxx=xxx+(-1)
where id=412 and xxx+(-1)>=0 //显示第一个死锁的的第一个事务
*** (1) WAITING FOR THIS LOCK TO BE GRANTED: //这里是B客户端
RECORD LOCKS space id 558 page no 5 n bits 144 index `idx_aaaa_unique` of table `test`.`aaaa` trx id 495116414 lock_mode X locks rec but not gap waiting
Record lock, heap no 17 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
// 以上表示死锁发生时事务1等待的锁，事务想获得aaaa表的idx_aaaa_unique索引对应的X排他锁(Innodb的锁是与索引相关)
0: len 8; hex 8000000000000001; asc ;;
1: len 2; hex 5748; asc WH;;
2: len 8; hex 800000000000004b; asc K;;
3: len 8; hex 8000000000000002; asc ;;
4: len 8; hex 8000000000002725; asc '%;;
5: len 8; hex 8000000000000215; asc ;;
6: len 2; hex 5a50; asc ZP;;
7: len 8; hex 4231363033313441; asc B160314A;;
8: len 6; hex 00001d82e06a; asc j;;
9: len 7; hex 1d00000235151a; asc 5 ;;
10: len 8; hex 800000000000019c; asc ;;
11: len 8; hex 8000000000000000; asc ;;
12: len 5; hex 9998da0000; asc ;;
13: len 5; hex 999f5a0000; asc Z ;;
14: len 10; hex 5a303230323032303031; asc Z020202001;;
15: len 12; hex e5b9bfe4b89ce5b9bfe5b79e; asc ;;
16: len 4; hex 80001b2f; asc /;;
17: len 7; hex 80000000000000; asc ;;
18: len 23; hex 50555243484153455f4f524445525f494e5f53544f434b; asc PURCHASE_ORDER_IN_STOCK;;
19: len 22; hex 53493230313630343136303030303136333531313735; asc SI20160416000016351175;;
20: len 5; hex 99992b1384; asc + ;;
21: SQL NULL;
22: len 5; hex 99994d7c8d; asc M| ;;
*** (2) TRANSACTION: // 事务2的状态
TRANSACTION 495116394, ACTIVE 0.246 sec fetching rows
mysql tables in use 1, locked 1
17 lock struct(s), heap size 2936, 18 row lock(s), undo log entries 21
MySQL thread id 870003, OS thread handle 0x2b6677e07700, query id 315677426 10.168.152.132 dsc Searching rows for update
update aaaa
set xxx=xxx+(-2)
where id=430 and xxx+(-2)>=0
*** (2) HOLDS THE LOCK(S): // 表示事务2获得的锁
RECORD LOCKS space id 558 page no 5 n bits 144 index `idx_aaaa_unique` of table `test`.`aaaa` trx id 495116394 lock_mode X locks rec but not gap
Record lock, heap no 17 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 2; hex 5748; asc WH;;
2: len 8; hex 800000000000004b; asc K;;
3: len 8; hex 8000000000000002; asc ;;
4: len 8; hex 8000000000002725; asc '%;;
5: len 8; hex 8000000000000215; asc ;;
6: len 2; hex 5a50; asc ZP;;
7: len 8; hex 4231363033313441; asc B160314A;;
8: len 6; hex 00001d82e06a; asc j;;
9: len 7; hex 1d00000235151a; asc 5 ;;
10: len 8; hex 800000000000019c; asc ;;
11: len 8; hex 8000000000000000; asc ;;
12: len 5; hex 9998da0000; asc ;;
13: len 5; hex 999f5a0000; asc Z ;;
14: len 10; hex 5a303230323032303031; asc Z020202001;;
15: len 12; hex e5b9bfe4b89ce5b9bfe5b79e; asc ;;
16: len 4; hex 80001b2f; asc /;;
17: len 7; hex 80000000000000; asc ;;
18: len 23; hex 50555243484153455f4f524445525f494e5f53544f434b; asc PURCHASE_ORDER_IN_STOCK;;
19: len 22; hex 53493230313630343136303030303136333531313735; asc SI20160416000016351175;;
20: len 5; hex 99992b1384; asc + ;;
21: SQL NULL;
22: len 5; hex 99994d7c8d; asc M| ;;
Record lock, heap no 59 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 2; hex 5748; asc WH;;
2: len 8; hex 800000000000004b; asc K;;
3: len 8; hex 8000000000000002; asc ;;
4: len 8; hex 800000000000276a; asc 'j;;
5: len 8; hex 80000000000002c2; asc ;;
6: len 2; hex 5a50; asc ZP;;
7: len 9; hex 423136303231374341; asc B160217CA;;
8: len 6; hex 00001d82e06a; asc j;;
9: len 7; hex 1d00000235169f; asc 5 ;;
10: len 8; hex 80000000000001db; asc ;;
11: len 8; hex 8000000000000000; asc ;;
12: len 5; hex 9998a20000; asc ;;
13: len 5; hex 99a2600000; asc ` ;;
14: len 10; hex 5a303230323032303031; asc Z020202001;;
15: len 12; hex e5b9bfe4b89ce5b9bfe5b79e; asc ;;
16: len 4; hex 80000772; asc r;;
17: len 7; hex 80000000000000; asc ;;
18: len 23; hex 50555243484153455f4f524445525f494e5f53544f434b; asc PURCHASE_ORDER_IN_STOCK;;
19: len 22; hex 53493230313630343136303030303137333630353531; asc SI20160416000017360551;;
20: len 5; hex 99992b1385; asc + ;;
21: SQL NULL;
22: len 5; hex 99994d7c8d; asc M| ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED: // 表示事务2等待的锁
RECORD LOCKS space id 558 page no 4 n bits 152 index `idx_aaaa_unique` of table `test`.`aaaa` trx id 495116394 lock_mode X locks rec but not gap waiting
Record lock, heap no 63 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 2; hex 5748; asc WH;;
2: len 8; hex 800000000000004b; asc K;;
3: len 8; hex 8000000000000002; asc ;;
4: len 8; hex 8000000000000065; asc e;;
5: len 8; hex 80000000000000a8; asc ;;
6: len 2; hex 5a50; asc ZP;;
7: len 9; hex 423136303232314b41; asc B160221KA;;
8: len 6; hex 00001d82e07e; asc ~;;
9: len 7; hex 2b000001d920ad; asc + ;;
10: len 8; hex 80000000000001c8; asc ;;
11: len 8; hex 8000000000000000; asc ;;
12: len 5; hex 9998aa0000; asc ;;
13: len 5; hex 99a2680000; asc h ;;
14: len 10; hex 5a303230323032303031; asc Z020202001;;
15: len 12; hex e5b9bfe4b89ce5b9bfe5b79e; asc ;;
16: len 4; hex 80000b14; asc ;;
17: len 7; hex 80000000000000; asc ;;
18: len 23; hex 50555243484153455f4f524445525f494e5f53544f434b; asc PURCHASE_ORDER_IN_STOCK;;
19: len 22; hex 53493230313630343136303030303137333630353531; asc SI20160416000017360551;;
20: len 5; hex 99992b1385; asc + ;;
21: SQL NULL;
22: len 5; hex 99994d7c8d; asc M| ;;
*** WE ROLL BACK TRANSACTION (1) // 表示选择了哪个事务回滚，避免无限期死锁等待
// innodb有一个内在的死锁检测工具，当死锁超过一定时间后，会回滚其中一个事务,innodb_lock_wait_timeout
可配置死锁等待超时时间
------------
TRANSACTIONS // 包含了InnoDB事务(transaction)的统计信息
------------
Trx id counter 495910498 // 当前的transaction id ,这是个系统变量，随着每次新的transaction产生而增加
Purge done for trx's n:o < 495910389 undo n:o < 0 state: running but idle //正在进行清空的操作操作的transaction ID
History list length 2606 // 记录了undo spaces 内unpurged 的事务个数
//Purge的原则就是记录没有被其它事务继续使用了
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 329193748744296, not started
MySQL thread id 909825, OS thread handle 0x2b6142bc7700, query id 325773092 10.143.34.172 dsc init
show engine innodb status
---TRANSACTION 329193658413160, not started
MySQL thread id 909832, OS thread handle 0x2b667d881700, query id 325773024 10.168.108.146 dsc cleaning up
.....此处省略...
