[筆記]鎖:各種場景 整理

see:
http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html


原理：
當主鍵/唯一索引進行更新(INSERT/UPDATE/DELETE)時,如果無法獲取記錄的X鎖，會轉為等待獲取S鎖
但持有X鎖的執行緒釋放後，其他等待S鎖的執行緒都會持有S鎖
但再嘗試獲取X鎖 時，都無法獲取

=============場景
表結構
CREATE TABLE `tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_uniq_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDBDEFAULT CHARSET=utf8;
資料
insert  into `tb`(`id`,`a`,`b`,`c`) values (1,1,0,1),(2,2,3,2),(3,3,1,3),(4,4,4,4),(5,5,4,5),(6,6,4,6),(7,7,5,7),(8,8,5,8),(9,9,5,9),(10,10,6,10),(11,11,6,11),(12,12,6,12),(13,13,6,13),(14,14,6,14),(15,15,7,15),(16,16,1,16);

# 主鍵: INSERT
th1: begin;INSERT INTO tb SET id=100,a=UNIX_TIMESTAMP();
th2: begin;INSERT INTO tb SET id=100,a=UNIX_TIMESTAMP();
th3: begin;INSERT INTO tb SET id=100,a=UNIX_TIMESTAMP();
th1先執行
th2、th3再執行，由於無法獲取X鎖，轉而獲取一個primary上id=100的S鎖
th1 rollback
th2、th3 由於持有S鎖，等待X鎖，發生死鎖


# 唯一索引: DELETE
th1: begin;delete from tb where a=3;
th2: begin;delete from tb where a=3;
th3: begin;delete from tb where a=3;
th1先執行
th2、th3再執行，由於無法獲取X鎖，轉而獲取一個idx_uniq_a上a=3的S鎖
th1 rollback
th2、th3 正常情況下th2先獲得X鎖，但不提交。th3由於無法獲取X鎖，在等待innodb_lock_wait_timeout秒後，報等待超時錯誤
但如果併發執行緒多可能會觸發死鎖 參見:


# 唯一索引: DELETE + INSERT
th1: begin;delete from tb where a=3;
th2: begin;delete from tb where a=3;
th3: begin;delete from tb where a=3;
th1先執行
th2、th3再執行，等待獲取X鎖，但由於是唯一索引，持有idx_uniq_a上a=3的S鎖
th1 : INSERT INTO tb SET a=3,b=3,c =CEIL(RAND()*10);  等待S鎖
th2、th3 由於持有S鎖，等待X鎖，發生死鎖

# 非唯一索引操作: DELETE 
th1: begin;delete from tb where b=3;
th2: begin;delete from tb where b=3;
th3: begin;delete from tb where b=3;
th1先執行
th2、th3再依次執行，等待獲取X鎖，但由於不是唯一索引，不持有S鎖
th1 rollback 或者 INSERT INTO tb SET a=uinx_timestamp(),b=3,c =CEIL(RAND()*10);  不需要等待S鎖
th2先獲得X鎖，但不提交。th3由於無法獲取X鎖，在等待innodb_lock_wait_timeout秒後，報等待超時錯誤