Mysql数据库之锁介绍

x33g5p2x  于2021-03-14 发布在 Mysql  
字(2.3k)|赞(0)|评价(0)|浏览(269)

什么是锁

锁是数据库系统区别于文件系统的关键特性。锁机制用于管理对共享资源的并发访问。

lock 与 latch

latch 一般称为闩锁(轻量级的锁),因为其要求锁定的世界必须非常短。若持续的时间长,则应用性能会非常差。分为mutex(互斥量)和rwlock(读写锁)。其目的是用来保证并发线程操作临界资源的正确性,并且通常没有死锁检测的机制。

lock 的对象是事务,用来锁定的是数据库中的对象,如表、页、行。并且一般的对象尽在事务commit 或者rollback后释放。有死锁机制的。

对于latch 可以通过show engine innodb mutex来进行查看

Innodb 存储引擎中的锁

(1)共享锁(s),允许事务读一行数据。
(2)排它锁(x)事务删除或者更新一行数据。


意向锁是将锁定的对象分为多个层次,意向锁意味着事务希望在更细粒度(fine granularity)上进行加锁。

在innodb 1.0版本之前,用户只能通过命令show full processlist,show engine innodb status等来查看当前数据库中锁的请求,然后在判断事务锁的情况。 1.0版本之后 在INFORMATION_SCHEMA 架构下添加了表INNODB_TRX、INNODB_LOCK、INNODB_LOCK_WAITS。这三张表分析锁问题。

如上图可以通过下面的命令,获取锁的信息:
select * from information_schema.innodb_trx\G;


输入select * from information_schema.innodb_locks;
可以获取如上图的相关信息

InnoDB存储引擎对于select语句支持两种一致性的锁定读(locking read)操作:

(1)select ... for update(排它锁 X)其他事务不能对已锁定的行加上任何锁。
(2)select ... lock in share mode(共享锁) 其他事务可以向被锁定的行加s锁,但是如果加x锁就会别阻塞。

自增长及锁

mysql5.1.22版本开始 innodb存储引擎提供了innodb_autoinc_lock_mode来控制自增长的模式 默认值为1。

innodb:必须是索引的第一列 myisam不需要。

外键和锁

innodb 使用外键如果没有加索引 搜索引擎会自动将其添加索引。因为这样就可以避免表锁。

锁的算法

行锁的3中算法

(1)Record Lock:单行记录上的锁 锁住的是索引记录 如果没有任何一个索引 那么使用隐式的主键来进行锁定。
(2)Gap Lock:间隙锁,锁定一个范围,但不包含记录本身
(3)Next-key Lock : Gap Lock + Record Lock,锁定一个范围,并锁定记录本身。

create table z (a int,b int ,primary key(a),key(b));
insert into z select 1,1;
insert into z select 3,1;
insert into z select 5,3;
insert into z select 7,6;
insert into z select 10,8;

select * from z where b=3 for update;

```~
很明显,这时sql语句通过索引列b进行查询, 会使用传统的Next-key Lock 技术加锁,并且由于两个索引,其需要分别进行锁定。对于聚集索引,其仅对列a等于5的索引加上Record Lock。 而对于辅助索引,其加上的是Next-Key Lock,锁定的范围是(1,3),特别需要注意的是,InnoDB存储引擎还会对辅助索引下一个键位加上gap lock,即还有一个辅助索引范围为(3,6)的锁,因此,若在新会话B中运行下面的sql语句执行会被堵塞:
`select * from z where a= 5 lock in share mode;`(第一条)
`insert into z select 4,2;`(第二条)
`insert into z select 6,5;`(第三条)
第一条语句不会被执行,因为会话A中执行的sql语句已经吧聚集索引列a=5的值加上X锁,因此执行会阻塞。
第二天语句主键插入没有4,没有问题,但是插入辅助索引值2在锁定的范围(1,3)中,因此执行同样会被阻塞。第三个sql语句,插入的主键没有被锁定,5也不在(1,3)之间。但插入的值5在另一个锁定的范围(3,6)中,故同样需要等待。
下面的sql语句不会被阻塞,可以立即执行:
`insert into z select 8,6;`
`insert into z select 2,0;`
`insert into z select 6,7;`

若此时没有Gap Lock 锁定(3,6),那么用户可以插入索引b列为3的记录,这会导致会话A中的用户再次执行插叙时会返回不同的记录,即导致 phantom problem(幻读)问题的产生。

可以通过一些的方式显式地关闭Gap Lock;
(1)将事务的隔离级别设置为READ COMMITTED
(2)将参数innodb_locks_unsafe_for_binlog设置为1
在上述的配置下,除了外键约束和唯一性检查依然需要的Gap Lock,其余情况仅使用Record Lock进行锁定。 上述设置破坏了事务的隔离性,并且对于replication(复制),可能会导致主从数据的不一致。 而且从性能上来看 read committed 不会优于事务隔离级别read repeatable。
对于inset的操作, 如果被锁定则不允许查询。 会话A中表示已经锁定了  所以其他会话插入同样会导致阻塞。


####  解决 phantom problem(幻读)

相关文章