mysql锁

1. 什么是锁

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

2. mysql锁介绍

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

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

全局锁:锁的是整个database。由MySQL的SQL layer层实现的
全局锁:锁的是整个database。由MySQL的SQL layer层实现的
行级锁:锁的是某行数据，也可能锁定行之间的间隙。由某些存储引擎实现，比如InnoDB。

按照锁的功能来说分为:共享读锁和排他写锁。
按照锁的实现方式分为:悲观锁和乐观锁(使用某一版本列或者唯一列进行逻辑控制)

表级锁和行级锁的区别:

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

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

3. mysql锁

1. mysql表级锁

1. 查询表级锁争用情况

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 100   |
| Table_locks_waited         | 0     |
| Table_open_cache_hits      | 0     |
| Table_open_cache_misses    | 0     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
5 rows in set (0.01 sec)

Table_locks_immediate: 产生表级锁定的次数.
Table_locks_waited: 出现表级锁定挣用而发生的次数.

2. 表锁

1. 手动操作表锁

增加表锁

语法: lock table 表名称1 read, 表名称2 write;

mysql> lock table sms read;
Query OK, 0 rows affected (0.00 sec)

查看表锁

show open tables;
#加锁以后
mysql> show open tables like 'sms%';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | sms   |      1 |           0 |
+----------+-------+--------+-------------+
1 row in set (0.00 sec)
#
mysql> show open tables like 'sms%';
+----------+-------+--------+-------------+
| Database | Table | In_use | Name_locked |
+----------+-------+--------+-------------+
| test     | sms   |      0 |           0 |
+----------+-------+--------+-------------+
1 row in set (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. 查看行级锁争用状态

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

Innodb_row_lock_current_waits:当前正在等待锁定的数量;
Innodb_row_lock_time_max:从系统启动到现在等待最常的一次所花的时间;
Innodb_row_lock_time:从系统启动到现在锁定总时间长度;
Innodb_row_lock_time_avg:每次等待所花平均时间;
Innodb_row_lock_waits:系统启动后到现在总共等待的次数;

这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 a	Session b
1	mysql> set autocommit=0; Query OK, 0rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rowsaffected (0.00 sec)
2	mysql> 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 a	Session b
1	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
2	mysql> 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 a	Session b
1	mysql> set autocommit=0; Query OK, 0 rows affected(0.00 sec)	mysql> set autocommit=0; QueryOK, 0 rows affected (0.00 sec)
2	mysql> update test_innodb_lock set b = a * 100 where a < 4 and a > 1; Query OK, 1 row affected (0.02 sec) Rows
matched: 1 Changed: 1 Warnings: 0
3		mysql> insert into test_innodb_lock values(2,'200');
被阻塞，等待
4	mysql> commit; Query OK, 0 rows affected (0.02 sec)
5		mysql> insert into test_innodb_lock values(2,'200');
Query OK, 1 row affected (38.68
sec) 阻塞解除，完成插入

死锁

	Session a	Session b
1	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec)
2	mysql> update t1 set id = 110 where id = 11; Query OK, 0 rows affected (0.00 sec) Rows matched: 0 Changed: 0 Warnings: 0
3		mysql> update t2 set id = 210 where id = 21; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
4	mysql>update t2 set id=2100 where id=21; 等待sessionb释放资源，被阻塞
5		mysql>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 注解

<!-- (事务管理)transaction manager, use JtaTransactionManager for global tx -->  
    <bean id="transactionManager"  
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">  
        <property name="dataSource" ref="dataSource" />  
    </bean>
    <!-- 使用annotation定义事务 -->
     <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文件

  <select id="getLock" resultMap="BaseResultMap" parameterType="java.lang.Long">
        <![CDATA[
            select * from user_balance where id=#{id,jdbcType=BIGINT} for update;
        ]]>
    </select>

2. 乐观锁

用法：

SELECT ... LOCK IN SHARE MODE;

posted @ 2019-04-23 12:59 阿晶阅读(154) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

阿晶