ITPub博客

首页 > Linux操作系统 > Linux操作系统 > MySQL官方手册中X lock优先赋予的问题?

MySQL官方手册中X lock优先赋予的问题?

原创 Linux操作系统 作者:mysqldbd 时间:2011-03-10 14:06:54 0 删除 编辑
The following example illustrates how an error can occur when a lock request would cause a deadlock. The example involves two clients, A and B.

First, client A creates a table containing one row, and then begins a transaction. Within the transaction, A obtains an S lock on the row by selecting it in share mode:

mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)

mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.10 sec)

Next, client B begins a transaction and attempts to delete the row from the table:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

The delete operation requires an X lock. The lock cannot be granted because it is incompatible with the S lock that client A holds, so the request goes on the queue of lock requests for the row and client B blocks.

Finally, client A also attempts to delete the row from the table:

mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction

Deadlock occurs here because client A needs an X lock to delete the row. However, that lock request cannot be granted because client B already has a request for an X lock and is waiting for client A to release its S lock. Nor can the S lock held by A be upgraded to an X lock because of the prior request by B for an X lock. As a result, InnoDB generates an error for client A and releases its locks. At that point, the lock request for client B can be granted and B deletes the row from the table.

这里面说的是client A释放了锁,然后由client B 来delete了这条记录,而A就报了40001的错误,但是其实我测试的时候发现刚好相反,client A delete了这条记录,而报错的确是client B,这是为什么呢?


过程如下,Client A创建表t、添加记录、开启事务、加S lock。

【Client A】
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.03 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.01 sec)
mysql> -- client A start delete the row;
mysql> DELETE FROM t WHERE i = 1;
Query OK, 1 row affected (0.04 sec)

mysql>

【Client B】
mysql>
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.02 sec)
mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql> -- client B accur a error;
mysql>

大家看到了吧,最后client A删除了记录,而client B报错400001,跟官方手册上说的不一样,Innodb不是说谁钱请求的,就把X lock给谁用。

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

请登录后发表评论 登录
全部评论

注册时间:2011-01-19

  • 博文量
    35
  • 访问量
    81364