内部锁定方法
本节讨论内部锁定。也就是说,在MySQL服务器本身内执行锁定,以管理多个会话对表内容的争用。这种类型的锁定是内部锁定,因为它完全由服务器执行,并且不涉及其他程序。有关其他程序对MySQL文件执行的锁定,请参见“外部锁定”。
- 行级锁定
- 表级锁定
- 选择锁定类型
行级锁定
MySQL 对表使用行级锁定,InnoDB
以支持多个会话同时进行写访问,从而使其适用于多用户,高度并发和OLTP应用程序。
为避免在单个表上执行多个并发写操作时出现死锁,即使数据更改语句出现在事务的后面,也要InnoDB
通过SELECT ... FOR UPDATE
在每个事务的行组中发布一条语句来获取必需的锁,以保证每行行都将被修改。如果事务修改或锁定了多个表,请在每个事务中以相同顺序发出适用的语句。死锁会影响性能,而不是代表严重的错误,因为默认情况下,死锁会InnoDB
自动检测到死锁条件,并回滚受影响的事务之一。
在高并发系统上,当多个线程等待相同的锁时,死锁检测会导致速度变慢。有时,禁用死锁检测并在innodb_lock_wait_timeout
发生死锁时依靠设置进行事务回滚可能会更有效。可以使用innodb_deadlock_detect
配置选项禁用死锁检测。
行级锁定的优点:
- 当不同的会话访问不同的行时,锁冲突减少。
- 回滚更改较少。
- 可以长时间锁定单个行。
表级锁定
MySQL 对,和表使用表级锁定MyISAM
,一次仅允许一个会话更新这些表。此锁定级别使这些存储引擎更适合于只读,只读或单用户应用程序。MEMORY
MERGE
这些存储引擎通过始终在查询开始时一次请求所有需要的锁并始终以相同顺序锁定表来避免死锁。权衡是该策略减少了并发性。其他想要修改表的会话必须等待当前数据更改语句完成。
表级锁定的优点:
- 所需的内存相对较少(行锁定需要锁定每行或每组行的内存)
- 在表的大部分上使用时非常快,因为只涉及一个锁。
- 如果您经常
GROUP BY
对大部分数据进行操作,或者必须经常扫描整个表,则速度很快。
MySQL授予表写锁定,如下所示:
- 如果表上没有锁,请在其上放置写锁。
- 否则,将锁定请求放入写锁定队列中。
MySQL授予表读取锁,如下所示:
- 如果表上没有写锁,请在其上放置一个读锁。
- 否则,将锁定请求放入读取锁定队列中。
表更新的优先级高于表检索。因此,释放锁时,该锁可用于写锁队列中的请求,然后可用于读锁队列中的请求。这样可以确保即使表有大量活动,对表的更新也不会“饿死”SELECT
。但是,如果表有许多更新,则SELECT
语句将等到没有更多更新。
有关更改读写优先级的信息,请参见“表锁定问题”。
您可以通过检查Table_locks_immediate
和Table_locks_waited
状态变量来分析系统上的表锁争用,这两个变量分别指示可以立即授予表锁请求的次数和必须等待的次数:
mysql>SHOW STATUS LIKE 'Table%'; +----------------------- +--------- + | Variable_name | Value | +----------------------- +--------- + | Table_locks_immediate | 1151552 | | Table_locks_waited | 15324 | +----------------------- +--------- +
性能架构锁定表还提供锁定信息。请参见“性能模式锁定表”。
该MyISAM
存储引擎支持并发插入,减少读者和作者之间的竞争给定表:如果一个MyISAM
表有数据文件的中间没有空闲块,行总是在数据文件的末尾插入。在这种情况下,您可以自由混合表的并发INSERT
和SELECT
语句,MyISAM
而无需锁定。也就是说,您可以将行插入MyISAM
同时其他客户端正在读取该表。漏洞可能是由于表格中间的行已删除或更新而造成的。如果有孔,将禁用并发插入,但是当所有孔都已填充新数据时,并发插入会自动重新启用。若要控制此行为,请使用concurrent_insert
系统变量。请参见“并发插入”。
如果使用显式获取表锁LOCK TABLES
,则可以请求一个READ LOCAL
锁而不是一个READ
锁,以使其他会话在锁定表时能够执行并发插入。
执行许多INSERT
和SELECT
操作上的表t1
时并发的插入是不可能的,你可以插入行到一个临时表temp_t1
,并从临时表中的行更新真正的表:
mysql>LOCK TABLES t1WRITE , temp_t1WRITE ; mysql>INSERT INTO t1SELECT *FROM temp_t1; mysql>DELETE FROM temp_t1; mysql>UNLOCK TABLES ;
选择锁定类型
通常,在以下情况下,表锁优于行级锁:
- 该表的大多数语句均为读取。
该表的语句是读和写的混合,其中写是对单行的更新或删除,可通过一次按键读取来获取:
UPDATE tbl_nameSET column=valueWHERE unique_key_col=key_value;DELETE FROM tbl_nameWHERE unique_key_col=key_value;SELECT
与并发INSERT
语句结合,很少有UPDATE
orDELETE
语句。GROUP BY
无需任何编写程序即可对整个表进行许多扫描或操作。
使用高级锁,您可以通过支持不同类型的锁来更轻松地调整应用程序,因为锁开销比行级锁要少。
行级锁定以外的选项:
- 版本控制(例如在MySQL中用于并发插入的版本控制),可以同时拥有一个编写者和多个阅读者。这意味着数据库或表根据访问开始的时间支持数据的不同视图。这个其他常见的术语是“时间旅行,”“上写副本,”或“按需复制。”
- 在许多情况下,按需复制优于行级锁定。但是,在最坏的情况下,它使用的内存可能比使用普通锁更多。
- 除了使用行级锁,您还可以使用应用程序级锁,例如MySQL
GET_LOCK()
和RELEASE_LOCK()
MySQL 提供的锁。这些是咨询锁,因此它们仅适用于相互协作的应用程序。请参见“锁定函数”。