MYSQL的锁机制_mysql 自己锁自己

mysql的锁概述

1.锁的概念

数据库锁定机制简单来说，就是数据库为了保证数据库的一致性，而使用各种共享资源而在被并发访问变得有序所设计的一种规则
假设当前商品只有一件, 两个用户同时购买,我们需要保证只有一个用户能下单成功.

因为购买行为是一组操作,这里需要使用事务控制,从获取商品数量,插入订单 到付款后插入付款信息,更新商品数量.在这个过程中,使用锁可以对有限的资源进行保护

2.锁的分类

Mysql数据库由于其自身架构的特点，存在多种数据存储引擎，Mysql中不同的存储引擎支持不同的锁机制

• MYISAM和MEMORY存储引擎采用的表级锁
• Innodb默认采用的行级锁，但是他既支持表级锁，又支持行级锁
  按照数据操作的类型分
• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
• 写锁（排他锁）：当前操作没有完成前，会阻断其他的读行为和写行为

按照数据操作的类型分：

• 表级锁：开销小，加锁快。不会出现思死锁，锁定粒度大，发生锁得概率最高，并发度最低
• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

按照操作性能可以分为乐观锁和悲观锁：

• 乐观锁：一般的实现方式是对记录数据的版本进行对比，在更新数据提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息
• 悲观锁：在对同一条数据进行修改的同时，为了避免其他人同时修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现方式，但是都属于悲观锁的实现方式。

3.表级锁

• 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。
• 表级锁定分为: 表共享读锁（共享锁）与表独占写锁（排他锁）。
• 特点: 开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。
  数据准备：

-- 创建数据库
CREATE DATABASE test_lock CHARACTER SET 'utf8';
-- 创建表,选择 MYISAM存储引擎
CREATE TABLE mylock01(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(20)
)ENGINE MYISAM;
-- 创建表
CREATE TABLE mylock02(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(20)
)ENGINE MYISAM;
-- mylock01表中向插入数据
INSERT INTO mylock01(title) VALUES('a1');
INSERT INTO mylock01(title) VALUES('b1');
INSERT INTO mylock01(title) VALUES('c1');
INSERT INTO mylock01(title) VALUES('d1');
INSERT INTO mylock01(title) VALUES('e1');
-- mylock02表中向插入数据
INSERT INTO mylock02(title) VALUES('a');
INSERT INTO mylock02(title) VALUES('b');
INSERT INTO mylock02(title) VALUES('c');
INSERT INTO mylock02(title) VALUES('d');
INSERT INTO mylock02(title) VALUES('e');
SELECT * FROM mylock01;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

加锁语法

查看表中加过的锁：

-- 0表示没有加锁,当前的所有数据库表都没有加锁
SHOW OPEN TABLES;
-- 查询加锁的表,条件In_use 大于0
SHOW OPEN TABLES WHERE In_use > 0;
1
2
3
4

手动增加表锁：

-- 语法格式: LOCK TABLE 表名 READ(WRITE), 表名2 READ(WRITE), 其他;
-- 为mylock01加读锁(共享锁) , 给mylock02加写锁(排他锁)
lock table mylock01 read,mylock02 write;
SHOW OPEN TABLES WHERE In_use > 0;

1
2
3
4
5

释放锁，解除锁定

-- 方式1
unlock tables;
-- 方式2 找到锁进程,得到id
SHOW PROCESSLIST;
kill id
1
2
3
4
5

• 对MyISAM表的读操作 (加读锁) ,不会阻塞其他进程对同一表的读请求,但是会阻塞对同一表的写请求.只有当读锁释放后,才会执行其他进程的写操作.
• 对MyISAM表加写锁, 会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其他进 程的操作

4.行级锁（偏写）

行锁的是mysql锁中粒度最小的一种锁，因为锁的粒度很小，所以发生资源争抢的概率也最小，并发性能最大，但是也会造成死锁，每次加锁和释放锁的开销也会变大。

使用MySQL行级锁的两个前提

• 使用 innoDB 引擎
• 开启事务 (隔离级别为 Repeatable Read )

InnoDB行锁的类型

• 共享锁（S）：当事务对数据加上共享锁后,
  其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
• 排他锁（X）：如果事务T对数据A加上排他锁后，则其他事务不能再对数据A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

加锁的方式

• InnoDB引擎默认更新语句，update,delete,insert 都会自动给涉及到的数据加上排他锁，
  select语句默认不会加任何锁类型，如果要加可以使用下面的方式:
• 加共享锁（S）：select * from table_name where … lock in share mode;
• 加排他锁（x）：select * from table_name where … for update;

锁兼容

共享锁只能兼容共享锁, 不兼容排它锁
排它锁互斥共享锁和其它排它锁

行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的，而会使用表级锁把整张表锁住

总结: 行级锁中的写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免”脏读”问题的产生。
总结：在有写锁情况下,其他事物不能再对当前数据添加写锁，从而保证数据的一致性，从而避免了不可从重复读的问题

select语句加排他锁方式 : select * from table_name where … for update;

for update 的作用

for update 是在数据库中上锁用的，可以为数据库中的行上一个排他锁。

for update 的应用场景

存在高并发并且对于数据的准确性很有要求的场景，是需要了解和使用for update的。

for update 的注意点

for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效。

-- 排他锁查询
select * from innodb_lock where id = 1 for update;
-- 共享锁查询
select * from innodb_lock where id = 1 lock in share mode;
1
2
3
4

执行下面的命令,可以获取行锁锁信息

mysql> SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 51265 |
| Innodb_row_lock_time_avg | 51265 |
| Innodb_row_lock_time_max | 51265 |
| Innodb_row_lock_waits | 1 |
+-------------------------------+-------+
1
2
3
4
5
6
7
8
9
10

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