10.11.2 表锁定问题
InnoDB
表使用行级锁定,以便多个会话和应用程序可以同时读取和写入同一个表,而不需要等待或产生不一致的结果。对于这个存储引擎,避免使用LOCK TABLES
语句,因为它不提供额外的保护,而是减少了并发性。自动的行级锁定使这些表适合于最繁忙的数据库中,同时也简化了应用程序逻辑,因为不需要锁定和解锁表。因此,InnoDB
存储引擎是 MySQL 的默认存储引擎。
MySQL 使用表锁定(而不是页面、行或列锁定)对所有存储引擎进行锁定,除了InnoDB
。锁定操作本身不需要太多资源。但是,因为只有一个会话可以在任何时候写入一个表,因此对于其他存储引擎,建议使用它们来存储经常查询的表,但不经常插入或更新。
在选择使用 InnoDB
或其他存储引擎时,请注意以下表锁定的缺点:
-
表锁定允许多个会话读取同一个表,但是如果一个会话想要写入一个表,它必须首先获取独占访问权限,这可能需要等待其他会话完成对表的访问。在更新期间,所有其他会话都需要等待直到更新完成。
-
表锁定会在会话等待磁盘空间变得可用时出现问题。在这种情况下,所有会话都将被阻塞直到更多的磁盘空间变得可用。
-
一个
SELECT
语句如果运行时间太长将阻止其他会话更新该表,使得其他会话出现延迟或不响应。一个会话等待获取独占访问权限更新表时,其他会话将队列起来,降低了读取会话的并发性。
以下是避免或减少表锁定竞争的方法:
-
考虑将表切换到
InnoDB
存储引擎,使用CREATE TABLE ... ENGINE=INNODB
在设置时,或者使用ALTER TABLE ... ENGINE=INNODB
对现有表。见第 17 章,《InnoDB 存储引擎》了解更多关于这个存储引擎的信息。 -
优化
SELECT
语句,以便它们在锁定表的时间更短。你可能需要创建一些摘要表来实现这个目标。 -
使用mysqld启动
--low-priority-updates
。对于使用表级锁定的存储引擎(例如SELECT
),这将使所有更新(修改)表的语句具有低优先级。在这种情况下,第二个SELECT
语句将在UPDATE
语句执行之前执行,并且不会等待第一个SELECT
语句完成。 -
要使特定连接中的所有更新语句具有低优先级,请将
low_priority_updates
服务器系统变量设置为1。 -
要为特定的
INSERT
、UPDATE
或DELETE
语句指定低优先级,请使用LOW_PRIORITY
属性。 -
要为特定的
SELECT
语句指定高优先级,请使用HIGH_PRIORITY
属性。请参阅第15.2.13节,“SELECT 语句”。 -
使用mysqld启动
max_write_lock_count
系统变量的低值,以强制MySQL在特定写锁定次数后临时提高所有等待表的SELECT
语句的优先级。这允许读锁定在写锁定后执行。 -
如果您遇到混合
SELECT
和DELETE
语句的问题,LIMIT
选项可能会有所帮助。请参阅第15.2.2节,“DELETE 语句”。 -
使用
SQL_BUFFER_RESULT
与SELECT
语句可以帮助使表锁定的持续时间更短。请参见 第15.2.13节,“SELECT 语句”。 -
将表内容分割到不同的表中可能会有所帮助,可以使查询运行在一个表中的列上,而更新操作则在另一个表中的列上。
-
您可以将
mysys/thr_lock.c
中的锁定代码修改为使用单个队列。在这种情况下,写锁定和读锁定将具有相同的优先级,这可能会帮助一些应用程序。