mysql锁

mac2022-06-30 88

1. 什么是锁

锁是计算机协调多个进程或纯线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂

2. mysql锁介绍

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。

MySQL大致可归纳为以下3种锁:

全局锁:锁的是整个database。由MySQL的SQL layer层实现的全局锁:锁的是整个database。由MySQL的SQL layer层实现的行级锁:锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。按照锁的功能来说分为:共享读锁和排他写锁。按照锁的实现方式分为:悲观锁和乐观锁(使用某一版本列或者唯一列进行逻辑控制)

表级锁和行级锁的区别:

表级锁:开销小，加锁快;不会出现死锁;锁定粒度大，发生锁冲突的概率最高，并发度最低;

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

3. mysql锁

1. mysql表级锁

1. 查询表级锁争用情况

2. 表锁

1. 手动操作表锁

增加表锁

语法: lock table 表名称1 read, 表名称2 write; mysql> lock table sms read; Query OK, 0 rows affected (0.00 sec)

查看表锁

删除表锁

mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)

2. 环境准备

CREATE TABLE mylock ( id int(11) NOT NULL AUTO_INCREMENT, NAME varchar(20) DEFAULT NULL, PRIMARY KEY (id) ); INSERT INTO mylock (id,NAME) VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

3. 读锁(read lock)

session1:

#加锁之前可以查看任何表的任何数据 #加锁 mysql> lock table mylock read; Query OK, 0 rows affected (0.00 sec) #mylock表因为加了锁, 所以可以查看 mysql> select * from mylock; +----+------+ | id | NAME | +----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | +----+------+ 4 rows in set (0.00 sec) #其他表就没法查看表的数据, mysql> select * from sms; ERROR 1100 (HY000): Table 'sms' was not locked with LOCK TABLES #当向mylock表插入数据时候 mysql> insert into mylock (id, name) values (5, 'e'); ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated

session2:

#使用其他窗口 mysql> select * from sms limit 1; +----+---------------------+---------+--------------+----------+--------+------+------+-----------+-----------------+------------+ | id | time | host | TRIGGER_NAME | DETAIL | STATUS | type | zone | send_type | sms_send_status | alarm_type | +----+---------------------+---------+--------------+----------+--------+------+------+-----------+-----------------+------------+ | 1 | 2018-05-25 15:40:46 | 1.1.1.1 | ajing | cpu load | ok | 1 | lf | 2 | 2 | 1 | +----+---------------------+---------+--------------+----------+--------+------+------+-----------+-----------------+------------+ 1 row in set (0.00 sec) ##当插入数据的时候, 一直卡在这里, 等待锁释放 mysql> insert into mylock (id, name) value (5, 'e'); ##当上一个窗口释放连接, 插入立马生效

4.写锁(write lock)

session1:

#加锁 mysql> lock table mylock write; Query OK, 0 rows affected (0.00 sec) #修改数据 mysql> update mylock set name='ajing' where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 #查看数据 mysql> select * from mylock; +----+-------+ | id | NAME | +----+-------+ | 1 | ajing | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+-------+ 5 rows in set (0.00 sec) #释放锁 mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)

session2:

#一直卡在这里, 等待所得释放 mysql> select * from mylock; #当窗口1释放完锁以后, 展示如下数据: +----+-------+ | id | NAME | +----+-------+ | 1 | ajing | | 2 | b | | 3 | c | | 4 | d | | 5 | e | +----+-------+ 5 rows in set (2 min 15.36 sec)

3. 元数据锁(meta data lock MDL)

1. 元数据锁介绍

MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁;当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

2. 元数据锁演示

我们可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁。由于 session B 需要的也是 MDL 读锁，因此可以正常执行。

之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。

如果只有 session C 自己被阻塞还没什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。

你现在应该知道了，事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。

4. InnoDB表级锁

InnoDB也实现了表级锁，也就是意向锁，意向锁是mysql内部使用的，不需要用户干预。

意向共享锁(IS):事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。意向排他锁(IX):事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁

意向锁和行锁可以共存，意向锁的主要作用是为了【全表更新数据】时的性能提升。否则在全表更新数据时，需要先检索该范是否某些记录上面有行锁。

共享锁(S)排他锁(X)意向共享锁(IS)意向排他锁(IX)共享锁(S)兼容冲突兼容冲突排他锁(X)冲突冲突冲突冲突意向共享锁(IS)兼容冲突兼容兼容意向排他锁(IX)冲突冲突兼容兼容

2. mysql行级锁

1. 行级锁介绍

mysql的行级锁, 是由存储引擎来实现的, 这里我们主要讲解InnoDB行级锁

InnoDB的行级锁, 按照锁定范围划分, 分为三种:

记录锁(Record Locks): 锁定索引中一条记录间隙锁(Gap Locks): 要么锁定索引记录中的值, 要么锁定一个索引记录前面的值或者索引记录后面的值Next-key Locks: 是索引记录上的记录锁和索引记录之前的间隙锁的组合.

InnoDB的行级锁, 按照功能来说, 分为两种:

共享锁(s): 允许一个事物去读一行, 阻止其他事物获得相同数据集的排他锁.排他锁(x): 允许获得排它锁的事物更新数据, 阻止其他事物取得相同数据集的共享读锁和排他写锁.

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁(X);

InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着:只有通过索引条件检索的数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁!

2. 查看行级锁争用状态

这5个状态变量, 最后3个比较重要, 尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

3. 手动操作行级锁

手动添加共享锁:

select * from table_name where ... lock in share mode

手动添加排它锁:

select * from table_name where ... for update

4.InnoDB行锁演示

mysql> create table test_innodb_lock (a int(11),b varchar(16)) engine=innodb; Query OK, 0 rows affected (0.02 sec) mysql> create index test_innodb_a_idx on test_innodb_lock(a); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index test_innodb_lock_b_idx on test_innodb_lock(b); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 Session aSession b1mysql> set autocommit=0; Query OK, 0rows affected (0.00 sec)mysql> set autocommit=0; Query OK, 0 rowsaffected (0.00 sec)2mysql> update test_innodb_lock set b ='b1' where a = 1; Query OK, 1 row affected

(0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 更新，但是不提交 | | | 3 | | mysql> update test_innodb_lock set b = 'b2'where a = 1; 被阻塞，等待 | | 4 | mysql> commit; Query OK, 0 rows affected(0.05 sec) 提交 | | | 5 | | mysql> update test_innodb_lock set b = 'b2'where a = 1; Query OK, 0 rows affected (36.14 sec) Rows matched: 1 Changed: 0 Warnings: 0 解除阻塞，更新正常进行 |

无索引升级为表锁演示

Session aSession b1mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)2mysql> update test_innodb_lock set b = '2' where b = 2000; Query OK, 1 row

affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 | | | 3 | | mysql> update test_innodb_lock set b = '3' where b = 3000; 被阻塞，等待 | | 4 | mysql> commit; Query OK, 0 rows affected (0.10 sec) | | | 5 | | mysql> update test_innodb_lock set b = '3' where b = 3000; Query OK, 1 row affected (1 min 3.41 sec) Rows matched: 1 Changed: 1 Warnings: 0 阻塞解除，完成更新 |

间隙锁带来的插入问题演示

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

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

SELECT * FROM emp WHERE empid > 100 FOR UPDATE

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另一方面，是为了满足其恢复和复制的需要。有关其恢复和复制对机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况。

很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

session aSession b1mysql> set autocommit=0; Query OK, 0 rows affected(0.00 sec)mysql> set autocommit=0; QueryOK, 0 rows affected (0.00 sec)2mysql> update test_innodb_lock set b = a * 100 where a < 4 and a > 1; Query OK, 1 row affected (0.02 sec) Rowsmatched: 1 Changed: 1 Warnings: 03mysql> insert into test_innodb_lock values(2,'200');被阻塞，等待4mysql> commit; Query OK, 0 rows affected (0.02 sec)5mysql> insert intotest_innodb_lock values(2,'200');

Query OK, 1 row affected (38.68 sec) 阻塞解除，完成插入 |

死锁

Session aSession b1mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)2mysql> update t1 set id = 110 where id = 11; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 03mysql> update t2 set id = 210 where id = 21; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 04mysql>update t2 set id=2100 where id=21; 等待sessionb释放资源，被阻塞5mysql>update t1 set id=1100 where id=11; Query OK,0 rows affected (0.39sec) Rows matched: 0 Changed: 0 Warnings:0 等待sessiona释放资源，被阻塞6两个 session 互相等等待对方的资源释放之后才能释放自己的资源,造成了死锁

4. java对mysql行锁操作

1. 悲观锁

用法： SELECT ... FOR UPDATE;

Spring 注解

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean>  <tx:annotation-driven transaction-manager="transactionManager" />

在代码中, 即service层增加逻辑代码

@Transactional @Override public boolean increaseBalanceByLock(Long userId, BigDecimal amount) throws ValidateException { long time = System.currentTimeMillis(); //获取对记录的锁定 UserBalance balance = userBalanceDao.getLock(userId); LOGGER.info("[lock] start. time: {}", time); if (null == balance) { throw new ValidateException( ValidateErrorCode.ERRORCODE_BALANCE_NOTEXIST, "user balance is not exist"); } boolean result = userBalanceDao.increaseBalanceByLock(balance, amount); long timeEnd = System.currentTimeMillis(); LOGGER.info("[lock] end. time: {}", timeEnd); return result; }

Mybatis的mapper文件

2. 乐观锁

用法：

SELECT ... LOCK IN SHARE MODE;

转载于:https://www.cnblogs.com/lingshang/p/10755750.html

最新回复(0)