SQL 锁

abuying
烂草皮
等级:
可用分等级：乞丐
总技术分：35
总技术分排名：144922

发表于：2008-04-27 15:56:072楼得分:30

锁定读SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE
在一些环境中，一个持续读是不方便的。比如，你可能想要往表的子表里添加一个新行，并确信该子表在父表中有一个根。下列例子显示如何在你应用程序代码中实现参考的完整性。

假设你使用一个持续读去读取父表并且看到表中子表的根。不能安全地往子表添加子行吗？不，因为可能同时发生一些其它用户从父表删除父行，而你没有注意到它的情况。

解决办法是在使用LOCK IN SHARE MODE的锁定模式执行SELECT：

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
在共享模式执行一个读意味着我们读最新的可用数据，并在我们读的行设置一个共享锁定。共享模式锁防止其它人更新或删除我们已读的行。同时，如果最新的数据属于其它客户端尚未提交的事务，我们等着知道那个事务被提交。我们看到前述的查询返回父'Jones'，我们可以安全地往子表添加子记录并提交我们的事务。

让我们看另外一个例子：我们在表child_codes 中有一个整数计数器域，我们用该表给每个添加到子表里的子项指派一个唯一的识别符。显然，使用持续读或者共享模式读去读取当前计数器的值并是一个好主意，因为数据库的两个用户可能看到计数器的同一个值，如果两个用户试着用同一识别符往该表添加子项，就会发生一个重复键（duplicate-key）错误。

在这里，如果两个用户同时读计数器，当试图更新计数器之时，至少它们中有一个会发生死锁错误并终止，因此LOCK IN SHARE MODE并不是一个好的解决方法。

在这种情况下，有两个好方法去实现读计数器和增长计数器值：(1) 先更新计数器，让计数器值增1，之后读计数器，或者(2)用锁定模式FOR UPDATE先读计数器，之后计数器值增加。后一个途径可被如下实现：

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;
SELECT ... FOR UPDATE读最新的可见数据，在每个它读取的行设置独占锁定。因此，它设置与搜索的SQL UPDATE可能会在行上设置的锁定同样的锁定。

请注意，以上仅是一个SELECT ... FOR UPDATE如何起作用的例子。在MySQL中，事实上生成一个唯一识别符的特殊任务可被用对该表的单独访问来完成：

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();
SELECT语句仅仅取回识别符信息（专门对当前连接）。它不访问任何表。

Next-Key锁定：避免幽灵问题
在行级锁定中，InnoDB 使用一个名为next-key locking的算法。InnoDB以这样一种方式执行行级锁定：当它搜索或扫描表的索引之时，它对遇到的索引记录设置共享或独占锁定。因此，行级锁定事实上是索引记录锁定。

InnoDB对索引记录设置的锁定也映像索引记录之前的“间隙”。如果一个用户对一个索引上的记录R有共享或独占的锁定，另一个用户不能紧接在R之前以索引的顺序插入一个新索引记录。这个间隙的锁定被执行来防止所谓的“幽灵问题”。假设你想要从有一个标识符值大于100的子表读并锁定所有子记录，并想着随后在选定行中更新一些列：

SELECT * FROM child WHERE id > 100 FOR UPDATE;
假设在id列有一个索引。查询从id大于100的第一个记录开始扫描。如果设置在索引记录上的锁定不把在间隙生成的插入排除在外，一个新行可能与此同时被插进表中。如果你在同一事务内执行同样的SELECT，你可能会在该查询返回的结果包里看到一个新行。这与事务的隔离原则是相反的：一个事务应该能够运行，以便它已经读的数据在事务过程中不改变。如果我们把一套行视为数据项，新的“幽灵”子记录可能会违反这一隔离原则。

当InnoDB扫描一个索引之时，它也锁定所以记录中最后一个记录之后的间隙。刚在前一个例子中发生：InnoDB设置的锁定防止任何插入到id可能大过100的表。

你可以用next-key锁定在你的应用程序上实现一个唯一性检查：如果你以共享模式读数据，并且没有看到你将要插入的行的重复，则你可以安全地插入你的行，并且知道在读过程中对你的行的继承者设置的next-key锁定与此同时阻止任何人对你的行插入一个重复。因此，the next-key锁定允许你锁住在你的表中并不存在的一些东西

修改删除举报引用回复