为InnoDB中不同类型的SQL设置锁
一般来说，锁定读、update、delete等类型的SQL语句在执行的过程中会对被扫描的索引记录加记录锁，这跟有没有过滤行的where条件没有关系，Innodb不会记住这些where条件，但是它知道哪些索引范围被扫描。通常，如果被插入的记录处于一个gap中，那么在插入该条记录前，next-key锁会阻塞记录的插入。但是，gap锁可以手动禁掉它，禁掉后就不会用到所谓的next-key锁了。更详细的内容，请查看14.2.2.4, “InnoDB Record, Gap, AND NEXT-KEY Locks”部分。事务的隔离级别也会对锁的设置产生影响，请看Section 13.3.6, “SET TRANSACTION Syntax”
如果某个查询中用到二级索引并且索引记录以独占方式锁定，InnoDB会检索相关的聚族索引记录，并在其上面加上。
有关共享锁和独占锁的解释请查看 14.2.2.1, “InnoDB LOCK Modes”部分。
如果你没有为你的SQL语句设置合理的索引的话，在执行这条SQl的时候MySQL会扫描整张表，那么表的每一行都会被锁定，所有其他用户对该表的插入都会被阻塞，如果你想让你执行SQL的时候不会扫描不必要的记录的话，那么请为你的SQl设计合理的索引吧。
对于SELECT ... FOR UPDATE 和 SELECT ... LOCK IN SHARE MODE这样的SQL，为扫描的行加锁，并且释放掉不在结果集中的记录的锁(例如，缺少where条件的SQL)，但是，有些行可能不会马上被锁，因为结果集可能被其他SQL更新了。比如，在存在union的SQl中，在分析结果集是否要过滤之前这些加锁的记录可能已经被插入了一张临时表。这种情况下，由于临时表中的行和原始表中丢失行的关系，查询结束前，原始表中的行是不会被锁定的。
InnoDB设置特定类型的锁
select ... from读的是数据库的一致性快照，除了串行化隔离级别外，一般不加锁。串行化级别，查询会对查询的结果集加共享的next-key锁。
SELECT ... FROM ... LOCK IN SHARE MODE 会对所有查询碰到的索引加
共享的next-key锁。
对查询中碰到的索引记录，SELECT ... FROM ... FOR UPDATE会阻塞正在执行的像SELECT ... FROM ... LOCK IN SHARE MODE 或者在某些事务隔离级别下的读，一致性读会忽略任何锁。
UPDATE ... WHERE ...会为他查询的每条记录加独占的next-key锁。
DELETE FROM ... WHERE ...同上。
INSERT会为插入的行加独占锁，这是一个索引记录锁，不是一个next-key锁(没有gap锁)，并且不会阻塞其他会话向gap间插入行。
在插入行之前，有一种gap锁叫插入意向gap锁会被加到表上。这种锁表示一种插入意向，
如果不是在gap间的同一位置插入数据，那么在同一个索引gap间插入数据不会互相阻塞。假设有值为4和7的索引记录，打算向表中插入5和6，在获得插入行的独占锁之前在4和7的gap间设置分离事务的意向锁，由于行之间是不冲突的，所以并不会互相阻塞。
如果发生主键重复的错误，在重复索引记录上会创建一个共享锁。如果别的会话已经持有独占锁，而多个事务均在尝试插入同一行数据，那么使用共享锁可能会导致死锁的发生。如果别的会话想删除这些行的话，也会发生死锁，假如有如下结构的InnoDB表t1:
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = INNODB;
现在假设有3个会话按顺序执行下面的操作
session 1:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
session 2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
session 3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
seesion 1:
ROLLBACK;
session 1的第一个操作会获得一个独占锁，session 2和session 3的操作都会导致主键重复的错误，并且他们都请求行的共享锁。当session 1回滚的时候，他释放了行上的独占锁并且session2和session3获得了共享锁队列的授权，这个时候，两个session死锁了:由于共享锁被互相持有，两者都不能获得独占锁。
如果表里已经有1的记录了，按下面的顺序操作的话也会发生同样的情况:
session 1:
START TRANSACTION;
DELETE FROM t1 WHERE i = 1;
session 1:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
session 1:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
session 1:
commit;
session 1的第一个操作会获得一个独占锁，session 2和session 3的操作都会导致主键重复的错误，并且他们都请求行的共享锁。当session 1提交的时候，他释放了行上的独占锁并且session2和session3获得了共享锁队列的授权，这个时候，两个session死锁了:由于共享锁被互相持有，两者都不能获得独占锁。
INSERT ... ON DUPLICATE KEY UPDATE不同于普通的INSERT，当发生主键冲突的时候他会在更新的行上加一个独占的next-key锁而非共享锁。
如果在唯一键索引上没有冲突，REPLACE就跟INSERT一样，否则的话，
他也会在被替换的行上加一个独占的next-key锁。
INSERT INTO T SELECT ... FROM S WHERE ... 会在T表上加独占的索引记录锁，不是gap锁。如果事务隔离级别是RC，或者启用 innodb_locks_unsafe_for_binlog并且事务隔离级别非串行化的话，InnoDB以一致性读的方式查询S表(非锁定读)。否则的话，InnoDB会在S表行上加共享的next-key锁，下面的例子InnoDB必须加锁:
在一个备份前滚恢复的时候，每个SQL都必须按照他原来的样子原样恢复。
CREATE TABLE ... SELECT ... 会作为一致性读或加共享的next-key锁，跟INSERT ... SELECT 一样。
当SELECT语句用来构建像 REPLACE INTO t SELECT ... FROM s WHERE ... OR UPDATE t ... WHERE col IN (SELECT ... FROM s ...)语句的时候，InnoDB会在表s上加共享的next-key锁。
当初始化一个表的自动增长列的时候，InnoDB会在该列的最后一条记录上加一个独占锁。在访问自动增长计数列的时候，InnoDB采用一种特定的AUTO-INC表锁模式，只会锁定到该SQl语句结束，而不是整个事务的结束，当持有AUTO-INC表锁的时候，别的会话不能向该表中插入记录。请查阅Section 14.2.2, “The INNODB TRANSACTION Model AND Locking”部分。
InnoDB获取当前的自动增长列的值不会加任何锁。
如果表上有外键索引，任何的insert,update,delete操作会检查约束条件，这样的话会在记录上加check约束的共享记录级别锁。约束失效的话也会加锁。
执行LOCK_TABLES语句会加表锁，但是这种锁是在server层设置而非存储引擎层
如果设置参数为innodb_table_locks = 1 (the default) 和
autocommit=0的话，server层也会识别到行级锁。
否则的话，InnoDB自动死锁检测机制不能检测到这种死锁，还有就是在这种情况下由于server层无法识别行级锁，所以当别的会话存在行级锁的时候，可能当前会话会识别为(锁升级)表锁。但是，这并不会危及到事务的完整性。
阅读(758) | 评论(0) | 转发(0) |
