mysql全局锁和表锁 mysql锁实现

转载

autohost 2023-08-21 01:18:45

文章标签 mysql全局锁和表锁加锁死锁隔离级别 文章分类 MySQL 数据库

锁机制在程序中是最常用的机制之一，当一个程序需要多线程并行访问同一资源时，为了避免一致性问题，通常采用锁机制来处理。在数据库的操作中也有相同的问题，当两个线程同时对一条数据进行操作，为了保证数据的一致性，就需要数据库的锁机制。每种数据库的锁机制都自己的实现方式，mysql作为一款工作中经常遇到的数据库，它的锁机制在面试中也经常会被问到。所以本文针对mysql数据库，对其锁机制进行总结。

mysql的锁可以分为服务层实现的锁，例如Lock Tables、全局读锁、命名锁、字符锁，或者存储引擎的锁，例如行级锁。InnoDB作为MySQL中最为常见的存储引擎，本文默认MySQL选择InnoDB作为存储引擎，将MySQL的锁和InnoDB实现的锁同时进行讨论。

Innodb的行锁

InnoDB存储引擎在也实现了自己的数据库锁。一般谈到InnoDB锁的时候，首先想到的都是行锁，行锁相比表锁有一些优点，行锁比表锁有更小锁粒度，可以更大的支持并发。但是加锁动作也是需要额外开销的，比如获得锁、检查锁、释放锁等操作都是需要耗费系统资源。如果系统在锁操作上浪费了太多时间，系统的性能就会受到比较大的影响。

InnoDB实现的行锁有共享锁(S)和排它锁(X)两种

共享锁：允许事务去读一行，阻止其他事务对该数据进行修改

排它锁：允许事务去读取更新数据，阻止其他事务对数据进行查询或者修改

两个加锁方式，一个是SELECT…FOR UPDATE,SELECT…LOCK IN SHARE MODE。SELECT FOR UPDATE能为数据添加排他锁，LOCK IN SHARE MODE为数据添加共享锁。这两种锁，在事务中生效，而当事务提交或者回滚的时候，会自动释放锁。遗憾的是，当我们在项目中遇到锁等待的时候，并没有办法知道是哪个线程正在持有锁，也很难确定是哪个事务导致问题。但是我们可以通过这几个表来确认消息Information_schema.processList、Information_schema.innodb_lock_waits、Information_schema.innodb_trx、Information_schema.innodb_locks来获取事务等待的状况，根据片面的锁等待状况来获取具体的数据库信息。

隐式加锁：SELECT FOR UPDATE和LOCK IN SHARE 这种通过编写在mysql里面的方式对需要保护的数据进行加锁的方式称为是显式加锁。还有一种加锁方式是隐式加锁，除了把事务设置成串行时，会对SELECT到的所有数据加锁外，SELECT不会对数据加锁（依赖于MVCC）。当执行update、delete、insert的时候会对数据进行加排它锁（select查询的是快照区所以不需要上锁）。

索引和锁：InnoDB在给行添加锁的时候，其实是通过索引来添加锁，如果查询并没有用到索引，就会使用表锁。

死锁

形成的原因：

1.死锁一般是事务相互等待对方资源（锁资源），最后形成环路造成的

如何避免死锁：

1.大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小（事务提交才能释放锁资源）。
2.为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。
3.在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
例如：一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。
4.降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。（也可以说：如果你正使用锁定读，（SELECT … FOR UPDATE或 … LOCK IN SHARE MODE），试着用更低的隔离级别，比如READ COMMITTED。）
5.修改多个表或者多个行的时候，将修改的顺序保持一致。

产生死锁如何解决

第一种：

1.查询是否锁表

show OPEN TABLES where In_use > 0;

2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

show processlist

3.杀死进程id（就是上面命令的id列）

kill id

第二种：

1.查看下在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2.杀死进程id（就是上面命令的trx_mysql_thread_id列）

kill 线程ID

其它关于查看死锁的命令：

1：查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

GAP锁

什么是gap锁：

以前的Mysql在repetable read级别下，还会出现幻读，现在的已经不会了，原因就是因为多了个Gap锁

工作方式：

例子一

session A	session B
select * from test where n = 105 for update;

	（1）update test set n = 103 where n = 102;	堵塞
	（2）insert into test (id,n) values(2,102);	执行成功，因为(2,102)已经不在锁定的间隙范围内了，
	(3) insert into test (id,n) values(4,104);	堵塞
	（4）insert into test (id,n) values(2,104);	堵塞
	（5）update test set n = 103 where n = 107;	堵塞
	（6）insert into test (id,n) values(6,107);	堵塞
	（7）insert into test (id,n) values(8,107);	执行成功，因为(2,102)已经不在锁定的间隙范围内了，

例子二

Repeatable Read隔离级别，id上有一个非唯一索引，执行delete from t1 where id = 10; 假设选择id列上的索引进行条件过滤，最后的加锁行为，是怎么样的呢？同样看下面这幅图：

mysql全局锁和表锁 mysql锁实现_加锁_02

其实这个多出来的GAP锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。确实，GAP锁锁住的位置，也不是记录本身，而是两条记录之间的GAP。所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。