MySQL——MyISAM 表锁

锁的概念：

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

• MYISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；
• BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；
• InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。

MySQL这3种锁的特性可大致归纳如下。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql> show status like ‘table%’;

如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。

MySQL的表级锁有两种模式：

表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

• 对 MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
• 对 MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；
• MyISAM表的读操作与写操作之间，以及写操作之间是串行的！

给表显示加锁：

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

给 MyISAM 表显式加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。

例如，有一个订单表orders，其中记录有各订单的总金额total，同时还有一个订单明细表 order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检查这两个表的金额合计是否相符，可能就需要执行如下两条SQL语句：

Select sum(total) from orders; 
Select sum(subtotal) from order_detail;

  

 

  
1
  
2
 

这时，如果不先给两个表加锁，就可能产生错误的结果，因为第一条语句执行过程中， order_detail表可能已经发生了改变。因此，正确的方法应该是：

Lock tables orders read local, order_detail read local; 
Select sum(total) from orders; 
Select sum(subtotal) from order_detail; 
Unlock tables;

  

 

  
1
  
2
  
3
  
4
 

上面的例子在LOCK TABLES时加了“local”选项，其作用就是在满足MyISAM表并发插入条件的情况下，允许其他用户在表尾并发插入记录。

MyISAM表不会出现死锁（Deadlock Free）的原因

在用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及表的锁，并且MySQL不支持锁升级。
也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。
在自动加锁的情况下也是如此，MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁（Deadlock Free）的原因。

MyISAM的锁调度

MyISAM存储引擎的读锁和写锁是互斥的，读写操作是串行的。
那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？

答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！

这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。

我们可以通过一些设置来调节MyISAM的调度行为。 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。 通过执行命令

SET LOW_PRIORITY_UPDATES=1

  

 

  
1
 

使该连接发出的更新请求优先级降低。 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

虽然上面3种方法都是要么更新优先，要么查询优先的方法，但还是可以用其来解决查询相对重要的应用（如用户登录系统）中读锁等待严重的问题。

另外，MySQL也提供了一种折中的办法来调节读写冲突，即给系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

另一类表级锁MDL锁（metadata lock)

MDL（metadata lock)。MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

总结：

对于MyISAM的表锁，主要讨论了以下几点。
（1）共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。

（2）在一定条件下，MyISAM允许查询和插入并发执行，可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。

（3）MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW_PRIORITY_UPDATES 参数，或在 INSERT、UPDATE、DELETE 语句中指定LOW_PRIORITY选项来调节读写锁的争用。

（4）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

文章来源: blog.csdn.net，作者：徐同学呀，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/weixin_36586120/article/details/88377800