MySQL 事务和锁

1.什么是事务?

主要针对的是DML(数据操作语句)(update,delete,insert)

一组数据操作执行步骤,这个步骤被视为一个工作单元

用于对多个语句分组

可以在多个客户端并发访问同一个表中的数据时使用

所有步骤都成功或者都失败

如果所有步骤都正常,则执行

如果步骤出现错误或者不完整,则取消

2.事务的通俗理解(交易)

物与物交换

现金与实物交易

虚拟货币与实物交易

等等

3.事务ACID特性

Atomic (原子性)

所有语句作为一个单元全部成功执行或全部取消

Consistent(一致性)

如果数据库在事务开始处于一致性,则在执行该事务期间保留一致性

比如:a有两个苹果,b有两个苹果,一共四个.a给b一个还剩下一个,b则有三个,结果还是四个

Isolated(隔离性)

事务之间不相互影响,一个事务所做出的操作在提交前是不能为其他事物所见,隔离有多种级别

Durable(持久性)

事务成功完成后,所做的所有更改都会准确的记录在数据库中,所做更改不会改变

事务举例

事务控制语句

事务控制语句

start transaction (begin): 显式开启一个事务

savepoint: 分配事务过程中的一个位置,供将来引用 (类似于单机游戏存档) 例如:savepoint a1 savepoint就是为回退做的,savepoint的个数没有限制,savepoint和虚拟机中快照类似. savepoint是事务中的一点。用于取消部分事务，当结束事务时，会自动的删除该事务中所定义的所有保存点。

commit: 永久记录当前事务所做的更改

rollback: 取消当前事务所做的更改

rollback to savepoint: 取消在savepoint之后执行的更改例如;rollback to savepoint a1 但是如果从第3个保存点回退到第一个保存点，直接跳过了第2个保存点。第二个保存点无效。

release savepoint: 删除savepoint标识符(类似于删除存档)

set autocommit: 为当前连接禁用或者启用默认autocommit模式例如:set autocommit=0 临时关闭自动提交 set autocommit=1 临时开启自动事务提交

一个失败的事务生命周期

begin; 开启事务

sql_1

sql_2

sql_3

rollback 回滚事务(或者直接退出mysql)

一个成功的事务生命周期

begin; 开启事务 (在事务的周期中修改的数据是看不到的,只有在commit后才可以看到)

sql_1

sql_2

sql_3

commit 完成事务

事务隐式提交情况

事务隐式提交情况

1.开启了自动提交功能(默认)

2.事务运行期间,当事务没有结束,begin开启了下一个事务

3.事务运行期间,加入DDL(数据定义语句create,drop,alter),DCL(数据控制语句 grant,revoke)操作

4.事务运行期间,执行锁定语句(lock tables,unlock tables)

5.load data infile 导入数据

6.select for update

举例说明

例如:模拟转账
mysql> select * from py;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | zhang3 |  1000 |
|  2 | li4    |  3000 |
+----+--------+-------+

事件1 事务回滚

begin; #开启事务

mysql> update py set money=-1000 where name='zhang3';  #修改数据(转账2000给li4)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0  

mysql> select * from py;   #查看数据 此时zhang3为负数 事务执行失败
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | zhang3 |  -1000 |
|  2 | li4    |  3000  |
+----+--------+-------+
2 rows in set (0.00 sec)

mysql> rollback;  #回滚  此时事务已经结束
Query OK, 0 rows affected (0.00 sec)

mysql> select * from py; #再次查看数据已经回滚
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | zhang3 |  1000 |
|  2 | li4    |  3000 |
+----+--------+-------+
2 rows in set (0.00 sec)

事件二:转账成功

mysql> begin; 开始事务
Query OK, 0 rows affected (0.00 sec)

mysql> update py set money=500 where name='zhang3'; #转账500给li4
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update py set money=3500 where name='li4';  #收到转账
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from py;
+----+--------+-------+
| id | name   | money |
+----+--------+-------+
|  1 | zhang3 |   500 |
|  2 | li4    |  3500 |
+----+--------+-------+
2 rows in set (0.00 sec)

mysql> commit;  #此时事务完成 如果不执行commit退出mysql 则事务未完成,所有修改回滚
Query OK, 0 rows affected (0.00 sec)

ps:在执行DML语句时系统会自动开启一个事务,但是由于MySQL内部有自动提交机制,所以在执行完DML语句后,系统会自动commit 如果不想自动提交事务需要在配置文件中关闭autocommit
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

事务锁

“锁”的作用是什么？

事务ACID特性过程，“排他锁和共享锁”和“隔离级别”一起来实现I(隔离性)的作用。

image-20210628162646081-1652582308008

上图中事务1会执行成功,事务二执行失败

原因:(当事务一开启一个事务后(begin)会开启行级锁必须要等当前事务完成后,其他事务才可对此数据进行修改,如果其他事务也在修改当前数据时 ,因为事务1没有结束,其他事务会被夯住,当执行update stu set id=2 where id=1后 commit, 然后事务2才开始运行,但是由于事务1已经将id改成了2 所以事务2中的where条件找不到id=1,所以不作修改 )

加锁方式:

隐式加写锁: insert, update,delete
读锁: select...lock in share mode
写锁: select...for update
手动读锁: lock tables 表名1 [表名2] read
手动写锁: lock tables 表名1 [表名2] write
全局读锁: flush tables with read lock
全局写锁: flush tables with write lock

例如:
mysql> select * from stu;
+----+--------+
| id | name   |
+----+--------+
|  1 | zhang3 |
+----+--------+
1 row in set (0.00 sec)
事务1:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update stu set id=2 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
此时事务1还没有commit

事务2:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update stu set id=3 where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 锁定等待超时,请尝试重新启动事务

事务已执行,但是因为事务1的行级锁,事务2已被夯住

此时事务1 执行commit
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

事务2:
再次执行时
mysql> update stu set id=3 where id=1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

命令成功但是没有修改数据,因为id已被修改为2,但是条件是id=1

mysql锁类型

排他锁（写锁）保证在多事务操作时,数据的一致性又称为写锁，简称X锁，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。简单的说就是 x锁会阻塞其它事务的读和写但不包括当前获取x锁的事务(当前加锁客户端可读可写,其余客户端不可读不可写)
共享锁（读锁）保证在多事务工作期间,数据查询不会阻塞又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改(所有客户端可读但是不可写)
乐观锁多个事务同时运行,谁先提交谁为准
悲观锁 (行级锁 for update)保护数据完整性。当多个事务并发执行时，一个事务对数据上了锁，则其他事务只能等该事务执行完了，才能进行对该数据进行修改

意向锁

产生原因
解决表锁与之前可能存在的行锁冲突，避免为了判断表是否存在行锁而去扫描全表的系统消耗。

意向锁的加锁规则
事务在获取行级 S 锁之前，必须获取其对应表的 IS 或 IX 锁
事务在获取行级 X 锁之前，必须获取其对应表的 IX 锁

作用: 一种快速判断手动加的表锁与之前可能存在的行锁冲突的机制。（数据库在执行事务过程中，更新数据时会帮我们自动加行锁)

 #显示使用锁（手动加表级锁）

mysql> lock tables 表名 write|read；

 #解锁
mysql> unlock tables；

 #全局读锁
mysql> flush tables 表名 with read lock;

#全局写锁
mysql flush tables 表名 with write lock;

 #查询时加写锁
 mysql> select * from tb_name for update;
 
 #"For Update"是一种SQL语句，它的作用是在查询过程中锁定数据行，防止其他事务对这些数据进行修改操作。使用"For Update"可以保证当前事务对被锁定的数据具有排他性，并且能够避免产生并发问题。"For Update"适用于需要修改或者删除数据的场景，例如在一个订单系统中，如果多个用户同时尝试对同一个订单进行修改操作，就可以使用"For Update"方式来避免冲突。InnoDB只有在通过索引条件检索数据时使用行级锁，否则使用表锁
 
例如:

悲观锁:   select * from stu for update;  就是表锁，
  	   select * from stu where id= 1 for update; 就是行锁。


mysql> select * from stu;
+----+--------+
| id | name   |
+----+--------+
|  1 | li4    |
|  2 | zhang3 |
|  3 | wang5  |
+----+--------+
3 rows in set (0.00 sec)

A主机
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu for update; 加表锁
+----+--------+
| id | name   |
+----+--------+
|  1 | li4    |
|  2 | zhang3 |
|  3 | wang5  |
+----+--------+
3 rows in set (0.00 sec)

B主机 此时无法更新此表数据 必须等到 A主机执行commit

多版本并发控制（MVCC）

只阻塞修改类操作,不阻塞查询类操作
乐观锁机制谁先提交谁为准

锁的粒度

MyISAM 低并发锁(表级锁)
innodb 高并发锁(行级锁)

事务隔离级别

概念说明

以下几个概念是事务隔离级别要实际解决的问题，所以需要搞清楚都是什么意思。

脏读

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。

可重复读

可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据**更新（UPDATE）**操作。

不可重复读

对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据**更新（UPDATE）**操作。

幻读

幻读是针对数据**插入（INSERT）**操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

事务隔离级别

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

读未提交（READ-UNCOMMITTED）
读已提交（READ-COMMITTED）不用退出就可查看 (根据生产环境,推荐使用)
可重复读（REPEATABLE-READ）默认级别
串行化（SERIALIZABLE）

从上往下，隔离强度逐渐增强，性能逐渐变差。其中，可重复读是 MySQL 的默认级别。

事务隔离其实就是为了解决脏读、不可重复读、幻读这几个问题，下面展示了 4 种隔离级别对这三个问题的解决程度。

隔离级别	脏读	不可重复读	幻读
读未提交（READ UNCOMMITTED）	可以脏读	可以不重复读	可以幻读
读已提交（READ COMMITTED）	不可脏读	可以不重复读	可以幻读
可重复读（REPEATABLE READ）默认级别	不可脏读	可以重复读	可以幻读
串行化（SERIALIZABLE）	不可脏读	可以重复读	不可以幻读

#查看事务隔离级别
MySQL> show variables like 'transaction_isolation';

MySQL> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+
1 row in set (0.004 sec)