* Mysql - 锁 与 事务

* Mysql - 锁 与 事务

1、MySQL 中的锁

MySQL 的锁机制比较简单
其最显著的特点是不同的存储引擎支持不同的锁机制
比如：
MyISAM 和 MEMORY 存储引擎采用的是表级锁（table-level locking）
InnoDB 存储引擎既支持行级锁（row-level locking）也支持表级锁，但默认采用行级锁。

MyISAM 的表锁

Mysql 的表级锁有两种模式：
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）

共享读锁语法

给表加锁
加共享读锁
lock table 表名 read

表独占写锁语法

给表加锁
加独占写锁
lock table 表名 write

InnoDB 行锁

示例表：

CREATE TABLE `testdemo` (
  `id` int(255) NOT NULL,
  `c1` varchar(100) DEFAULT NULL,
  `c2` int(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_c2` (`c2`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

注意三，示例：
会话1

begin 
update testdemo set c1 = '1' where c1 = '1';

会话2，会锁住，处于等待状态

update testdemo set c1 = '1' where c1 = '2';

InnoDB 表锁：

死锁了怎么办

5.7 版本：
select * from information_schema.INNODB_LOCKS;

select * from sys.innodb_lock_waits;

5.6版本：

select
r.trx_id waiting_trx_id,
r.trx_mysql_thread_id waiting_thread,
r.trx_query waiting_query,
b.trx_id blocking_trx_trx_id,
b.trx_mysql_thread_id blocking_thread
from information_schema.innodb_lock_waits w
inner join
information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id
inner join
information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id;

kill 27;

查看一张表使用的是什么存储引擎：
show create table testdemo;

事务

事务的特性

事务的隔离级别

隔离性：

• 要求一个事务对数据库中数据的修改，在未提交完成前对于其他事务是不可见的。

分类：

• 未提交读（read uncommitted）脏读
• 已提交读（read committed）不可重复读
• 可重复读（repeatable read）：默认
• 可串行化（serializable）

查询当前数据库使用的事务隔离级别：

select @@tx_isolation;

脏读（一个事务读取到另一个事务未提交的数据）演示：
1，修改两个session的书屋隔离级别

set session transaction isolation level read uncommitted;

2，在session1中开启一个事务，更新数据

begin 
update testdemo set c2 = '4' where id = 1;

3，在session2中开启一个新事物，查到的数据是session1事务未提交的数据。

begin
select * from testdemo;

不可重复读，演示
在两个session中设置隔离级别为：已提交读
1，解决脏读问题
2，一个事务两次读取数据库获取的结果不一致。

set session transaction isolation level read committed;

初始数据：

• 1，在session1中开启一个事务，修改数据

begin
update testdemo set c2 = '4' where id = 1;

• 2，在session2中查看数据，结果为 5

begin
select * from testdemo;

• 3，提交session1的事务，在session2中查看数据，结果为 4

select * from testdemo;

可重复读
重复不可重复读的操作，一个事务中每次查询出来的结果都是一样的。解决了不可重复读问题。也解决了幻读问题

串行化，通过锁表来解决幻读问题。
幻读，数据量（条数）不同，新增或删除

MySQL 解决了可重复读问题，幻读问题。

事务的语法

开启事务

• begin
• start transaction（推荐）
• begin work

begin 多次，前面的事务会先 commit ，再开启一个新的事务。

事务的回滚

rollback

提交事务

commit

还原点

savepoint

演示：

-- 数据准备
create table t_lock_2 (a int primary key, b int, key(b));
insert into t_lock_2 values (1,1),(3,1),(5,3),(8,6),(10,8);

-- 保存点
set autocommit = 0;
insert into t_lock_2 values (11, 11);
savepoint s1;

insert into t_lock_2 values (12, 12);
savepoint s2;

insert into t_lock_2 values (13, 13);
savepoint s3;

rollback to savepoint s1;

回滚后的数据

间隙锁

主键列，范围查询

准备工作：

create table t_lock_1 (a int primary key);

insert into t_lock_1 values (10),(11),(12),(13),(20),(40);

session 1 中：开启事务，并锁住一定范围的数据（主键）

begin 
select * from t_lock_1 where a <= 13 for update;

session 2 中：

begin
insert into t_lock_1 values (21);  -- 插入成功

insert into t_lock_1 values (19);  -- 执行后，进入等待状态

解释：
间隙锁在锁数据时，是先将数据加锁，然后判断该行数据是否满足条件，不满足条件则停止，满足条件则继续向后锁数据。（即发现10、11、12、13都可以锁住，然后继续向后多锁一行数据，发现20不满足条件则停止）

非主键列索引（加了索引的数据都是有序的）

数据准备：

-- b 增加了普通索引
create table t_lock_2 (a int primary key, b int, key(b));

insert into t_lock_2 values (1,1),(3,1),(5,3),(8,6),(10,8);

a  1 3 5 8 10   pk
b  1 1 3 6  8   key

session 1：锁住 b = 3 的数据

begin 
select * from t_lock_2 where b = 3 for update;

session 2：尝试给 a = 5 的记录添加锁

begin
select * from t_lock_2 where a = 5 lock in share mode; -- 进入等待状态
insert into t_lock_2 values (4, 2); -- 进入等待状态
insert into t_lock_2 values (6, 5); -- 进入等待状态

insert into t_lock_2 values (4, 7); -- 可以插入成功

结论：

• 与 b = 3 同一列的数据 a = 5 也被锁住
• b 列 数据在 1-3 和 3-6 之间的数据被锁住