MySQL锁(一)

墨蓝 2022-02-26 18:06 131阅读 0赞

## MySQL锁(MyISAM) ##

**锁是计算机协调多个进程或线程并发访问某一资源的机制.** 在数据库中,除了 传统的计算资源(如CPU,内存,I/O等)的争用以外,数据也是一种供许多用户共享的资源. 如何保证数据并发访问的一致性,有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素. Mysql用到了很多这种锁机制,比如行锁,表锁,读锁,写锁等,都是在操作之前先上锁.这些锁统称为悲观锁(Pessimistic Lock)

#### 1.MySQL锁概述 ####

相对其他数据库而言,MySQL的锁机制比较简单,其中 显著地特点是不同的存储引擎支持不同的锁机制.比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking); BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁; InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁.

*  表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低
 *  行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高
 *  页面锁:开销和加锁时间介于表锁和行锁之间;会出现死锁;锁定粒度介于表锁与行锁之间,并发度一般

综上所述,不能说哪种锁更好,只能就具体应用的特点来看哪种锁更合适. 从锁的角度来说:表级锁更适合于查询为主,只有少量按索引条件更新数据的应用. 而行级锁更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用

#### 2.MyISAM表锁 ####

MySQL表级锁有有两种模式: **表共享读锁**(Table Read Lock) 和 **表独占写锁**(Table Write Lock)

对于MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞同一表的写请求; 对于MyISAM表的写请求,则会阻塞其他用户对同一表的读和写操作; 所以 MyISAM表的读操作与写操作之间,以及写操作之间是串行的. 所以,当一个线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新操作.其他线程的读,写操作都会等待,知道锁被释放为止

*  例一:MyISAM写锁阻塞读: 当一个线程获得对一个表的写锁之后,只有持有锁的线程可以对表进行更新操作. 其他线程的读,写操作都会等待,知道锁释放为止

![è¿™é‡Œå†™å›¾ç‰‡æè¿°][SouthEast]

*  例二:MyISAM读锁阻塞写: 一个session使用LOCK TABLE命令给表加了读锁,这个session可以查询锁定表中的记录,但是**更新或者访问其他表都会提示错误**; 同时,另外一个session**可以查询表汇总的记录,但是更新就会出现锁等待**

![è¿™é‡Œå†™å›¾ç‰‡æè¿°][SouthEast 1]

*  MyISAM给表加锁

MyISAM作为引擎的表中,在执行查询语句之前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE,INSERT,DELETE)前,会自动给涉及的表加写锁.

给MyISAM显式加锁,一般是为了在一定程度模拟事务操作,实现对某一时间多个点多个表的一致性读取. 例如:有一个订单表orders,其中有各订单的总金额total,同时有一个订单明细表order\_detail,其中记录有各订单每一产品的小计subtotal,假设我们需要检查这两个表的金额合计是否相符,可能要执行以下两条sql

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

这时,如果不给两个表加锁,很有可能产生错误的结果.因为第一条语句执行过程中,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.上面的例子在锁表时加了"local"选项,其作用就是在满足MyISAM表并发插入条件的情况下,允许其他用户在表尾插入记录 2. 再用LOCK TABLES 给表显式加锁时,必须同时取得所有涉及到表的锁,不能访问未加锁的表;同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作. MyISAM总是以此获得SQL语句所需要的全部所,这也正是MyISAM不会出现死锁的原因

*  查询表级锁争用情况
    
        show status like 'table%'

如果Table\_locks\_waited的值比较高,则说明存在比较严重的表级锁争用情况

#### 3.并发插入(Concurrent Inserts) ####

上文提到过MyISAM表的读和写是串行的,但这是就总体而言.一定条件下,MyISAM表也支持查询和插入操作的并发进行

MyISAM存储引擎有一个系统变量concurrent\_insert, 专门用以控制其并发插入的行为,其值分别可以为0,1,2;

*  当concurrent\_insert设置为0, 不允许并发插入
 *  当concurrent\_insert设置为1时,如果MyISAM表中没有空洞(即标的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录.这是MySQL的默认设置
 *  当concurrent\_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录
 *  MyISAM存储引擎的读写(INSERT)并发例子:

![è¿™é‡Œå†™å›¾ç‰‡æè¿°][SouthEast 2]

可以利用MyISAM存储引擎的并发插入特性,来解决应用中对同一表查询和插入的锁争用. 将concurrent\_insert系统变量设为2,总是允许并发插入. 同时,通过定期在系统空闲时段执行OPTIMIZE TABLE语句来整理空间碎片,回收因删除记录而产生的中间空洞

#### 4.MyISAM的锁调度 ####

前面讲过,MyISAM存储引擎的读锁和写锁是互斥的,读写操作是串行的,那么,一个进程请求某个MyISAM表的读锁,同时另外一个进程也请求同一表的写锁,MySQL会如何处理呢?答案是**写进程先获得锁**,不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插入到读锁之前! 这时因为MySQL认为写请求一般比读请求重要. 这也是MyISAM不适合于有大量更新和查询操作应用的原因,因为大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞. 不过我们可以通过一些设置来调节MyISAM的调度行为:

*  通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以有限的权利
 *  通过执行 SET LOW\_PRIORITY\_UPDATES=1 , 使该连接发出的更新请求优先级降低
 *  通过指定INSERT ,UPDATE,DELETE语句的LOW\_PRIORITY属性,降低该语句的优先级

虽然上面的三种方法都是要么更新优先,要么查询优先的方法,但还是可以来解决查询相对重要的应用中读锁等待严重的问题

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

另外,一些需要长时间运行的查询操作,也会使写进程"饿死",因此,应用中应避免出现长时间运行的查询操作,不要总想用一条SELECT语句来解决问题,因为这种看似巧妙的SQL语句,往往比较复杂,执行时间长,在可能的情况下可以通过使用中间表等措施对SQL语句做一定的分解,使每一步查询都能在较短时间完成.从而减少锁冲突.

#### 小结:对于MyISAM的表锁,我们讨论了以下内容 ####

（1）共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的。  
（2）在一定条件下，MyISAM允许查询和插入并发执行，我们可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。  
（3）MyISAM默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以通过设置LOW\_PRIORITY\_UPDATES参数，或在INSERT、UPDATE、DELETE语句中指定LOW\_PRIORITY选项来调节读写锁的争用。  
（4）由于表锁的锁定粒度大，读写之间又是串行的，因此，如果更新操作较多，MyISAM表可能会出现严重的锁等待，可以考虑采用InnoDB表来减少锁冲突。

[SouthEast]: /images/20220226/06214db70f3c40b0b02b82d04f829607.png
[SouthEast 1]: /images/20220226/7d366991d2314c28964d42c24a34da99.png
[SouthEast 2]: /images/20220226/c2accfe699a64bf5bc111f9e87b2e6d9.png