问个innodb死锁问题

jinguanding

winterroot 发表于 2011-12-19 12:45
死锁产生的条件我当然知道，不知道你有没有看我的问题，我的问题其实就是第二个事务到底占用了哪个资源导 ...

意向锁是一种锁的等待状态，排优先级的，并不是真正获得了加锁的权利，只是加锁的优先权的大小而已...

MySQL数据库InnoDB存储引擎源码角度解读死锁检测  http://www.mysqlops.com/2011/12/05/innodb-dead-lock.html

jiwang1980

winterroot 发表于 2011-12-19 14:28
但是，第一个事务已经取得了S锁，而S锁和IX是不兼容的。这是怎么回事？是不是因为意向锁只是针对表锁的， ...

好吧，我承认我也晕了

1.在第二个事务delete语句执行后
------------
TRANSACTIONS
------------
Trx id counter 125D1C46
Purge done for trx's n < 125D1C44 undo n < 0
History list length 321
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 64, query id 610 localhost root
show engine innodb status
---TRANSACTION 125D1C45, ACTIVE 18 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 63, query id 606 localhost root updating
delete from t where i=1
------- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1816 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `ywang`.`t` trx id 125D1C45 lock_mode X locks rec but not gap waiting
------------------
TABLE LOCK table `ywang`.`t` trx id 125D1C45 lock mode IX
RECORD LOCKS space id 1816 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `ywang`.`t` trx id 125D1C45 lock_mode X locks rec but not gap waiting
---TRANSACTION 125D1C44, ACTIVE 20 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 62, query id 605 localhost root
TABLE LOCK table `ywang`.`t` trx id 125D1C44 lock mode IS
RECORD LOCKS space id 1816 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `ywang`.`t` trx id 125D1C44 lock mode S locks rec but not gap
----------------------------
END OF INNODB MONITOR OUTPUT
============================

第二个事务的相关锁信息LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
是不是说明它在等2个锁，一个IS一个是S？

2.这个时候数据库里

mysql> select * from INNODB_LOCKS
    -> ;
+-------------------+-------------+-----------+-----------+-------------+-------------------+------------+-----------+----------+----------------+
| lock_id           | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index        | lock_space | lock_page | lock_rec | lock_data      |
+-------------------+-------------+-----------+-----------+-------------+-------------------+------------+-----------+----------+----------------+
| 125D1C60:1816:3:2 | 125D1C60    | X         | RECORD    | `ywang`.`t` | `GEN_CLUST_INDEX` |       1816 |         3 |        2 | 0x00000038EF01 |
| 125D1C5F:1816:3:2 | 125D1C5F    | S         | RECORD    | `ywang`.`t` | `GEN_CLUST_INDEX` |       1816 |         3 |        2 | 0x00000038EF01 |
+-------------------+-------------+-----------+-----------+-------------+-------------------+------------+-----------+----------+----------------+
2 rows in set (0.04 sec)

mysql> select * from INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+-------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id  |
+-------------------+-------------------+-----------------+-------------------+
| 125D1C60          | 125D1C60:1816:3:2 | 125D1C5F        | 125D1C5F:1816:3:2 |
+-------------------+-------------------+-----------------+-------------------+
1 row in set (0.00 sec)


啊啊啊，抓狂了，晚上回去看看书

jiwang1980

winterroot 发表于 2011-12-19 14:28
但是，第一个事务已经取得了S锁，而S锁和IX是不兼容的。这是怎么回事？是不是因为意向锁只是针对表锁的， ...

好吧，我承认我也晕了

1.在第二个事务delete语句执行后
------------
TRANSACTIONS
------------
Trx id counter 125D1C46
Purge done for trx's n < 125D1C44 undo n < 0
History list length 321
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 64, query id 610 localhost root
show engine innodb status
---TRANSACTION 125D1C45, ACTIVE 18 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 63, query id 606 localhost root updating
delete from t where i=1
------- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1816 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `ywang`.`t` trx id 125D1C45 lock_mode X locks rec but not gap waiting
------------------
TABLE LOCK table `ywang`.`t` trx id 125D1C45 lock mode IX
RECORD LOCKS space id 1816 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `ywang`.`t` trx id 125D1C45 lock_mode X locks rec but not gap waiting
---TRANSACTION 125D1C44, ACTIVE 20 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 62, query id 605 localhost root
TABLE LOCK table `ywang`.`t` trx id 125D1C44 lock mode IS
RECORD LOCKS space id 1816 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `ywang`.`t` trx id 125D1C44 lock mode S locks rec but not gap
----------------------------
END OF INNODB MONITOR OUTPUT
============================

第二个事务的相关锁信息LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s)
是不是说明它在等2个锁，一个IS一个是S？

2.这个时候数据库里

mysql> select * from INNODB_LOCKS
    -> ;
+-------------------+-------------+-----------+-----------+-------------+-------------------+------------+-----------+----------+----------------+
| lock_id           | lock_trx_id | lock_mode | lock_type | lock_table  | lock_index        | lock_space | lock_page | lock_rec | lock_data      |
+-------------------+-------------+-----------+-----------+-------------+-------------------+------------+-----------+----------+----------------+
| 125D1C60:1816:3:2 | 125D1C60    | X         | RECORD    | `ywang`.`t` | `GEN_CLUST_INDEX` |       1816 |         3 |        2 | 0x00000038EF01 |
| 125D1C5F:1816:3:2 | 125D1C5F    | S         | RECORD    | `ywang`.`t` | `GEN_CLUST_INDEX` |       1816 |         3 |        2 | 0x00000038EF01 |
+-------------------+-------------+-----------+-----------+-------------+-------------------+------------+-----------+----------+----------------+
2 rows in set (0.04 sec)

mysql> select * from INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+-------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id  |
+-------------------+-------------------+-----------------+-------------------+
| 125D1C60          | 125D1C60:1816:3:2 | 125D1C5F        | 125D1C5F:1816:3:2 |
+-------------------+-------------------+-----------------+-------------------+
1 row in set (0.00 sec)


啊啊啊，抓狂了，晚上回去看看书

winterroot

jinguanding 发表于 2011-12-19 15:22
客户端A:
START TRANSACTION;
SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE; ----①

那如果我把例子中的delete语句改成select * from t where i=1 for update, 这样也会产生死锁，这时第二个事务为什么不等待第一个事务commit或者rollback，然后再获得X锁呢？

jinguanding

winterroot 发表于 2011-12-19 15:41
那如果我把例子中的delete语句改成select * from t where i=1 for update, 这样也会产生死锁，这时第二个 ...

FOR UPDATE 也是属于独占锁，与DELETE ...操作是类似的...

winterroot

jinguanding 发表于 2011-12-19 16:35
FOR UPDATE 也是属于独占锁，与DELETE ...操作是类似的...

第一个事务能顺利执行下去，为什么会死锁呢？
为什么不是这样呢：第一个事务取得X锁，第二个事务继续等待，第二个事务提交，第一个事务取得X锁。

jinguanding

winterroot 发表于 2011-12-19 17:27
第一个事务能顺利执行下去，为什么会死锁呢？
为什么不是这样呢：第一个事务取得X锁，第二个事务继续等待 ...

关键是其内部可能认为 无法再获得此锁 你测试过程不要乱了，先准备好，推荐文章：
MySQL InnoDB之事务与锁详解  http://www.mysqlops.com/2011/02/16/mysql-innodb-lock.html

ban_ping

意向锁只是针对表锁

jiwang1980

winterroot 发表于 2011-12-19 14:28
但是，第一个事务已经取得了S锁，而S锁和IX是不兼容的。这是怎么回事？是不是因为意向锁只是针对表锁的， ...

我觉得你指的事行锁而 IS IX 是否与S以及X锁兼容，（S，X）指的是表锁
BTW，你的表如果没有索引，innodb会使用表锁

winterroot

jiwang1980 发表于 2011-12-20 16:51
我觉得你指的事行锁而 IS IX 是否与S以及X锁兼容，（S，X）指的是表锁
BTW，你的表如果没有索引，innodb ...

没有索引不会使用表锁。