mysql update阻塞_mysql 更新唯一主键列 被堵塞

mysql> select @@tx_isolation;

+-----------------+

| @@tx_isolation |

+-----------------+

| REPEATABLE-READ |

+-----------------+

1 row in set (0.00 sec)

Session 196:

mysql> show create table mail;

+-------

| mail | CREATE TABLE `mail` (

`mailid` bigint(20) NOT NULL AUTO_INCREMENT,

`receiverid` int(10) unsigned NOT NULL,

`senderid` int(10) unsigned NOT NULL,

PRIMARY KEY (`mailid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

mysql> insert into mail values(1,2222,3333);

Query OK, 1 row affected (0.00 sec)

mysql> insert into mail values(2,333,3333);

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into mail values(3,333,3333);

Query OK, 1 row affected (0.00 sec)

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

| 196 |

+-----------------+

1 row in set (0.02 sec)

Session 197:

mysql> update mail set receiverid=193719371 where mailid=3;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> update mail set receiverid=193719371 where mailid=3;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> select connection_id();

+-----------------+

| connection_id() |

+-----------------+

| 197 |

+-----------------+

1 row in set (0.00 sec)

mysql> update mail set receiverid=193719371 where mailid=3;

389253799,197,update mail set receiverid=193719371 where mailid=3,389253798,196,

mysql[192.168.11.187] blocking_thread[196] current_query[] blocking waiting_thread[197]'s update mail set receiverid=193719371 where mailid=3

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,

,,,,,