这是一个很好的问题。 InnoDB是行级锁定引擎,但它必须设置额外的锁来确保二进制日志(用于复制;时间点恢复)的安全。要开始解释它,请考虑以下(简单的)示例:
session1> START TRANSACTION;
session1> DELETE FROM users WHERE is_deleted = 1; # 1 row matches (user_id 10), deleted.
session2> START TRANSACTION;
session2> UPDATE users SET is_deleted = 1 WHERE user_id = 5; # 1 row matches.
session2> COMMIT;
session1> COMMIT;
因为语句只有在提交后才会写入二进制日志,所以在从属会话#2 上将首先应用,并会产生不同的结果,导致数据损坏.
InnoDB 所做的就是设置额外的锁。如果is_deleted
被索引,那么在 session1 提交之前没有其他人能够修改或插入范围记录的数量,其中is_deleted=1
。如果没有索引is_deleted
,那么InnoDB需要锁定整个表中的每一行以确保重放的顺序相同。你可以将其视为锁定间隙, 这是与直接掌握行级锁定不同的概念.
在你的情况下ORDER BY position ASC
,InnoDB需要确保在最低键值和“特殊”最低可能值之间不能修改任何新行。如果你做了类似的事情ORDER BY position DESC
..好吧,那么没有人可以插入这个范围。
所以解决方案来了:
基于语句的二进制日志记录很糟糕。我真的很期待我们都转向的未来基于行的二进制日志记录 http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html(从 MySQL 5.1 开始可用,但默认情况下不启用)。
对于基于行的复制,如果将隔离级别更改为已提交读,则只需锁定匹配的一行。
如果你想成为受虐狂,你也可以开启innodb_locks_unsafe_for_binlog http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog具有基于语句的复制。
4 月 22 日更新:复制+粘贴我的测试用例的改进版本(它不是在“间隙”中搜索):
session1> CREATE TABLE test (id int not null primary key auto_increment, data1 int, data2 int, INDEX(data1)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
session1> INSERT INTO test VALUES (NULL, 1, 2), (NULL, 2, 1), (5, 2, 2), (6, 3, 3), (3, 3, 4), (4, 4, 3);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
session1> start transaction;
Query OK, 0 rows affected (0.00 sec)
session1> SELECT id FROM test ORDER BY data1 LIMIT 1 FOR UPDATE;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
session2> INSERT INTO test values (NULL, 0, 99); # blocks - 0 is in the gap between the lowest value found (1) and the "special" lowest value.
# At the same time, from information_schema:
localhost information_schema> select * from innodb_locks\G
*************************** 1. row ***************************
lock_id: 151A1C:1735:4:2
lock_trx_id: 151A1C
lock_mode: X,GAP
lock_type: RECORD
lock_table: `so5694658`.`test`
lock_index: `data1`
lock_space: 1735
lock_page: 4
lock_rec: 2
lock_data: 1, 1
*************************** 2. row ***************************
lock_id: 151A1A:1735:4:2
lock_trx_id: 151A1A
lock_mode: X
lock_type: RECORD
lock_table: `so5694658`.`test`
lock_index: `data1`
lock_space: 1735
lock_page: 4
lock_rec: 2
lock_data: 1, 1
2 rows in set (0.00 sec)
# Another example:
select * from test where id < 1 for update; # blocks