MyISAM表锁

https://blog.csdn.net/qq_39736597/article/details/113247788

MyISAM 存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

显式加锁：
(针对 user 表）

lock table user read; 加读锁

lock table user write; 加写锁

unlock tables;

实验：使用navicat的多个查询页（相当于多个用户线程？）

关于读锁：（共享锁
“微观感受”：多个线程可以同时读，执行写操作的线程会被阻塞
“宏观感受”：查询很快

  1. 当某个客户端线程显式拿到读锁时，其他的客户端不能进行写操作（增删改），此时会发生阻塞（如果是持有读锁线程进行写，则会报 Table was locked with a READ lock and can’t be updated）
  2. 允许多个客户端线程同时读（即每个客户端在读之前都会给表加一把读锁，当他们都释放了读锁时，写操作才停止阻塞开始执行）
  3. 特殊情况：只有一个线程同时拥有读写锁（其他线程不拥有读锁或写锁），此时这个客户端可写不可读（死锁！）

关于写锁：（排它锁
“微观感受”：当某个线程进行读时，其他线程的读写被阻塞
“宏观感受”：当有大量更新操作时查询很难得到锁（MyISAM的读写锁调度是写优先的）⭐

  1. 当某个客户端线程显式拿到写锁时，别的客户端的读写操作会被阻塞
  2. 同一个时刻，只有一个客户端可以拿到写锁，别的线程请求读写锁时会被阻塞

查看锁使用情况

mysql>show open tables; 查看打开的表，In_use 表示其是否被使用（被锁）
image.png
mysql>show status like ‘Table_locks%’; 查看立即、等待获取表级锁的次数？？
image.png

InnoDB行锁

InnoDB 相较于 MyISAM，最大的不同是使用了 事务 和 行锁。

事务

ACID属性

原子性 Atomicity 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性 Consistent 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性 Isolation 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性 Durable 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务的隔离级别：

隔离级别 脏读（Dirty Read） 不可重复读（NonRepeatable Read） 幻读（Phantom Read）
读未提交（Read uncommitted） 可能 可能 可能
读已提交（Read committed） 不可能 可能 可能
可重复读（Repeatable read） 不可能 不可能 可能
可串行化（Serializable ） 不可能 不可能 不可能

查看默认隔离级别：
image.png

行锁

使用索引列检索数据，InnoDB将使用行锁

验证 InnoDB行锁（user表，username是唯一主键，没有其他索引）
image.png
图中是从上到下是1，2，3，4步，第4步标错

右侧窗口代表的会话先拿到行锁，左侧窗口再次获取时发生阻塞，直到等待超时

关于事务锁超时时间：

查询全局等待事务锁超时时间

SHOW GLOBAL VARIABLES LIKE ‘innodb_lock_wait_timeout’;

设置全局等待事务锁超时时间

SET GLOBAL innodb_lock_wait_timeout=100;

查询当前会话等待事务锁超时时间

SHOW VARIABLES LIKE ‘innodb_lock_wait_timeout’;

1205 - Lock wait timeout exceeded; try restarting transaction

此时右侧窗口提交事务，左侧窗口重新执行更新，此时可以更新成功，右侧窗口在提交事务时释放了行锁

注意：使用索引列依旧可能出现锁冲突的情况：
由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

行锁升级为表锁

如果不使用索引列检索数据，行锁将升级为表锁

image.png

使用非索引列进行写操作，行锁会升级为表锁，此时别的事务可以读不可以写

注意：行锁是针对索引加的锁，所以有时候即便是用来了索引列还是可能会升级为表锁（索引失效）

间隙锁

当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁； 对于键值在条件范围内但并不存在的记录，叫做 “间隙（GAP）” ， InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的 间隙锁（Next-Key锁）

危害

查看**行锁争用**情况

image.png