会话1中执行如下:
mysql> select * from ttt; | ||
---|---|---|
id | name | score |
1 | aa | 60 |
2 | bb | 70 |
3 | cc | 80 |
3 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update ttt set name='aaa' where score=60;
Query OK, 1 row affected (0.07 sec)
Rows matched: 1 Changed: 1 Warnings: 0
会话2中执行:
mysql> select * from ttt; | ||
---|---|---|
id | name | score |
1 | aa | 60 |
2 | bb | 70 |
3 | cc | 80 |
3 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update ttt set name='a' where score=70;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
会话3中执行:
mysql> select * from information_schema.innodb_locks; | |||||||||
---|---|---|---|---|---|---|---|---|---|
lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
20759:177:3:5 | 20759 | X | RECORD | test .ttt | PRIMARY | 177 | 3 | 5 | 1 |
20757:177:3:5 | 20757 | X | RECORD | test .ttt | PRIMARY | 177 | 3 | 5 | 1 |
其中表ttt的结构如下:
mysql> show create table ttt; | |
---|---|
Table | Create Table |
ttt | CREATE TABLE ttt ( |
id
int(11) NOT NULL AUTO_INCREMENT,
name
varchar(20) DEFAULT NULL,
score
int(11) DEFAULT NULL,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 |
---|
问题1;
请问ttt上没有普通索引,在会话1中更新score=60的记录时候,应该是把所有的记录都加上行锁
但是通过information_schema.innodb_locks没有看到
问题2;
请问会话1,会话2更新的不同行,一个是60,一个是70,
为什么information_schema.innodb_locks中 的lock_data都是1 呢???