mysql InnoDB行锁

程序员文章站 2024-01-14 09:33:04

...

mysql的InnoDB存储引擎既支持行级锁，也支持表级锁，默认行级锁。

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

事务及其ACID属性

事务是由一组sql语句组成的逻辑处理单元，具有4个属性；

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全部不执行。

一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态，这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如所有或链表）也都必须是正确的。

隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作形象的独立环境执行，这意味着事务处理过程中的中间状态对外部是可见的，反之亦然。

持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务带来的问题

数据丢失（Lost Update）：当2个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，最后的更新会覆盖其他事务所做的更行。

脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态，这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务的取了这些脏数据，并据此做处理，就会产生未提交的数据依赖关系，即脏读。

不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读取过的数据，却发现其读取出的数据已经发生了改变或某些记录已经被删除了，这种现象就叫不可重复读。

幻读（Phantom Reads）：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其他查询条件的新数据，这种现象叫做幻读。

解决方式

事务隔离有2种实现方式

一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。

二种是不加任何锁，通过一定机制生成一个数据请求时间点额一致性数据快照，并用这个快照来提供一定级别的一致性读取，从用户角度来看，好像数据库可以提供一定级别的多个版本，这种技术叫做数据多版本并发控制，也称为多版本数据库。

隔离级别

隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读	最低级别，只能保证不读取物理上损坏的数据。	是	是	是
已提交读	语句级	否	是	是
可重复读	事务级	否	否	是
可序列化	*别，事务级	否	否	否

行锁争夺

通过

show status like 'innodb_row_lock%';

查看争夺情况。

innodb_row_lock_current_waits：当前正在等待锁定的数量。

innodb_row_lock_time：从系统启动到现在锁定的总时间长度。

innodb_row_lock_time_avg：每次等待锁花费的平均时间。

innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花费的时间。

innodb_row_lock_waits：系统启动后到现在总共等待的次数。

行锁类型

共享锁（S）：允许一个事务取读一行，阻止其他事务获得相同数据集的排他锁。

SELECT * FROM table WHERE .... LOCK IN SHARE MODE;

排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁。

SELECT * FROM table WHERE ....FOR UPDATE;

为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有2种内部使用的意向锁，都是表锁。

意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是自动加的，对于UPDATE、DELETE、INSERT，InnoDB会自动给涉及数据集加排他锁，对SELECT，InnoDB不会加锁。

共享锁

session1	session2
select id, name from author where id = 1;	select id, name from author where id = 1;
当session对id=1的记录加share mode的共享锁； select id, name from author where id = 1 lock in share mode;
	其他session仍然可以查询记录，并也可以对该就加share mode共享锁； select id, name from author where id = 1 lock in share mode;
当sesison对锁定的记录更新时，等待锁； update author set name ="test" where id = 1; 等待
	其他session也对该记录进行更新操作时，则会导致死锁退出。 update author set name ="test" where id = 1; error: Dead lock found when trying to get lock; try restarting transaction;
获得锁后，可以成功更新； update author set name ="test" where id = 1;

排他锁

session1	session2
select id, name from author where id = 1;	select id, name from author where id = 1;
当前session对id=1的记录加for update的排他锁； select id, name from author where id = 1 for update;
	其他sesison也可以查询该记录，但是不能对该记录加排他锁，会等待获得锁； select id, name from author where id = 1; 可以执行 select id, name from author where id = 1 for update; 等待
当前session可以对锁定的记录进行更新操作，更新后释放锁； update author set name = "test" where id = 1; commit;
	其他session获得锁，得到其他session提交的记录。 select id, name from author where id = 1 for update;

注意：在不通过索引条件查询时，InnoDB会锁定表种的所有记录。

间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫间隙，InnoDB也会对这个间隙加锁，这种锁机制就是间隙锁，即Next-Key锁。

InnoDB除了通过范围条件加锁时使用Next-key锁外，如果使用相等条件请求给一个不存在的记录加锁，也会使用Next-key锁。

mysql InnoDB行锁