mysql 事务类型 mysql事务性

转载

墨香四溢 2023-11-08 23:33:42

文章标签 mysql 事务类型 sql MySQL 数据库 文章分类 MySQL 数据库

mysql的事务性，锁和隔离性

事务性：

所谓事务性，一句话概括：一个组操作的各个单元，执行情况要么都成功，要么都执行失败。

开门见山：

事务的四大特性（ACID）：

1.原子性（automicity）：一个事物必须看做一个不可分割的最小工作单元，整个事务中的所有操作要么都提交成功，要么全部失败回滚。对于事务而言，不可能只执行其中一部分，这就是事务的原子性。

2.一致性（consistency）：数据库总是从一个一致性的状态转换到另一种一致性的状态。

3.隔离性（isolation）：一个事务所做的修改在最终提交之前，对其他的事务是不可见的。

4.持久性（durability）：一旦事务提交，其所做的一切修改必须永久保存在数据库中，此时即使系统崩溃，修改的数据也不会丢失。

MySQL的是先将所有操作到日志中记录下来，再去真正操作。而这种机制就是靠事务日志来实现的

锁：

而MySQL是有锁粒度的。

1.锁的类型被分为了读锁和写锁。

可以这么想：增删改就是写锁。

查询才是读锁。

2.锁的粒度也就是锁的范围，分为表锁，页锁，行锁。MySQL支持表级锁，行锁则需要存储引擎的支持

MyISAM支持的是表锁。

DBD支持的页面锁，

InnoDB支持的行锁。

表锁：开销小，加锁快，不会出现死锁；锁粒度大，发生锁冲突的概率最高，并发度最低。

页面锁：开销和加锁的时间界于表锁和行锁之间，会出现死锁，并发度一般。

行锁：开销大，加锁慢，会出现死锁；锁粒度小，发生锁冲突的概率最低，并发度最高。

锁的基本概念：

从数据库角度来看锁有3类：

1.独占锁：

独占锁的锁粒度是行或者是多行。只允许锁定操作的程序使用，其他任何对它的操作均不被接收。当对象有其他锁的时候，无法对其加独占锁。

2.共享锁：

共享锁的锁粒度是行或者是多行。当被锁定后其他程序可以读，但不能修改它。

3.更新锁：更新锁是为了防止死锁的。当数据库要更新数据时，它对数据对象做更新锁锁定，这样数据不能被修改，但可以读取。等到确定是要更新数据时才会自动将更新锁换成独占锁。

从程序员的角度来看锁有2类：

1.乐观锁：程序员很高兴。程序代码不需要做任何事，数据库自己加锁

2.悲观锁：

需要程序员自己手动加锁处理。

隔离性：

数据库的隔离级别由低到高分别为Read uncommitted，Read committed，Repeatable read，Serializable。

Read Uncommitted：

读未提交

例子：老板要发工资了。程序员一个月的工资是2.6万/月。但发工资时老板错按成了2.9万。该钱已经打到了程序员的账户上，但是事务还没有提交。这个时候程序员查自己的账户发现多了3000元。程序员很高兴，以为涨工资了。但这时老板发现了这个操作失误。马上就回滚了差点提交了的事务。将数据改成了2.6万后提交。

分析：

实际程序员的工资还是2.6万。程序员看到了老板还没提交的事务。这就是脏读。

Read Commintted：

读提交

例子：程序员拿着工资卡去消费（卡里只有2.6万元），当他要埋单的时候（事务开启），收费系统检测卡里有2.6万元。就在此时。程序员的妻子登录了程序员的支付宝将所有的钱都转了出去充当家用，并提交（期间有耗时）。当收费系统准备扣款时，发现卡里没钱了。程序员就很不理解，卡里明明有钱的啊。

分析：若有事务对数据进行了更新操作时，读操作事务必须要等待这个更新操作事务提交了才能读。这个方法虽然解决了脏读，但是出现了一个事务中查询出了两个不同的数据。这就是不可重复读。

Repeated read：重复读

例子：还是上面的例子。程序员要消费了，当他埋单时。（事务开启，并不允许其他事务做更新（UPDATE）操作），收费系统检测到他的卡里有2.6万元。这个时候程序员妻子想转出钱是失败的。接下来收费系统正常的扣钱。完成交易。

分析：

重复读可以解决不可重复读，但是这只解决了update操作。还是有可能出现幻读的（即插入的Insert操作）。

例子：程序员出去消费，花了2000元。程序员妻子查账。（全表扫描FTS，妻子事务开启）看到确实是花了2千元。就在这个时候。程序员花了2万元买了一台电脑，瞬间多了一条操作记录，并立马提交。当程序员妻子打印程序员账单时，发现消费成了2.2万元，感觉好像出了幻觉。这就是幻读。

Serializable

串行化

这就是最高的隔离性。在该级别下，任何操作必须一个完

[sql] view plain copy

MariaDB [CargoWarehouse]> select * from employee_list;+-----------+-----+-------+
| name | age | wages |
+-----------+-----+-------+
| Zhang san | 20 | 180 |
| Zhang | 20 | 2000 |
+-----------+-----+-------+
2 rows in set (0.00 sec)
MariaDB [CargoWarehouse]> commit;
Query OK, 0 rows affected (0.00 sec)

成了才能接着做下一个，轻轻松松解决脏读，不可重复读，和幻读。但是你知道的。。。效率奇低！！

参考博客：Mysql的事务四个特性以及四个隔离级别

MySQL，Mariadb，Sql Server 一般都是默认Repeatable read。

[sql] 
1. MariaDB [(none)]> select @@session.tx_isolation;  
2. +------------------------+  
3. | @@session.tx_isolation |  
4. +------------------------+  
5. | REPEATABLE-READ        |  
6. +------------------------+  
7. 1 row in set (0.00 sec)

接下来实际操作一下。

首先开启两个MySQL，看看第一个事务的操作会不会影响第二个事务的操作。

测试read uncommitted

先修改MySQL的隔离级别：

[sql]  
1. MariaDB [CargoWarehouse]> set tx_isolation='READ-UNCOMMITTED';  
2. Query OK, 0 rows affected (0.00 sec)  
3.   
4. MariaDB [CargoWarehouse]> select @@tx_isolation;  
5. +------------------+  
6. | @@tx_isolation   |  
7. +------------------+  
8. | READ-UNCOMMITTED |  
9. +------------------+  
10. 1 row in set (0.00 sec)

两边都开启事务：

[sql]  
1. MariaDB [CargoWarehouse]> start transaction;  
2. Query OK, 0 rows affected (0.00 sec)

老板

的在事务中的操作：

[sql]  
1. MariaDB [CargoWarehouse]> insert into employee_list values  
2.     -> (  
3. 'Zhang san',20,29000  
4.     -> );  
5. Query OK, 1 row affected (0.01 sec)

程序员

在事务中查看账户时发现账户上成了29000元：

[sql]   
1. MariaDB [CargoWarehouse]> select * from employee_list;  
2. +-----------+-----+-------+  
3. | name      | age | wages |  
4. +-----------+-----+-------+  
5. | Zhang san |  20 | 29000 |  
6. +-----------+-----+-------+  
7. 1 row in set (0.00 sec)

老板

发现错了，修改列表：

[sql]  
1. MariaDB [CargoWarehouse]> update employee_list set wages=26000   
2. where name='Zhang san';  
3. Query OK, 1 row affected (0.11 sec)  
4. Rows matched: 1  Changed: 1  Warnings: 0

老板

改完立马提交：

[sql]  
1. MariaDB [CargoWarehouse]> commit;  
2. Query OK, 0 rows affected (0.00 sec)

程序员

懵逼了，怎么同一个事务里查看出了两个不同的结果：（所谓的脏读）

[sql] 
1. MariaDB [CargoWarehouse]> select * from employee_list;  
2. +-----------+-----+-------+  
3. | name      | age | wages |  
4. +-----------+-----+-------+  
5. | Zhang san |  20 | 29000 |  
6. +-----------+-----+-------+  
7. 1 row in set (0.00 sec)  
8.   
9. MariaDB [CargoWarehouse]> select * from employee_list;  
10. +-----------+-----+-------+  
11. | name      | age | wages |  
12. +-----------+-----+-------+  
13. | Zhang san |  20 | 26000 |  
14. +-----------+-----+-------+  
15. 1 row in set (0.00 sec)

测试read committed

修改隔离级别

（两边都需要改）

[sql]  
1. MariaDB [(none)]> select @@tx_isolation;  
2. +----------------+  
3. | @@tx_isolation |  
4. +----------------+  
5. | READ-COMMITTED |  
6. +----------------+  
7. 1 row in set (0.00 sec)  
8.   
9. MariaDB [(none)]> start transaction;  
10. Query OK, 0 rows affected (0.00 sec)

程序员要消费，系统检测卡里的剩余钱数。

[sql] 
1. MariaDB [(none)]> select * from CargoWarehouse.employee_list;  
2. +-----------+-----+-------+  
3. | name      | age | wages |  
4. +-----------+-----+-------+  
5. | Zhang san |  20 | 26000 |  
6. +-----------+-----+-------+  
7. 1 row in set (0.00 sec)

程序员妻子去转账。

[sql]  
1. MariaDB [CargoWarehouse]> update employee_list set wages=200 where name='Zhang san';  
2. Query OK, 1 row affected (0.00 sec)  
3. Rows matched: 1  Changed: 1  Warnings: 0

程序员这里再次检测。（还是26000，没毛病）

[sql] 
1. MariaDB [(none)]> select * from CargoWarehouse.employee_list;  
2. +-----------+-----+-------+  
3. | name      | age | wages |  
4. +-----------+-----+-------+  
5. | Zhang san |  20 | 26000 |  
6. +-----------+-----+-------+  
7. 1 row in set (0.00 sec)  
8.   
9. MariaDB [(none)]> select * from CargoWarehouse.employee_list;  
10. +-----------+-----+-------+  
11. | name      | age | wages |  
12. +-----------+-----+-------+  
13. | Zhang san |  20 | 26000 |  
14. +-----------+-----+-------+  
15. 1 row in set (0.00 sec)

程序员妻子提交申请。

[sql] 
1. MariaDB [CargoWarehouse]> update employee_list set wages=200 where name='Zhang san';  
2. Query OK, 1 row affected (0.00 sec)  
3. Rows matched: 1  Changed: 1  Warnings: 0  
4.   
5. MariaDB [CargoWarehouse]> commit;  
6. Query OK, 0 rows affected (0.01 sec)

程序员再次查看自己的账户。

[sql]   
1. MariaDB [(none)]> select * from CargoWarehouse.employee_list;  
2. +-----------+-----+-------+  
3. | name      | age | wages |  
4. +-----------+-----+-------+  
5. | Zhang san |  20 | 26000 |  
6. +-----------+-----+-------+  
7. 1 row in set (0.00 sec)  
8.   
9. MariaDB [(none)]> select * from CargoWarehouse.employee_list;  
10. +-----------+-----+-------+  
11. | name      | age | wages |  
12. +-----------+-----+-------+  
13. | Zhang san |  20 | 26000 |  
14. +-----------+-----+-------+  
15. 1 row in set (0.00 sec)  
16.   
17. MariaDB [(none)]> select * from CargoWarehouse.employee_list;  
18. +-----------+-----+-------+  
19. | name      | age | wages |  
20. +-----------+-----+-------+  
21. | Zhang san |  20 |   200 |  
22. +-----------+-----+-------+  
23. 1 row in set (0.00 sec)

程序员再次一脸懵逼，明明在一个事务里，竟然查询结果还是不一样。太不符合事务的特性了

测试repeatable read

先修改隔离级别

[sql]  
1. MariaDB [CargoWarehouse]> set tx_isolation='REPEATABLE-READ';  
2. Query OK, 0 rows affected (0.00 sec)  
3.   
4. MariaDB [CargoWarehouse]> select @@tx_isolation;  
5. +-----------------+  
6. | @@tx_isolation  |  
7. +-----------------+  
8. | REPEATABLE-READ |  
9. +-----------------+  
10. 1 row in set (0.00 sec)  
11.   
12. MariaDB [CargoWarehouse]> start transaction;  
13. Query OK, 0 rows affected (0.00 sec)

程序员的工资卡只剩200元了，这时程序员妻子开始查账（并开启事务）。

[sql] 
1. MariaDB [CargoWarehouse]> select * from employee_list;  
2. +-----------+-----+-------+  
3. | name      | age | wages |  
4. +-----------+-----+-------+  
5. | Zhang san |  20 |   200 |  
6. +-----------+-----+-------+  
7. 1 row in set (0.00 sec)

程序员买了一包烟，并立马提交了事务。

[sql]  
1. MariaDB [CargoWarehouse]> update employee_list set wages=180 where name='Zhang san';  
2. Query OK, 1 row affected (0.00 sec)  
3. Rows matched: 1  Changed: 1  Warnings: 0  
4.   
5. MariaDB [CargoWarehouse]> commit;  
6. Query OK, 0 rows affected (0.00 sec)

程序员妻子查账还是200，并没有因为程序员的提交，而改变一个事务中的查询。（事务的隔离性提高了）

[sql] 
1. MariaDB [CargoWarehouse]> select * from employee_list;  
2. +-----------+-----+-------+  
3. | name      | age | wages |  
4. +-----------+-----+-------+  
5. | Zhang san |  20 |   200 |  
6. +-----------+-----+-------+  
7. 1 row in set (0.00 sec)  
8.   
9. MariaDB [CargoWarehouse]> select * from employee_list;  
10. +-----------+-----+-------+  
11. | name      | age | wages |  
12. +-----------+-----+-------+  
13. | Zhang san |  20 |   200 |  
14. +-----------+-----+-------+  
15. 1 row in set (0.00 sec)

看似这样做很棒了，但是这只解决了update，并没有解决insert。假设

程序员这个月表现的好，发了奖金。（再开个事务做insert操作）

[sql]  
1. MariaDB [CargoWarehouse]> update employee_list set wages=180 where name='Zhang san';  
2. Query OK, 1 row affected (0.00 sec)  
3. Rows matched: 1  Changed: 1  Warnings: 0  
4.   
5. MariaDB [CargoWarehouse]> commit;  
6. Query OK, 0 rows affected (0.01 sec)

程序员妻子

打印工资单

[sql]  
1. MariaDB [CargoWarehouse]> select * from employee_list;  
2. +-----------+-----+-------+  
3. | name      | age | wages |  
4. +-----------+-----+-------+  
5. | Zhang san |  20 |   200 |  
6. +-----------+-----+-------+  
7. 1 row in set (0.00 sec)  
8.   
9. MariaDB [CargoWarehouse]> commit;  
10. Query OK, 0 rows affected (0.00 sec)  
11.   
12. MariaDB [CargoWarehouse]> select * from employee_list;  
13. +-----------+-----+-------+  
14. | name      | age | wages |  
15. +-----------+-----+-------+  
16. | Zhang san |  20 |   180 |  
17. | Zhang     |  20 |  2000 |  
18. +-----------+-----+-------+  
19. 2 rows in set (0.00 sec)

串行就不演示了

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。