mysql的锁机制

表级锁:

开销小,加锁快,不会出现死锁,锁定粒度大,加锁冲突概率最高,并发度最低;

适用于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;

行级锁

开销大,加锁慢,会出现死锁,锁定粒度小,发生锁冲突的概率最低,并发度最高;

适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。

页面锁

开销和加锁时间介于表锁和行锁之间,会出现死锁,锁定粒度介于表锁和行锁之间,并发度一般;

MyISAM表锁

查询表级锁的争用情况

mysql>显示状态如表%的;
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +
| Variable_name | |值
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +
| Table_locks_immediate 19 | |
| Table_locks_waited | 0 |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +
2行(0.00秒)

Table_locks_waited的值越大,表级锁的争用情况越严重

表级锁的锁模式

<李>

表共享读锁

<李>

表共享写锁

MyISAM表读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;
MyISAM表的写操作,会阻塞其他用户对同一表的读请求和写请求。
MyISAM表的读操作和写操作之间,以及写操作之间是串行的

如何加表锁

MyISAM表在执行选择语句之前,会自动给涉及到的表加读锁,在执行更新、删除、插入之前,自动给涉及到的表加写锁。

当然也可以显示手动加锁,用来模拟事务操作。

mysql>锁表tbl_name1读/写;
mysql>锁表tbl_name1读(本地),tbl_name2写(本地);

mysql>打开表;

注意:对表加锁之后,使用该表的别名也是不允许的,需要对别名也进行锁定,如下:

mysql>锁表tbl_name1 tb1阅读;
mysql>select  a.id 从tbl_name tb1;

MyISAM的并发插入

MyISAM的读写操作是串行的,但一定程度上,也是支持查询和插入的并发进行,但不可以删除和更新。

在MyISAM引擎中有一个系统变量concurrent_insert,专门用来控制并发插入的行为,有三种取值:

0→不允许并发插入;
1→在MyISAM表没有孔洞的前提下,允许在读的同时,另一个进程从尾部插入记录(默认)
2→不论有无空洞,都可以在表尾并发插入

整理空间碎片:

mysql>优化表tbl_name;

MyISAM的锁调度

已经知道,MyISAM存储引擎的读锁和写锁是互斥的,读写操作是串行的,但是,及时读请求先到达等待队列,写请求后到达等待队列,写锁也会插入到读锁之前,因为MySQL认为写操作比读操作重要。

同时,这也是MyISAM表不适合有大量更新和查询操作的原因,因为大量的更新和查询操作会占据锁等待队列,读锁会被长时间等待。

为了解决这个问题,我们有一些参数设置来调节MyISAM的调度行为。

<李>

启动low-priority-updates,使得MyISAM默认基于读请求以优先的权利,

<李>

降低更新请求的的优先级

mysql>设置LOW_PRIORITY_UPDATES=1

<李>

指定插入、更新、删除语句的LOW_PRIORITY属性,降低该语句的优先级

<李>

一种折中的方案:给系统参数max_write_locl_count设置一个合适的值,当表的读操作达到该值之后,MySQL就暂时将写请求的优先级降低。

InnoDB锁

InnoDB与MyISAM的最大不同支出在于:1支持事务;2,采用了行级锁。

查询InnoDB行锁的争用情况

mysql>显示状态像innodb_row_lock %的;
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +
| Variable_name | |值
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + +

mysql的锁机制