mysql 的S 锁和X锁的区别

Mysql的锁系统

Shared lock 和 exclusive lock(共享锁和排它锁，也就是读锁和写锁，即read lock 和write lock)

读锁是共享的，或者说是相互不阻塞的，
写锁是排他的，一个写锁会阻塞其他写锁和读锁

共享锁（shared lock S锁）

如果事务T1对数据对象A加上S锁，则事务T可以读A但是不能修改A，其他事务T2只能再对A加S锁，而不是X锁，一直持续到T1释放A上的S锁。

排他锁（exclusive lock X锁）

如果事务T1对数据对象A加上X锁，则事务T可以读取A也可以修改A，其他事务T2不能对该对象加任何锁，一直持续到T释放A上的锁。保证了其他事务在T1释放A上的锁之前不能读取或修改对象A

image.png

锁的粒度和锁的策略

MySQL有三种锁的级别：页级、表级、行级。
MyISAM 和MEMORY存储引擎采用的是表级锁(table-level locking);
BDB 存储引擎采用的是页面锁(page-level locking),但也支持表级锁；
InnoDB存储引擎既支持行级锁（row-level locking) ,也支持表级锁，但默认情况下是采用行级锁。

MySQL 这3中锁的特性可大致归纳如下：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最小，并发度最高；
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

脏读、不可重复读、幻读

脏读：事务A读取了事务B未提交的记录；如果事务B在修改这条记录的时候加了X锁，则事务A就无法读取该条记录了，也就不会出现脏读；

不可重复读：事务A先读取该条记录，但是事务B对该条记录做了改动并提交之后，事务A再次读取记录时，就会获取到不同的值。解决方法是事务A读取该记录时，对该记录加S锁，事务B就无法修改记录，然后不会发生不可重复读。

幻读：当前事务A读取了一个范围的记录，然后另外的事务B向该范围插入了新记录，当前事务A再次读取该范围的记录时，发现了新插入的新记录，我们把新插入的那些记录称之为“幻影记录”。在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多或者少的那一行被叫做 幻行

如何解决幻读

1、多版本并发控制(MVCC) (快照读/一致性读)

多数数据库实现了多版本并发控制，并且都是靠保护数据快照实现的。
InnoDB 每一行记录都冗余了两个字段，一个是行的创建版本，一个行的删除（过期）版本。
具体的版本号（trx_id）存在information_schema.INNODB_TRX表中。
版本号(trx_id)随着每次事务开启自增。事务每次读取数据时候，都会创建版本小于当前事务版本的数据，以及过期版本大于当前版本的数据。
普通的select就是快照读。
select * from table where id=100;
原理：将历史数据存一份快照，所以其他事务增加和删除数据，对于当前事务来说是不可见的。

2. next-key 锁 （当前读）

next-key 锁包含两部分 记录锁（行锁） 间隙锁（gap锁）

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁

对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（next-Key锁）【行锁+gap锁】

记录锁是加在索引上的锁，间隙锁是加在索引之间的。（思考：如果列上没有索引会发生什么？）

select * from account where account_id= 1 for update;
select * from account where account_id = 1 lock in share mode;
insert
update
delete
原理：将当前数据行与上一条数据和下一条数据之间的间隙锁定，保证此范围内读取的数据是一致的。

实例：

假如emp表中只有101条记录，其empid的值分别是 1,2,...,100,101，下面的SQL：

mysql> select * from emp where empid > 100 for update;

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。（如果where条件全部命中，则只会添加记录锁，如果部分命中或者全部命中，则会加Gap锁）

InnoDB使用间隙锁的目的：

1.防止幻读，以满足相关隔离级别的要求。对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；

2.为了满足其恢复和复制的需要。