MySQL MyISAM与表锁

MySQL MyISAM与表锁

 

在数据库中，除了CPU、内存、IO等的争用外，数据也是一种供许多用户共享的资源，如何保证数据并发的一致性、有效性是所有数据库必须解决的问题，锁冲突也是影响数据库并发性能的一个重要因素。MySQL中不同的存储引擎之间的锁机制不一定相同，例如MyISAM和MEMORY采用的是表锁，BDB采用的是页面锁，但野支持表锁，InnoDB默认是行锁，但也支持表锁。

 

MySQL锁大体分三种：

1. 表锁：开销小，加锁快；不会出现死锁；锁粒度大，发生锁冲突几率高，并发度最低
2. 行锁：开销大，加锁慢；会出现死锁；锁粒度小，发生锁冲突几率小，并发度最高
3. 页锁：开销和加锁时间介于表锁和行锁之间；会出现死锁，锁粒度介于表锁和行锁之间

 仅从锁的角度来讲，表锁使用于以查询为主，只有少量按索引条件更新数据的应用，如WEB应用；而行级锁更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理(OLTP)系统。

 

MyISAM表锁

MyISAM只支持表锁，这也是早期MySQL的唯一支持的锁。而随着应用对事务完整性和并发性要求的不断提高，MySQL才开始开发基于事务的存储引擎，后来慢慢出现了支持页锁和BDB和支持行锁的InnoDB。

 

表锁有共享读锁和独占写锁两种模式。读锁与读锁是兼容的，读锁与写锁是互斥的，写锁与写锁是互斥的。也就是说，MyISAM表的一个连接的读操作是不会阻塞其它连接的读操作的，但是会阻塞其它连接的写操作，而一个连接的写操作会阻塞其它连接的读操作和写操作。

 

MyISAM在执行查询(SELECT)时会自动给涉及到的表加上读锁，在执行更新(UPDATE、DELETE、INSERT)等，会自动给涉及到的表加上写锁。但是我们也可以显示加锁/解锁：

 

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE

UNLOCK TABLES

 LOCAL关键字用于指示允许并发插入，后面会讲到

 

以下演示一些情况锁定情况：

 读阻塞写

 

以下黑背景色的会话A，灰背景色的为会话B

 

会话A给user加读锁，加锁后会话A可以查询，但不可以插入

会话B可以查询，但插入会阻塞：

 

会话A释放锁后，会话B才插入成功：

 

只能访问加锁的表

对表加锁后只能访问加了锁的表，不能访问没加锁的表：

 

 

 

凡是用到的别名都要加一次锁

 在使用别名时，必须对别名也加锁，而且即使是同一个表，如果别名不一样也需要另外加锁：

 

 

并发插入

MyISAM表的读和写是串行的，但在一定条件下，MyISAM表也支持查询和插入的并发执行，MyISAM引擎有一个concurrent_insert变量，专门用来控制其并发插入的行为，取值为0(或NEVER)，1(或AUTO)和2(或ALWAYS)，当concurrent_insert为0时，不允许并发插入，当concurrent_insert为1时如果表中没有空洞（即表的中间没有被删除的行），那么允许一个会话读表的同时，另一个表在表尾插入记录，这是默认值；当concurrent_insert为2时，允许在表尾并发插入记录。

 

接下来演示这三种模式的效果

 设置concurrent_insert为0：

 会话A给user表加锁（注意此处一定要加LOCAL关键字）

 可以看到，会话B插入会阻塞：

 

设置concurrent_insert为1，会话A给user加读锁，

 会话B依然可以插入，因为此时表没有空间碎片(可以用optimize table table_name整理碎片)

删除一些数据后(制造碎片)再上锁：

此时会话B再次试图插入数据，可以看到其会被阻塞：

 

设置concurrent_insert为2，会话A给user加读锁：

 会话B可以在插入数据：

 

如果你的应用中用了MyISAM的表，并且你想有一定的插入并发，那么就可以设置concurrent_insert为2，并且定期地在空闲时期执行OPTIMIZE TABLE来整理碎片。

 

另外值得注意的是，当一个会话A请求MyISAM表的读锁，另一个会话B也请求同一个表的写锁时，MySQL会让会话B获得写锁，甚至，假如锁等待队列中读请求先到，写请求后到，MySQL同样也会让写请求插入到读请求前面。这也是为什么MyISAM表不太适合于有大量更新操作和查询操作应用的原因，大量的更新操作会早场查询操作很难获得锁从而可能阻塞很长一段时间。对此，我们可以做一些设置来打破这个规则：

1. 通过启动参数low-priority-updates，使MyISAM引擎默认给予读请求更高的优先级
2. 通过set low-priority-updates = 1，使当前连接发出的所有更新请求的优先级降低
3. INSERT、UPDATE、DELETE语句都有一个LOW_PRIORITY属性，通过该属性，降低该语句的优先级，例如insert low_priority into user values ...

 

 参考：http://dev.mysql.com/doc/refman/5.7/en/lock-tables.html

深入浅出MySQL