Mysql锁总结

数据库锁

概述

相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);InnoDB存储引擎既支持行级锁( row-level locking),也支持表级锁,但默认情况下是采用行级锁。

MySQL主要的两种锁的特性可大致归纳如下:

  • 表级锁: 开销小,加锁快;不会出现死锁(因为MyISAM会一次性获得SQL所需的全部锁);锁定粒度大,发生锁冲突的概率最高,并发度最低。
  • 行级锁: 开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

考虑上述特点,表级锁使用与并发性不高,以查询为主,少量更新的应用,比如小型的web应用;而行级锁适用于高并发环境下,对事务完整性要求较高的系统,如在线事务处理系统。

MyISAM锁细述

(1). 锁模式

MySQL的表级锁有两种模式: 表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。

(2). 如何加锁

当MyISAM在执行查询语句时,会自动给涉及到表加读锁,在执行更新操作时,会加写锁。当然用户也可以用LOCK TABLE 去显式的加锁。显式的加锁一般是应用于:需要在一个时间点实现多个表的一致性读取,不然的话,可能读第一个表时,其他表由于还没进行读操作,没有自动加锁,可能数据会发生改变。并且显示加锁后只能访问加锁的表,不能访问其他表。

(3). 并发插入

MyISAM存储引擎有个系统变量 concurrent_insert,专门用来控制并发插入的行为,可以取 0 , 1 , 2。

0表示不允许并发插入,1表示表中间没有删除的行时可以在表末尾插入,2表示总是可以插入。

一般如果对并发要求比较高的情况下,可以设置为2,总是可以插入,然后定期在数据库空闲时间对表进行optimize。

(4). 锁的调度

需要注意的是,其中读操作不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;并且当写锁和读锁同时被申请时,优先获得写锁,这也这正是表级锁发生锁冲突概率最高的原因,因为写锁可能会一直阻塞读锁,所以不适合有大量写操作的环境下工作。这一问题可以通过设置low-priority-updates这一启动参数来降低写的优先级。 虽然写锁优先于读锁获取,但是长时间的查询操作也可能会让写操作饿死,所以尽量避免一条SQL语句执行所有的查询,应该进行必要的分解。

InnoDB锁细述

由于InnoDB支持事务,并默认是使用行级锁,所以InnoDB的锁问题和MyISAM锁问题还是有蛮大差别的。

(1). 锁模式

共享锁(S)和排他锁(X),分别类似于MyISAM的读锁和写锁。对于 UPDATE、 DELETE 和 INSERT 语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通 SELECT 语句,InnoDB不会加任何锁。

(2). 如何加锁

可以显式的加锁,用lock in share mode 显式的加共享锁,用 for update 显式的加排他锁。

需要注意的是,如果线程A加了共享锁后,线程B对同一个表加了共享锁,那么两个线程需要进行更新操作时会产生死锁。所以,进行更新操作时最好加排他锁。

(3). InnoDB行锁的实现方式——索引加锁

这一点与Oracle不同,所以这也意味着(重要):1. 只有通过索引条件检索数据时,InnoDB才会使用行级锁,否则会使用表级锁。 2. 即使是访问不同行的记录,如果使用的是相同的索引键,会发生锁冲突。 3. 如果数据表建有多个索引时,可以通过不同的索引锁定不同的行。

(4). 间隙锁

InnoDB支持事务,为了满足隔离级别的要求,InnoDB有个间隙锁,当使用范围查找时,InnoDB会给满足key范围要求,但实际并不存在的记录加锁。例如:select * from user where id > 100 for updata 会给ID>100的记录加排他锁,满足这个范围,但不存在的记录,会加间隙锁,这样可以避免幻读,避免读取的时候插入满足条件的记录。

(5). 隔离级别与锁

一般来说,隔离级别越高,加锁就越严格。这样,产生锁冲突的概率就越大,一般实际应用中,通过优化应用逻辑,选用 可提交读 级别就够了。对于一些确实需要更高隔离级别的事务,再通过set session transaction isolation level+"级别" 来动态改变满足需求。

死锁

MyISAM是没有死锁问题的,因为他会一次性获得所有的锁。InnoDB发生死锁后一般能自动检测到,并使一个事务释放锁并回退,另一个事务获得锁,继续完成事务。

在应用中,可以通过如下方式来尽可能的避免死锁:

(1) 如果不同的程序会并发的存取多个表,应尽量约定以相同的顺序来访问表,这样可以大大降低产生死锁的机会。

(2) 在程序以批量方式处理数据时,如果事先对数据排序,保证每个线程按固定的顺序来处理记录,也可以大大的降低出现死锁的可能。

案例

秒杀一

## 查询库存,由于是主键查询使用到了索引,所以是行级锁
SELECT number FROM seckill WHERE seckill_id=? FOR UPDATE
## 如果库存大于秒杀数则更新,UPDATE 操作也是行级锁
UPDATE seckill  SET number=number-1 WHERE seckill_id=?

秒杀二

//直接更新数据,如果count为1秒杀成功否则失败
UPDATE seckill  SET number=number-1 WHERE seckill_id=? AND number>0

秒杀三

//获取商品版本号以及剩余数量
SELECT version,number  FROM seckill WHERE seckill_id=?
//判断剩余数量是否充足并更新
UPDATE seckill  SET number=number-?,version=version+1 WHERE seckill_id=? AND version = ? 
//如果更新数量等于1秒杀成功否则失败

Mysql innodb虽是锁行的,但是如果没有索引,或者索引唯一性不是特别强,那就要锁表了。

加锁对并发访问的影响体现在锁的粒度上,可见行锁粒度最小,并发访问最好,页锁粒度最大,表锁介于2者之间。

锁有两种:悲观锁和乐观锁。悲观锁假定其他用户企图访问或者改变你正在访问、更改的对象的概率是很高的,因此在悲观锁的环境中,在你开始改变此对象之前就将该对象锁住,并且直到你提交了所作的更改之后才释放锁。悲观的缺陷是不论是页锁还是行锁,加锁的时间可能会很长,这样可能会长时间的限制其他用户的访问,也就是说悲观锁的并发访问性不好。

与悲观锁相反,乐观锁则认为其他用户企图改变你正在更改的对象的概率是很小的,因此乐观锁直到你准备提交所作的更改时才将对象锁住,当你读取以及改变该对象时并不加锁。可见乐观锁加锁的时间要比悲观锁短,乐观锁可以用较大的锁粒度获得较好的并发访问性能。但是如果第二个用户恰好在第一个用户提交更改之前读取了该对象,那么当他完成了自己的更改进行提交时,数据库就会发现该对象已经变化了,这样,第二个用户不得不重新读取该对象并作出更改。这说明在乐观锁环境中,会增加并发用户读取对象的次数。

参考

https://www.cnblogs.com/zhanht/p/5431273.html

https://www.cnblogs.com/liujiacai/p/7605612.html

https://www.cnblogs.com/claireyuancy/p/7258314.html