标签：事务行锁线程 MyISAM Mysql 机制 where id

一、锁的概念

锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除了传统的计算资源(CPU、RAM、I/O等)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是数据库必须解决的一个问题。锁冲突也是影响数据库并发访问性能的一个重要因素。

锁的分类

锁从对数据的操作类型可以分为读锁和写锁，从对数据操作粒度可以分为表锁和行锁。
读锁：针对同一份数据，多个读操作可以同时进行而不会互相影响；
写锁：当前写操作没有完成前，它会阻断其它写锁和读锁；

二、读锁案例(MyISAM引擎)

加锁： lock table tbl_name write/read:
解锁： unlock tables;
查看使用锁的情况：show open tables;

线程1给 dept表添加了读锁
线程1读取自己的表、读取其它表
线程1更新自己的表
线程2读取加锁的表、线程2更新加锁的表

总结：
添加读锁的线程只能对本表进行读取，不能够修改本表也不能读取或修改其他表；
其它线程能够访问到加锁的表，但是修改操作会被阻塞，只有加锁的线程取消锁后本线程才能够进行写操作。

三、写锁案例(MyISAM引擎)

添加写锁的线程能够对本表进行读操作和写操作，但是不能够对其它表进行读/写操作；
其它线程对加锁的表的读和写操作会被阻塞，需要释放锁后才能读取；

四、MyISAM引擎锁总结

MyISAM在执行查询语句(SELECT)前，会自动给所涉及到的所有表添加表锁，在执行增删改操作前，会自动给涉及到的表加写锁。
MyISAM的表级锁有两种模式：
对MyISAM表的读操作(加读锁)，不会阻塞其他进程对同一表的读请求。只有当读锁释放后才会执行其它进程的写操作；
对MyISAM表的写操作(加写锁)，会阻塞其它进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。
MyISAM适合于读操作多的，它的写锁会阻塞其他线程的所有操作。

五、如何分析表锁定

SQL： show status like 'table%';
在这里插入图片描述
table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
table_locks_waited：出现表级锁定争用而发生等待的次数。(不能立即获取锁的次数，每等待一次锁值加1)，此值较高说明存在严重的表级锁争用情况。

六、行锁理论

先回顾以下数据库事务的特性ACID：

原子性(A)：事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全都不执行。
一致性(C)：在事务开始和结束的时候，数据都必须保证一致性状态。一致性强调事务的中间状态数据对外部不可见；比如A向B转账，不可能A扣了钱，B却没收到。
隔离性(I)：多个事务的时候，相互之间不能够干扰；比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
持久性(D)：事务完成后对于数据的修改是永久的，即使出现故障也能够保持。
！！！ 原子性和一致性的的侧重点不同：原子性关注状态，要么全部成功，要么全部失败，不存在部分成功的状态。而一致性关注数据的可见性，中间状态的数据对外部不可见，只有最初状态和最终状态的数据对外可见

再回顾下并发问题：

脏读：事务A读取了事务B更新的数据，然后事务B回滚，那么A读到的就是脏数据；
不可重复度：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
幻读：事务A在读取数据时，事务B在该范围内插入了新的行，当A再去读取数据时会发现新增的行。

四种隔离级别：

读未提交(read uncommitted)：事务B可以读取事务A未提交的内容，会产生脏读；
读已提交(read committed)：一个事务只能够读取到已经提交的内容。这种隔离级别也支持不可重复读。
可重复读(repeatable read)：确保一个事务的多个实例在并发读取数据时，会看到相同的数据行。
串行化(Serializable)：serializable是在每个读的数据行上加锁，在这个级别，可能会导致大量的超时和锁竞争。

在这里插入图片描述
查看数据库的隔离级别： show variables like 'tx_isolation';

1. 行锁演示

在这里插入图片描述
事务1 进行了某一行的更新，但是没有提交；
事务2 此时相对同一行进行更新，发现被阻塞；
事务1提交后，事务2解除阻塞正常更新；

2. 行锁失效变为表锁

当使用索引时不小心造成索引失效，此时会将数据库的行锁变为表锁。

3. 间隙锁的危害

在这里插入图片描述

间隙锁的概念：
当我们使用范围条件而不是相等条件检索数据时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙(GAP)；
危害：
因为Query执行过程中通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜锁定，而造成阻塞无法插入数据。

4. 如何上锁

锁定一行有两种方式，分别是添加共享锁(lock in share mode) 和排他锁(for update)；

1.添加共享锁

命令：select * from dept where id=1 lock in share mode;
案例：

# session1
start transaction;
select * from test where id = 1 lock in share mode;

# session2
start transaction;
select * from test where id = 1 lock in share mode;

此时 session1 和 session2都可以正常获取结果；

(1) 加入排他锁获取数据

# session3
start transaction;
select * from test where id = 1 for update;

Lock wait timeout exceeded; try restarting transaction

结果 session3 无法获取数据，直到超时或其它事务commit 后才能有结果；

(2) 共享锁之间更新

# session1
update test set name = 'kkkk' where id = 1;

session1 能够获取结果；

# session2
update test set name = 'zzz' where id = 1;

session1 未 commit前提下 session2也执行对同一行的数据的修改操作。

Deadlock found when trying to get lock; try restarting transaction
或者
Lock wait timeout exceeded; try restarting transaction

此时会出现死锁或者锁超时现象。必须等到 session1完成事务之后 session2 才能够执行更新操作。

共享锁总结：
- 允许其它事务添加共享锁；
- 不允许其它事务添加排他锁；
- 当事务同时增加共享锁时，事务的更新必须等待先执行update操作的事务commit之后才能执行，否则很容易造成死锁。

2. 添加排他锁

命令：select * from dept where id=2 for update;
案例：

(1) 排他锁之间读取数据

# session1 
start transaction;
select * from test where id = 1 for update;

# session2
start transaction;
select * from test where id = 1 for update;

当session1 执行查询后(未提交)，session2的查询会被阻塞；直到session1 提交后session2才能获取数据；
查询都不被允许，更不用说更新操作了。

(2) 加入共享锁后尝试读取数据

# session3
select * from test where id = 1 lock in share mode;

结果一样也是阻塞。

排他锁总结：
- 事务之间不允许其它排他锁或共享锁读取数据，更新数据更不可能；
- 一次只能一个排他锁执行 commit之后，其他事务才能够执行；

5. 行锁总结