如何理解mysql的锁机制

本文向您展示了如何理解mysql的锁定机制。内容简洁易懂，一定会让你大放异彩。希望通过这篇文章的详细介绍，你能有所收获。

00-101010

锁

目前，MySQL已经支持ISAM、MyISAM、MEMORY (HEAP)表的表级锁、BDB表的页级锁和InnoDB表的行级锁。

很多时候，经验可以用来猜测什么样的锁更适合应用，但通常很难说一个锁比其他锁好，这都取决于应用，不同的地方可能需要不同的锁。

如果您想决定是否需要采用支持行级锁的存储引擎，您必须了解应用程序应该做什么，以及如何使用查询和更新语句。比如很多web应用做了很多查询，但是很少删除，主要是基于索引更新，只在特定的表中插入记录。使用基本的MySQL MyISAM表是合适的。

在MySQL中，表级锁的存储引擎会释放死锁。为了避免死锁，您可以在任何查询之前请求锁，并按照请求的顺序锁定表。

MySQL中WRITE的表锁实现机制如下：

如果表未锁定，则添加写锁。

否则，将请求放入写锁队列。

MySQL中READ的表锁实现机制如下：

如果表上没有写锁，则添加读锁。

否则，将请求放入读锁定队列。

当锁被释放时，写锁队列中的线程可以使用这个锁资源，然后轮到读锁队列中的线程。

也就是说，如果表中有很多更新操作，那么选择在所有更新完成后才能开始。

从MySQL 3.23.33开始，系统中的锁表争用可以通过状态变量Table_locks_waited和Table_locks_immediate来分析：

mysqlSHOWSTATUSLIKE 'Table %---| Variable _ name | Value |---| Table _ locks _ immediate | 1151552 | | Table _ locks _ waited | 15324 |-MySQL 3 . 23 . 7(Windows上为3.23.25)之后，只要MyISAM表中没有冲突的Insert操作，就可以在不使用锁表的情况下自由并行执行。也就是说，当其他客户端正在读取MyISAM表记录时，可以插入新记录。如果数据文件中间没有备用磁盘块，就不会有冲突，因为这种情况下所有新记录都会写到数据文件的末尾(在表中间删除或更新时，可能会造成漏洞)。当用新数据填充孔时，平行插入特征会自动重新启用。

如果您想对一个表执行大量的插入和选择操作，但并行插入是不可能的，您可以将记录插入临时表，然后定期将临时表中的数据更新为实际表。它可以通过以下命令实现：

mysqlLOCKTABLESreal _ tableWRITE，insert _ tableWRITE关系型数据库

gt; Insert INTO real_table Select * FROM insert_table;mysql> TRUNCATE TABLE insert_table;mysql> UNLOCK TABLES;

InnoDB 使用行级锁，BDB 使用页级锁。对于 InnoDB 和 BDB 存储引擎来说，是可能产生死锁的。这是因为 InnoDB 会自动捕获行锁，BDB 会在执行 SQL 语句时捕获页锁的，而不是在事务的开始就这么做。
行级锁的优点有：

• 在很多线程请求不同记录时减少冲突锁。

• 事务回滚时减少改变数据。

• 使长时间对单独的一行记录加锁成为可能。

行级锁的缺点有：

• 比页级锁和表级锁消耗更多的内存。

• 当在大量表中使用时，比页级锁和表级锁更慢，因为他需要请求更多的所资源。

• 当需要频繁对大部分数据做 GROUP BY 操作或者需要频繁扫描整个表时，就明显的比其它锁更糟糕。

• 使用更高层的锁的话，就能更方便的支持各种不同的类型应用程序，因为这种锁的开销比行级锁小多了。

表级锁在下列几种情况下比页级锁和行级锁更优越：

• 很多操作都是读表。

• 在严格条件的索引上读取和更新，当更新或者删除可以用单独的索引来读取得到时：

  Updatetbl_nameSETcolumn=valueWhereunique_key_col=key_value;    Delete FROMtbl_nameWhereunique_key_col=key_value;

• Select 和 Insert 语句并发的执行，但是只有很少的 Update 和 Delete 语句。

• 很多的扫描表和对全表的 GROUP BY 操作，但是没有任何写表。

表级锁和行级锁或页级锁之间的不同之处还在于：
将同时有一个写和多个读的地方做版本（例如在MySQL中的并发插入）。也就是说，数据库/表支持根据开始访问数据时间点的不同支持各种不同的试图。其它名有：时间行程，写复制，或者是按需复制。
原文： Versioning (such as we use in MySQL for concurrent inserts) where you can have one writer at the same time as many readers. This means that the database/table supports different views for the data depending on when you started to access it. Other names for this are time travel, copy on write, or copy on demand.
按需复制在很多情况下比页级锁或行级锁好多了。尽管如此，最坏情况时还是比其它正常锁使用了更多的内存。
可以用应用程序级锁来代替行级锁，例如MySQL中的 GET_LOCK() 和 RELEASE_LOCK()。但它们是劝告锁（原文：These are advisory locks），因此只能用于安全可信的应用程序中。

7.3.2 锁表

为了能有快速的锁，MySQL除了 InnoDB 和 BDB 这两种存储引擎外，所有的都是用表级锁（而非页、行、列级锁）。
对于 InnoDB 和 BDB 表，MySQL只有在指定用 LOCK TABLES 锁表时才使用表级锁。在这两种表中，建议最好不要使用 LOCK TABLES，因为 InnoDB 自动采用行级锁，BDB 用页级锁来保证事务的隔离。
如果数据表很大，那么在大多数应用中表级锁会比行级锁好多了，不过这有一些陷阱。
表级锁让很多线程可以同时从数据表中读取数据，但是如果另一个线程想要写数据的话，就必须要先取得排他访问。正在更新数据时，必须要等到更新完成了，其他线程才能访问这个表。
更新操作通常认为比读取更重要，因此它的优先级更高。不过最好要先确认，数据表是否有很高的 Select 操作，而更新操作并非很‘急需’。
表锁锁在一个线程在等待，因为磁盘空间满了，但是却需要有空余的磁盘空间，这个线程才能继续处理时就有问题了。这种情况下，所有要访问这个出问题的表的线程都会被置为等待状态，直到有剩余磁盘空间了。
表锁在以下设想情况中就不利了：

• 一个客户端提交了一个需要长时间运行的 Select 操作。

• 其他客户端对同一个表提交了 Update 操作，这个客户端就要等到 Select 完成了才能开始执行。

• 其他客户端也对同一个表提交了 Select 请求。由于 Update 的优先级高于 Select，所以 Select 就会先等到 Update 完成了之后才开始执行，它也在等待第一个 Select 操作。

下列所述可以减少表锁带来的资源争夺：

• 让 Select 速度尽量快，这可能需要创建一些摘要表。

• 启动 mysqld 时使用参数 --low-priority-updates。这就会让更新操作的优先级低于 Select。这种情况下，在上面的假设中，第二个 Select 就会在 Insert 之前执行了，而且也无需等待第一个Select 了。

• 可以执行 SET LOW_PRIORITY_UpdateS=1 命令，指定所有的更新操作都放到一个指定的链接中去完成。详情请看“14.5.3.1 SET Syntax”。

• 用 LOW_PRIORITY 属性来降低 Insert，Update，Delete 的优先级。

• 用 HIGH_PRIORITY 来提高 Select 语句的优先级。详情请看“14.1.7 Select Syntax”。

• 从MySQL 3.23.7 开始，可以在启动 mysqld 时指定系统变量 max_write_lock_count 为一个比较低的值，它能强制临时地提高表的插入数达到一个特定值后的所有 Select 操作的优先级。它允许在 WRITE 锁达到一定数量后有 READ 锁。

• 当 Insert 和 Select 一起使用出现问题时，可以转而采用 MyISAM 表，它支持并发的Select 和 Insert 操作。

• 当在同一个表上同时有插入和删除操作时，Insert DELAYED 可能会很有用。详情请看“14.1.4.2 Insert DELAYED Syntax”。

• 当 Select 和 Delete 一起使用出现问题时，Delete 的 LIMIT 参数可能会很有用。详情请看“14.1.1 Delete Syntax”

• 执行 Select 时使用 SQL_BUFFER_RESULT 有助于减短锁表的持续时间.详情请看“14.1.7 Select Syntax”。

• 可以修改源代码 `mysys/thr_lock.c'，只用一个所队列。这种情况下，写锁和读锁的优先级就一样了，这对一些应用可能有帮助。

以下是MySQL锁的一些建议：

• 只要对同一个表没有大量的更新和查询操作混在一起，目前的用户并不是问题。

• 执行 LOCK TABLES 来提高速度（很多更新操作放在一个锁之中比没有锁的很多更新快多了）。将数据拆分开到多个表中可能也有帮助。

• 当MySQL碰到由于锁表引起的速度问题时，将表类型转换成 InnoDB 或 BDB 可能有助于提高性能。详情请看“16 The InnoDB Storage Engine”和“15.4 The BDB (BerkeleyDB) Storage Engine”。

上述内容就是如何理解mysql的锁机制，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注行业资讯频道。