mysql锁理论

一、简介

锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除传统的计算机资源（如CPU、RAM、I/O等）的争用之外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤为重要，也更加复杂。

二、表锁(偏读)

Mysql的MyISAM存储引擎，开销小，加锁快，无死锁，锁力度大，发生锁冲突的概率最高。

1.建表

为了方便展示表锁相关特性，建立一个MyISAM作为存储引擎的数据库表：

create table mylock(
id int not null primary key auto_increment,
name varchar(20)
)engine myisam;
insert into mylock(name) values(‘ssss’);

2.读锁（共享锁）

lock table mylock read;

当前会话对该锁住的表只能读，不能写。对其他表不能读：

新开一个会话，可以对该锁住的表和其他表进行查询，但执行update时被阻塞：

直到执行下面的命令释放锁，上面的update操作才能执行：

#释放该库中所有表的锁
unlock tables；
#查询当前库中所有表的锁情况
show open tables；

3.写锁（排他锁）

lock table mylock write;

当前会话可以对该锁住的表进行读和写，但无法对其他表操作！

新开的会话连接，无法对锁住的表进行读和写操作，直接阻塞住了！

直到执行下面的命令释放锁，上面的查询才会执行：

unlock tables;

4.总结

1.对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求，只有当读锁释放后。才会执行其他进程的写操作。
2.对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。
简而言之，就是读锁会阻塞写，但是不会阻塞读，而写锁则会把读和写都阻塞。

5.分析表锁定

可以通过检查table_locks_waited 和 table_locks_immediate状态变量来分析系统上的表锁定：

show status like ‘table%’;

table_locks_immediate:产生表级锁定的次数，表示可以立刻获取锁的查询次数，每立刻获取锁值+1
table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值+1），此值高则说明存在较严重的表级锁争用情况。
此外，MyISAM的读写锁调度锁写优先，这也是myisam不适合做写为主表的引擎，因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

三、行锁

InnoDB存储引擎，开销大，加锁慢，会出现死锁；锁粒度最小，发生锁冲突的概率低，并发度也最高；
InnoDB和MyISAM的最大不同有2点：一是支持事物，二是采用了行级锁；
复习：

1.事务上由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常称为事务的ACID属性：
原子性（atomicity）：事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全部不执行。
一致性（consistent）：在事务开始和完成时，数据都必须保证一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保证数据的完整性，事务结束时，所有的内部数据结构（如B数索引和双向链表）也都必须是正确的。
隔离性（isolation）： 数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行，这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

2.并发事务带来的问题
更新丢失：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了其他事务所做的更新。
脏读：事务A读取到事务B已修改但尚未提交到数据，还在这个数据基础上做了操作，此时，如果事务B会滚，A读取到数据无效，不符合一致性要求。
不可重复读：一个事务在读取某些数据后到某个时刻，再次读取以前读过的数据，却发现其读出的数据已经发生了改变，或某些记录已经被删除了！这种现象就叫做“不可重复读”；
幻读：一个事务按相同的查询条件重复读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为幻读。

查看事务隔离级别的语句是：

show variables like ‘tx_isolation’;

隔离级别	读数据一致性
未提交读	最低级别，只保证不读取物理上损坏的数据
已提交读	语句级
可重复读	事务级
可序列化	最高级别，事务锁

正文开始！！！！！

1.建立InnoDB表

create table test_inno(
a int,
b varchar(10)
)engine innoDB;
insert into test_inno values(1,’100’);
insert into test_inno values(2,’200’);
insert into test_inno values(3,’300’);
insert into test_inno values(4,’400’);

2.关闭mysql自动提交

set autocommit=0;

关闭自动提交后，有下面的情况需注意：
1.窗口A关闭了自动提交，但窗口B没关闭自动提交：
a对表的数据进行了修改（但还未commit），只有a能通过select语句查询出修改的内容，当a进行commit后，b瞬间就能通过select看到a的修改。
2.窗口A和窗口B都关闭了自动提交
a对表的数据进行了修改，并且完成了conmit。此时b是无法通过select语句查询到a到修改，只有当b也执行commit，才能看到修改后到内容。

3.行锁展示

需要打开两个命令行窗口进行演示，两个窗口都关闭了mysql的自动提交！
1⃣️操作的数据是同一行：
此时后执行update的会话要等待前面的会话进行commit才能执行。

2⃣️操作的数据是不同行
此时两个会话可以同时进行update操作，互不影响！！

4.锁升级

当索引失效时，行锁会自动升级成为表锁！
先给上面表的b字段加上索引！！！
例如下面的案例：左边的update的where条件中的b字段是varchar类型，4000不加双引号的话，就会发生类型自动转化，继而导致索引失效。所以右边的窗口操作其他行时，由于锁升级为表锁，所以一直阻塞等待！

5.间隙锁

当我们用范围条件，而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙”。
InnoDB也会给这个间隙加锁，这种锁机制就是所谓的间隙锁。
表中其实没有id=2的数据，这时候session2往表中添加id=2的数据，就会被阻塞，直到session1执行commit。

6.如何锁定一行？

利用select * from table where … for update来实现锁定，直到commit才会释放锁！！！

7.分析行锁定

通过检测InnoDB_row_lock状态变量来分析系统上的行锁争夺情况：

show status like ‘innodb_row_lock%’;