重新认识Mysql之MyISAM表锁(共享读与独占写)
重新认识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行锁详解》