[笔记]锁:各种场景 整理

see:
http://dev.mysql.com/doc/refman/5.5/en/innodb-locks-set.html
http://hedengcheng.com/?p=844

原理：
当主键/唯一索引进行更新(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秒后，报等待超时错误
但如果并发线程多可能会触发死锁 参见:http://hedengcheng.com/?p=844


# 唯一索引: 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秒后，报等待超时错误

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

转载于:http://blog.itpub.net/26250550/viewspace-1220082/