mysql的 myisam引擎锁问题

一，概述

    相对其他数据库而言，mysql的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。比如：myisam和memory存储引擎采用的是表级锁，bdb采用的是页面锁，但也支持表级锁，innodb存储引擎即支持行级锁也支持表级锁，但默认情况下是行级锁。

  三种锁的特性大致归纳如下：

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

     （2）行级锁：开销大，加锁慢；会出现锁死；锁粒度最小，发生锁冲突的概率最低，并发度也高。

     （3）页面锁： 开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

   所以，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用。而行级锁则更适合于有大量按照索引条件并发更新少量不同数据，同时又有并发查询的应用。

关于innodb锁问题可以看：http://blog.csdn.net/fuzhongyu2/article/details/52743680

二，myisam表锁

     1，通过查看状态变量来分析系统上表锁定争夺

mysql> show status like 'table_lock%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 101   |
| Table_locks_waited    | 0     |
+-----------------------+-------+
2 rows in set (0.00 sec)

   以上两个数据，如果table_locks_waited的值更高，则说明存在较严重的表级锁争用情况。

     2，表级锁的锁模式

 mysql的表级锁有两种模式：表共享读锁(table read lock)和表独占写锁(table write lock)。锁模式的兼容性如下：

  

mysql表锁兼容性

	none	读锁	写锁
读锁	是	是	否
写锁	是	否	否

      可见，对myisam表的读操作不会阻塞其他用户对同一表的读操作，但会阻塞对同一表的写操作.

                  对myisam表的写操作则会阻塞其他用户对同一表的读和写操作。

                  myisam表的读操作与写操作之间，以及写操作之间是串行的。

下面我们来看一个例子：

      首先，我们创建表并插入几条数据，看一下表的结构：

mysql> create table t18(id int auto_increment primary key,name varchar(63),age int(3));
Query OK, 0 rows affected (0.33 sec)

mysql> insert into t18(name,age)values('fzy1',1),('fzy2',2),('fzy3',3);
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t18;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | fzy1 |    1 |
|  2 | fzy2 |    2 |
|  3 | fzy3 |    3 |
+----+------+------+
3 rows in set (0.00 sec)

写阻塞读例子

session_1	session_2
获得表t18的write锁 mysql> lock table t18 write; Query OK, 0 rows affected (0.00 sec)
当前session对锁定的表的查询，更新，插入操作都可以执行 mysql> insert into t18(name,age)values('fzy4',4); Query OK, 1 row affected (0.04 sec) mysql> update t18 set age=5 where id=2; Query OK, 1 row affected (0.11 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from t18; +----+------+------+ | id | name | age | +----+------+------+ | 1 | fzy1 | 1 | | 2 | fzy2 | 5 | | 3 | fzy3 | 3 | | 4 | fzy4 | 4 | +----+------+------+ 4 rows in set (0.00 sec)	其他session对锁定的表操作被阻塞 mysql> select * from t18; 等待
释放锁 mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)	等待
	session2获得锁，返回查询结果 +----+------+------+ | id | name | age | +----+------+------+ | 1 | fzy1 | 1 | | 2 | fzy2 | 5 | | 3 | fzy3 | 3 | | 4 | fzy4 | 4 | +----+------+------+ 4 rows in set (1 min 21.56 sec)

 

读阻塞写例子

session_1	session_2
获得t18的read锁定 mysql> lock table t18 read; Query OK, 0 rows affected (0.00 sec)
当前session可以查询该表记录 mysql> select * from t18; +----+------+------+ | id | name | age | +----+------+------+ | 1 | fzy1 | 1 | | 2 | fzy2 | 5 | | 3 | fzy3 | 3 | | 4 | fzy4 | 4 | +----+------+------+ 4 rows in set (0.00 sec)	session_2也可以获得该表记录 mysql> select * from t18; +----+------+------+ | id | name | age | +----+------+------+ | 1 | fzy1 | 1 | | 2 | fzy2 | 5 | | 3 | fzy3 | 3 | | 4 | fzy4 | 4 | +----+------+------+ 4 rows in set (0.00 sec)
当前session不能查询未锁定的表，但可以看锁定的表（这个会话中锁定的表） mysql> select * from t17; ERROR 1100 (HY000): Table 't17' was not locked with LOCK TABLES	其他session可以查询和更新未锁定的表 mysql> select * from t17; +----+------+------+--------+ | id | name | age | intro | +----+------+------+--------+ | 1 | fzy1 | 1 | intro1 | | 2 | fzy2 | 2 | intro2 | | 3 | fzy3 | 3 | intro3 | | 4 | hsq | 4 | intro4 | +----+------+------+--------+ 4 rows in set (0.00 sec)
当前session中插入或更新数据都会报错 mysql> insert into t18(name,age)values('fzy5',5); ERROR 1100 (HY000): Table 't18' was not locked with LOCK TABLES	其他session更新锁定的表会等待获得锁 mysql> insert into t18(name,age)values('fzy6',6); 等待
释放锁 mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)	等待
	获得锁执行 Query OK, 1 row affected (2 min 33.25 sec)

      当多次使用lock tables时，不仅需要一次锁定用到的所有表，而且，同一个表在sql语句中出现多少次，就要通过与sql语句中相同的别名来锁定多少次，否则也会出错。

例子：

  

mysql> lock table t18 read;
Query OK, 0 rows affected (0.00 sec)

mysql> select a.name,b.name from t18 a,t18 b where a.id=b.id;
ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES

   我们做了一个表本身的连接，查询出错，正确的做法如下：

##先释放上面的锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

##对别名分别锁定
mysql> lock table t18 a read,t18 b read;
Query OK, 0 rows affected (0.00 sec)

mysql> select a.name,b.name from t18 a,t18 b where a.id=b.id;
+------+------+
| name | name |
+------+------+
| fzy1 | fzy1 |
| fzy2 | fzy2 |
| fzy3 | fzy3 |
| fzy4 | fzy4 |
| fzy6 | fzy6 |
| fzy6 | fzy6 |
+------+------+
6 rows in set (0.01 sec)

3，如何加表锁

  myisam在执行查询语句(select)前，会自动给涉及的所有表加读锁，在执行更新操作(update,delete,insert)前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用lock table命令个myisam表显式加锁。这边我们加锁是为了模拟事务操作，实现对某一时间点多个表的一致性读取。

  当我们需要同时对两张表锁定的时候：

mysql> lock tables t17 read local,t18 read local;
Query OK, 0 rows affected (0.00 sec)

#执行操作
...

 mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

    （1）上面的例子lock tables时加了local 其作用就是在满足myisam表并发插入的条件情况下，允许其他用户在表尾并发插入记录

    （2）在用lock tables给表显式加表锁的时候，必须同时取得所有涉及表的锁，并且mysql不支持锁升级，也就是说，在执行lock tables后只能访问显式加锁的这些表，不能访问未加锁的表。在自动加锁的情况下，myisam总是一次读取sql语句所需要的全部锁，这也是myisam不会出现死锁的原因。

4，并发插入

 

       myisam表的读和写是串行的，但这是就总体而言的，在一定条件下，myisam表也支持查询和插入操作的并发。

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

当值为0时，不允许并发插入，

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

当值为2时，无论myisam表中有没有空洞，都允许在表尾并发插入记录。

5，myisam的锁的调度

     myisam存储引擎的读锁和写锁是互斥的，读写操作是串行的。那么，一个进程请求某个myisam表的读锁，同时另一个进程也请求同一表的写锁，mysql怎么处理呢？

答案是写进程先获得锁，不仅如此，即使是读请求先到锁等待队列，写请求后到，写锁也会插入到读锁之前。这是因为mysql认为写请求一般比读请求更重要。不过我们可以通过一些设置来调节myisam的调度行为。

  （1）通过指定启动参数low-priority-updates，使myisam引擎默认给予读请求以优先的权利

  （2）通过执行命令set low_priority_updates=1，使该连接发出的更新请求优先级降低

   （3）通过指定insert  ,update ,delete 语句的low_priority属性，降低该语句的优先级。

三，小结

  

  好，下面对锁问题总结一下：主要介绍了mysql中myisam表级锁实现特点：

     对于myisam的表锁，主要有一下几点：

       （1）共享锁(s)之间是兼容的，但共享锁(s)与排它锁(x)之间，以及排它锁写锁(x)之间是互斥的，也就是说读和写是串行的。

        （2）在一定条件下，myisqm允许查询和插入并发执行，可以利用这一点来解决应用中对同一表查询和插入的锁争用问题。

       （3）myisam默认的锁调度机制是写优先，这并不一定适合所有应用，用户可以设置low_priority_updates参数，或在insert ,update ,delete 语句中指定 low_prority选项来调节读写锁的争用。

       （4）由于表锁的锁定粒度打，读写之间又是串行的，因此，如果更新操作较多，myisqm表可能会出现严重的锁等待，可以考虑采用innodb表来减少冲突。