????????
????????????????????????????????????????????????????μ???????????????????????????????????????????????????????????????mysql????????????????????????????????????????????????????????????????????????????????????????????????????????DBA?????????????????????????????????????????????????????鷽???????????????????????????????
????PS?????????????????????????mysql???????????ο??????ο??????????????
????????????
????????????????????????????漰??????????????????????????????????????????????????
??????????????5.5?汾??mysql????????????????????RR??Repeatable-Read????????innodb???檔???????test???
CREATE TABLE `test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT??
`a` int(11) unsigned DEFAULT NULL??
PRIMARY KEY (`id`)??
UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8;
????????????????????id???????????a??????????????￡?
mysql> select * from test;
+----+------+
| id | a    |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  4 |    4 |
+----+------+
3 rows in set (0.00 sec)
????????????????????￡?

?????????????????SHOW ENGINE INNODB STATUS;?????????????
------------------------
LATEST DETECTED DEADLOCK
------------------------
170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD?? ACTIVE 11 sec starting index read
mysql tables in use 1?? locked 1
LOCK WAIT 2 lock struct(s)?? heap size 376?? 1 row lock(s)
MySQL thread id 448218?? OS thread handle 0x2abe5fb5d700?? query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock?? heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc     ;;
1: len 4; hex 00000002; asc     ;;
*** (2) TRANSACTION:
TRANSACTION 2A8BC?? ACTIVE 18 sec inserting
mysql tables in use 1?? locked 1
4 lock struct(s)?? heap size 1248?? 3 row lock(s)?? undo log entries 2
MySQL thread id 448217?? OS thread handle 0x2abe5fd65700?? query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id??a) values (10??2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock?? heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc     ;;
1: len 4; hex 00000002; asc     ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock?? heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc     ;;
1: len 4; hex 00000002; asc     ;;
*** WE ROLL BACK TRANSACTION (1)
????????
??????????????
???????????????????????????????????????????????????????????????????1??????????
170219 13:31:31
*** (1) TRANSACTION:
TRANSACTION 2A8BD?? ACTIVE 11 sec starting index read
mysql tables in use 1?? locked 1
LOCK WAIT 2 lock struct(s)?? heap size 376?? 1 row lock(s)
MySQL thread id 448218?? OS thread handle 0x2abe5fb5d700?? query id 18923238 renjun.fangcloud.net 121.41.41.92 root updating
delete from test where a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BD lock_mode X waiting
Record lock?? heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc     ;;
1: len 4; hex 00000002; asc     ;;
?????????????????????????1??????????delete from test where a = 2?????????????????????a??X???????????lock_mode X waiting??
?????????????°????????????2??????е???????????????
*** (2) TRANSACTION:
TRANSACTION 2A8BC?? ACTIVE 18 sec inserting
mysql tables in use 1?? locked 1
4 lock struct(s)?? heap size 1248?? 3 row lock(s)?? undo log entries 2
MySQL thread id 448217?? OS thread handle 0x2abe5fd65700?? query id 18923239 renjun.fangcloud.net 121.41.41.92 root update
insert into test (id??a) values (10??2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock_mode X locks rec but not gap
Record lock?? heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc     ;;
1: len 4; hex 00000002; asc     ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 923 n bits 80 index `a` of table `oauthdemo`.`test` trx id 2A8BC lock mode S waiting
Record lock?? heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000002; asc     ;;
1: len 4; hex 00000002; asc     ;;
???????????HOLDS THE LOCKS(S)??????????????????2????????a??X????????????????Record Lock?????????????????2?????2??е?delete???????????????RR???????μ?????????????????Where a = 2???????????????????????????next-key????