mysql rowlock_mysql 死锁

建section表,id为主键,tree_left,tree_right分别为普通索引，

CREATE TABLE `section` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`title` varchar(255) NOT NULL,

`tree_left` int(11) DEFAULT NULL,

`tree_right` int(11) DEFAULT NULL,

`tree_level` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `tree_left` (`tree_left`) ,

KEY `tree_right` (`tree_right`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

数据:

INSERT INTO `section` VALUES ('1', 'root', '1', '14', '0');

INSERT INTO `section` VALUES ('2', 'left tree', '8', '13', '1');

INSERT INTO `section` VALUES ('3', 'left tree2', '9', '12', '2');

INSERT INTO `section` VALUES ('4', 'left tree3', '10', '11', '3');

INSERT INTO `section` VALUES ('5', 'right Tree', '2', '7', '1');

INSERT INTO `section` VALUES ('6', 'right Tree 2', '3', '6', '2');

INSERT INTO `section` VALUES ('7', 'right Tree 3', '4', '5', '3');

session1:

set autocommit=0;

UPDATE `section` SET `TREE_LEFT` = section.TREE_LEFT + 2 WHERE section.TREE_LEFT>=9;

Query OK, 2 rows affected (0.00 sec)

Rows matched: 2 Changed: 2 Warnings: 0

session2:

set autocommit=0;

UPDATE `section` SET `TREE_LEFT` = section.TREE_LEFT + 2 WHERE section.TREE_LEFT>=3;

此语句未立即执行，等待中。。。。

session1:

UPDATE `section` SET `TREE_RIGHT` = section.TREE_RIGHT + 2 WHERE section.TREE_RIGHT>=9;

Query OK, 4 rows affected (0.01 sec)

Rows matched: 4 Changed: 4 Warnings: 0

session2:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

------------------------

LATEST DETECTED DEADLOCK

------------------------

110209 18:31:39

*** (1) TRANSACTION:

TRANSACTION 0 23906588, ACTIVE 6 sec, process no 22976, OS thread id 1162983776 updating or deleting

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 368, 3 row lock(s), undo log entries 1

MySQL thread id 1048786, query id 21106475 192.168.15.219 icms Updating

UPDATE `section` SET `TREE_LEFT` = section.TREE_LEFT + 2 WHERE section.TREE_LEFT>=3

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 4728 n bits 88 index `tree_left` of table `hexh_propel2`.`section` trx id 0 23906588 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 16

*** (2) TRANSACTION:

TRANSACTION 0 23906473, ACTIVE 28 sec, process no 22976, OS thread id 1172035936 starting index read, thread declared inside InnoDB 500

mysql tables in use 1, locked 1

5 lock struct(s), heap size 1216, 8 row lock(s), undo log entries 2

MySQL thread id 1048785, query id 21106651 192.168.15.219 icms Updating

UPDATE `section` SET `TREE_RIGHT` = section.TREE_RIGHT + 2 WHERE section.TREE_RIGHT>=9

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 0 page no 4728 n bits 88 index `tree_left` of table `hexh_propel2`.`section` trx id 0 23906473 lock_mode X

Record lock, heap no 1

Record lock, heap no 15

Record lock, heap no 16

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 0 page no 4727 n bits 80 index `PRIMARY` of table `hexh_propel2`.`section` trx id 0 23906473 lock_mode X waiting

Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0

0: len 4; hex 80000001; asc ;; 1: len 6; hex 0000016cc470; asc l p;; 2: len 7; hex 0000002cea09a1; asc , ;; 3: len 4; hex 726f6f74; asc root;; 4: len 4; hex 80000001; asc ;; 5: len 4; hex 8000000e; asc ;; 6: len 4; hex 80000000; asc ;;

*** WE ROLL BACK TRANSACTION (1)

测了一下午了，索引锁，主键锁，不知道，哪儿出了错？

问题补充：

asima8106 写道

我执行怎么没报错

我在家里的机子上试了试，还是有错的。

问题补充：

william_ai 写道

既然用了set autocommit=0;，那么commit;--或者rollback;就要加上了。

session1,session2分别是两个会话，在commit之前就已经死锁了

问题补充：

qepwqnp 写道

未出现lz所说在死锁情况

session2:

Sql代码

set autocommit=0;

UPDATE `section` SET `TREE_LEFT` = section.TREE_LEFT + 2 WHERE section.TREE_LEFT>=3;

此语句未立即执行，等待中。。。。

session1,session2分别为2个会话。。。。。。。