mysql MyISAM 表锁

MyISAM采用表级锁。

表级锁开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低。

 

通过table_locks_waited和table_locks_immediate状态变量来分析系统上表锁定争夺。

show status like 'table%';

table_locks_waited:产生表锁定的次数。

table_locks_immediate：出现表锁定争夺而发生等待的次数。

 

表锁有2种模式：

表共享读锁。

表独占写锁。

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

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

MyISAM表的读操作和写操作之间，以及写操作之间是串行的。

 

当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作，其他线程的读、写操作都会等待，直到锁被释放。

写阻塞：

session1	session2
获得表film_text的write锁定 lock table film_text write;
当前session对锁定表的查询、更新、插入操作都可以执行。 select id, title form film_text where id = 1; insert into film_text (id, title) values (2, 'test'); update film_text set title = 'test2' where id = 2;	其他session对锁定表的查询被阻塞，需要等待锁被释放。 select id, title from film_text where id = 1; 等待锁释放
缩放锁 unlock tables;	等待锁释放
	获得锁，执行查询并返回

 

 

一个session使用lock table 给表film_text加了读锁，这个session可以查询表中的记录，但更新或者访问其他表都会提示错误，同时，另一个session可以查询表中的记录，但更新就会出现锁等待。

读阻塞：

session1	session2
所得表film_text的read锁定 lock table film_text read;
当前session可以查询该表的记录 select id, title from film_text where id = 1;	其他session也可以查询该表的记录 select id, title form film_text where id = 1;
当前session不能查询没有锁定的表 select id, name from film where id = 1; error: table 'film' was not locked with lock tables	其他session可以查询或者更新没有锁定的表 select id, name from film where id = 1; update film set name = 'test' where id = 1;
当前session中插入或者更新锁定的表会提示错误 insert into film_text (id, title) values(2, 'test'); error: table 'film_text' was not locked with a read lock and can't be updated update film_test set title = 'test' where id = 1; error: table 'film_text' was not locked with a read lock and can't be updated	其他session更新锁定的表会等待锁 update film_test set title = 'test' where id = 1; 等待锁释放
缩放锁 unlock tables;	等待锁释放
	session获得锁，更新操作

 

 

MyISAM并发插入

MyISAM引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为，其值分别为0、1、2

0： 不允许并发插入。

1： 如果MyISAM表中没有空洞（表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是myslq的默认设置。

2： 无论MyISAM表中有没有空洞，都允许在表尾并发插入记录。

 

MyISAM锁调度

一个进程请求某个MyISAM表的读锁，同时另一个进程也请求同一个表的写锁，那么写进程会先获得锁，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前。

所以大量的更新操作会造成查询操作很难获得读锁，从而造成阻塞。可以使用MyISAM的锁调度调整。

1、通过指定启动参数low-priority-updates，是MyISAM引擎默认给予读请求以优先的权力。

2、通过执行命令SET LOW_PRIORITY_UPDATES = 1，使该连接发出的更新请求优先级降低。

3、通过指定INSERT、UPADATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。

4、通过系统参数max_write_lock_count设置一个合适的值，当一个表的读锁得到这个值后，mysql就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

 

一些耗时长的查询操作，也会使写进程‘饿死’，应用中应该尽量避免长时间允许的查询操作，如果不可避免，应该尽量安排在数据库空闲的时间段执行，比如一些定期统计可以安排在晚上进行。