mysql myisam 锁_重新认识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行锁详解》