重新认识Mysql之MyISAM表锁(共享读与独占写)

(一、Mysql锁的概念与特性)

在Mysql数据库系统中,不同的存储引擎支持不同的锁机制。比如MyISAM和MEMORY存储引擎采用的表级锁，BDB采用的是页面锁，也支持表级锁，InnoDB存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁。

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

本篇博客将记录MyISAM储存引擎中的表级锁

(二、)MyISAM表锁模式

1.MyISAM Lock Read(共享读)

共享读:MyISAM表的读操作，不会阻塞其他用户对同一个表的读请求，但会阻塞对同一个表的写请求.

操作命令:

//加锁
lock table 表名 read

//解锁
unlock tables

实战场景:

clientA: 
    lock table roles read; //读锁
    select * from roles where id = 1; //查询成功

clientB: 
    select * from roles where id = 1; //查询成功
    update roles set name = 'root'; //卡住,等待锁释放
    
ClientA:
    unlock tables; //解锁
    
clientB: 
    update roles set name = 'root2'; //更新成功

2.MyISAM Lock Write(读占写)

独占写:MyISAM表的写操作，会阻塞其他用户对同一个表的读和写操作。

操作命令:

//加锁
lock table 表名 write

//解锁
unlock tables

实战场景:

clientA: 
    lock table roles write; //写锁
    select * from roles where id = 1; //查询成功
    update roles set name = 'admin' where id = 1; //更新成功

clientB: 
    select * from roles where id = 1; //卡住,等待锁释放
ClientA:
    unlock tables; //解锁
    
clientB: 
    select * from roles where id = 1; //查询成功

(三、)表级锁争用情况分析

通过检查table_locks_waited(表锁等待,无法立即获得数据)和table_locks_immediate(立即获得锁地查询数目)状态变量分析系统上表锁争夺情况

show status like '%table_lock%'

分析:如果Table_locks_waited 数值比较高,就说明存在着较严重的表级锁争用情况 ,性能有问题，并发高,需要优化.

(四、)关于MyISAM 锁调度

MyISAM存储引擎的读和写锁是互斥，读操作是串行的。

那么，一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一表的写锁，MySQL如何处理呢？答案是写进程先获得锁。不仅如此，即使读进程先请求先到锁等待队列，写请求后到，写锁也会插到读请求之前！这是因为MySQL认为写请求一般比读请求重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。这种情况有时可能会变得非常糟糕！幸好我们可以通过一些设置来调节MyISAM的调度行为。

• 通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
• 通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
• 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

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

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

参考:《MySQL数据库锁机制之MyISAM引擎表锁和InnoDB行锁详解》