锁

表级锁定（table-level）: 共享锁 , 排他锁
行级锁定（row-level）
页级锁定（page-level）

冲突 = 阻塞

image.png

锁模式:

S锁 (共享锁) : 只能读不能修改

X锁 : 其他事务不能对目标加任何锁; 其他事务不能修改数据; 也不能显式加锁

意向共享锁

意向排他锁

innodb 中在update, insert, delete 操作前 加排他锁; select 不涉及锁机制

手动杀死进程

show processlist;
kill $pid;

乐观锁 / 悲观锁

悲观锁 : 每次线程要修改数据时都先获得锁，保证同一时刻只有一个线程能操作数据，其他线程则会被 block

乐观锁 : 拿数据的时候不上锁, 在提交更新的时候会判断一下在此期间别人有没有去更新这个数据, 适用于读多写少;

乐观锁 -- 提交前判定版本冲突

1. 使用版本号
2. 使用时间戳

两阶段锁定协议(S2PL)

定义 : 先加锁, 再解锁

只有comit / rollback 才解锁

提升性能: 越热点记录(update/delete/insert)离事务的终点(commit/rollback)越近

InnoDB会根据事务隔离级别在需要的时候自动加锁 : update/delete/insert 等操作

显示锁定

# innodb 显示锁定
select .. ... for update（排它锁）; 
select ..... lock in share mode(共享锁) # 在当前事务未结束前其他事务对表数据修改阻塞

# myisam 显示锁定
lock table g read local;
Unlock tables;

什么是死锁 & 如何造成死锁 & 死锁如何被检测到 & 死锁如何处理?

死锁.png

创造[图片上传中...(死锁测试1.png-854b67-1588238630149-0)]

死锁

事务A先对第一条数据进行锁定; 同时事务B对第二天数据锁定;事务A修改第二条数据, 阻塞等待; 事务B修改第一条数据, 被mysql判定为死锁, 事务B被回滚, 事务A成功提交

死锁测试2.png

# 事务A
begin;
update g set cost =1,name=1 where id = 5;

# 事务B
begin;
update g set cost =2,name=2 where id = 18; 

# 事务A
update g set cost =1,name=1 where id = 18; 

# 事务B
update g set cost =2,name=2 where id = 5; 
# Deadlock found when trying to get lock; try restarting transaction, 事务B自动回滚

# 事务A
commit;

死锁测试1.png

死锁如何检测

mysql 有死锁检测机制, 检测到事务冲突会使持有最少行级排它锁的事务进行回滚事务回滚来解锁

查看死锁状态

show status like 'table_lock_%';
#如果Table_locks_immediate / Table_locks_waited > 5000，最好采用InnoDB引擎

ACID

a : 原子性

c: 一致性

i : 隔离性

d : 持久性

事务隔离级别

SELECT @@transaction_isolation; # REPEATABLE-READ

	描述	脏读	不可重复读	幻读
未提交读	最低	1	1	1
已提交读	语句级(默认)	0	1	1
可重复读	事务级	0	0	1
可序列化	最高	0	0	0

事务隔离级别

并发事务处理带来的问题

问题	描述
更新丢失	update冲突覆盖
脏读	select跨事务
不可重复读	update跨事务
幻读	insert跨事务

测试和结论:

更新丢失测试(update冲突覆盖)

# 事务A
set @@autocommit =0 ;
begin ; 
SELECT cost from g where id =5 ;    # 999
UPDATE g set cost = 1 where id = 5 ;
commit;

# 事务B
set @@autocommit =0 ;
begin ; 
SELECT cost from g where id =5 ;    # 999
UPDATE g set cost = 2 where id = 5 ;
commit;

# 事务A
begin ; 
SELECT cost from g where id =5 ;    #2 
commit;

# 事务B
begin ; 
SELECT cost from g where id =5 ;    #2
commit;

脏读 / 不可重复读测试(update跨事务)

在事务A中读数据为1; 在事务B中将数据改为999并提交; 在事务A中再次读取数据, 读到的数据还是1; 提交事务A, 再读取数据, 读到数据为999

幻读(insert跨事务)

# 事务A
set @@autocommit =0 ;
begin ; 
SELECT count(id) from g ;   #5

# 事务B
set @@autocommit =0 ;
begin ;         
SELECT count(id) from g ;       #6
INSERT INTO g (cost,name) values(8,9);
commit;

# 事务A
SELECT count(id) from g ;   #5
commit;

begin;
SELECT count(id) from g ;   #6
commit;