关于MySQL两种引擎下的锁介绍

18-07-20 来源：[db:作者]

收藏我要投稿

介绍MySQL两种引擎下的锁。

1.两种引擎的锁介绍

MyISAM的锁	MyISAM存储引擎采用的是表级锁，
InnoDB的锁	InnoDB既支持表级锁，有支持行锁，默认情况下是行锁。

InnoDB还支持事务特性

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

总结：两种锁各有优缺点。表级锁更适合用于查询为主，只有少量按索引更新数据的应用，例如Web应用。而行级锁则更适合有大量按照索引条件并发更新少量不同数据，同时又有并发处理查询的引用。如一些在线事务处理的系统。

MyISAM的表级锁有两种模式：表共享读锁、表独占锁，他们的兼容性如下表格所示

从上可以看出，MyISAM的表读操作不会阻塞其他用户对同一表的读操作，但是会阻塞对同一表的写操作；而写操作则会阻塞其他用户对同一表的读和写操作。写操作和读操作之间，以及写操作之间是串行的。

1.>:默认情况下MyISAM在执行Select语句(查询)时，自动会加表读锁；而在执行update,delete,insert语句时，会自动给设计到的表加上写锁，这个过程用户完全不用做任何干预。

2>:显示加表锁。例如在执行sql语句前 lock table user write (给user表加表写锁)，用处一般是为了模拟事务存在，例如有一个订单表，还有一个订单明细表，为了检查这两个表的金额是否相符，那么就需要执行如下

select sum(total) from orders;

select sum(subtotal) from order_detail;

这两个表如果不给其加上锁，有可能某一个表会添加修改数据，造成数据读取不一致。所以需要在执行这两句前执行

lock tables orders read local,order_detail read local; //这里的local表示允许在表尾并发插入

.....

unlock tables; //解锁

1>:显示加锁，如上文中如果加了某一种锁，那么只能执行相应的操作，加了读锁，只能读取select,不能update,delete,insert.反之，隐式锁同理。

2>: 执行了local table 后，当前会话只能访问当前加锁的表，不能访问其他表，否则报错。

3>:对当前的表显示加锁后，如果当前表用了别名，用了多次也会报错,隐式不会有这样的情况。如下：

lock table actor read;

selelct a.name b.name from actor a, actor b where a.name = b.name and a.name="vison"; //报错

需要修改加锁条件为：lock table actor as a read,actor as b read;

4>:MyISAM总是一次获取SQL语句所需要的全部锁，这也是MyISAM不会出现死锁的原因。

总体而言，MyISAM表的读和写是串行的，但是在一定条件下也是支持查询和插入并发进行。这个存储引擎又给系统变量concurrent_insert专门用于控制并发插入的行为。

concurrent_insert的值	并发插入可行性
concurrent_insert=0	不允许并发插入
concurrent_insert=1	如果MyISAM表没有空洞(即表中间没有删除行)，则允许插入在表尾，(通过optimize table 可以去除空洞,自行百度)
concurrent_insert=2	无论有没有空洞，都允许在表尾并发插入数据

问题：如果一个读请求和一个写请求同时到达，那么优先让哪一个进程的请求获取到锁呢？

答案：写进程获取，就算是读请求先到达进入排队，也会先让写请求插队，这是由于MySQL默认认为写请求比读请求重要，这也是MyISAM表不太适合有大量更新操作和查询操作的原因，大量的更新操作会让查询很难获取到锁。不过可以通过启动参数调节

set low_priority_updates = 1 表示降低更新优先级,当然还有 insert,update，delete 。当前也可以设置max_write_lock_count设置一个合适的值，当表的写锁到达一个值后，就将写锁优先级降低。