MySql 事务&锁机制

最新推荐文章于 2024-04-25 09:00:00 发布

瓦蓝色的天空

最新推荐文章于 2024-04-25 09:00:00 发布

阅读量326

点赞数 1

分类专栏： MySQL事务文章标签： mysql 事务数据库

本文链接：https://blog.csdn.net/qq_37487520/article/details/78876864

版权

MySQL事务专栏收录该内容

1 篇文章 0 订阅

订阅专栏

事务

一. MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务.

1.在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
2.事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
3.事务用来管理 insert,update,delete 语句

二. 一般来说，事务是必须满足4个条件（ACID）： Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）、Durability（可靠性）

1. 事务的原子性：一组事务，要么成功；要么撤回。
2. 稳定性 ：有非法数据（外键约束之类），事务撤回。
3. 隔离性：事务独立运行。一个事务处理后的结果，影响了其他事务，那么其他事务会撤回。事务的       100%隔离，需要牺牲速度。
4. 可靠性：软、硬件崩溃后，InnoDB数据表驱动会利用日志文件重构修改。可靠性和高速度不可兼得， innodb_flush_log_at_trx_commit 选项 决定什么时候吧事务保存到日志里。

三. 在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

四. 事物控制语句

BEGIN或START TRANSACTION；显式地开启一个事务；

COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；

ROLLBACK；也可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；

RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier；把事务回滚到标记点；

SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

五. 事务的四种隔离级别

1. READ UNCOMMITTED(未提交读)。在RU的隔离级别下，事务A对数据做的修改，即使没有提交，对于事务B来说也是可见的，这种问题叫脏读。这是隔离程度较低的一种隔离级别，在实际运用中会引起很多问题，因此一般不常用。

2.READ COMMITTED(提交读)。在RC的隔离级别下，不会出现脏读的问题。事务A对数据做的修改，提交之后会对事务B可见，举例，事务B开启时读到数据1，接下来事务A开启，把这个数据改成2，提交，B再次读取这个数据，会读到最新的数据2。在RC的隔离级别下，会出现不可重复读的问题。这个隔离级别是许多数据库的默认隔离级别。

3.REPEATABLE READ(可重复读)。在RR的隔离级别下，不会出现不可重复读的问题。事务A对数据做的修改，提交之后，对于先于事务A开启的事务是不可见的。举例，事务B开启时读到数据1，接下来事务A开启，把这个数据改成2，提交，B再次读取这个数据，仍然只能读到1。在RR的隔离级别下，会出现幻读的问题。幻读的意思是，当某个事务在读取某个范围内的值的时候，另外一个事务在这个范围内插入了新记录，那么之前的事务再次读取这个范围的值，会读取到新插入的数据。Mysql默认的隔离级别是RR，然而mysql的innoDB引擎间隙锁成功解决了幻读的问题。

4.SERIALIZABLE(可串行化)。可串行化是最高的隔离级别。这种隔离级别强制要求所有事务串行执行，在这种隔离级别下，读取的每行数据都加锁，会导致大量的锁征用问题，性能最差。

- 补充1:

隔离级别越高，并发性能越低
在MySQL数据库中，支持上面四种隔离级别，默认的为Repeatable read (可重复读)；而在Oracle数据库中，只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别，其中默认的为Read committed级别。

- 补充2:

脏读（dirty read）：一个事务可以读取另一个尚未提交事务的修改数据
不可重复读（nonrepeatable read）：在同一个事务中，同一个查询在T1时间读取某一行，在T2时间重新读取这一行时候，这一行的数据已经发生修改，可能被更新了（update），也可能被删除了（delete）。
幻读（phantom read）：在同一事务中，同一查询多次进行时候，由于其他插入操作（insert）的事务提交，导致每次返回不同的结果集。

六. MYSQL 事务处理主要有两种方法

用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认
直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

测试

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  //创建数据表
Query OK, 0 rows affected (0.04 sec)

mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)

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

mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)

mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)

mysql> commit; //提交事务
Query OK, 0 rows affected (0.01 sec)

mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

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

mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)

mysql> rollback;   //回滚
Query OK, 0 rows affected (0.00 sec)

mysql>   select * from runoob_transaction_test;   //因为回滚了，所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

mysql>

七. 锁机制

1. 为什么要锁?
数据库是一个多用户使用的共享资源,比如一个用户表t_user,两个浏览器前面的人登录了同个一个账号，把电话号码改了。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性(脏读，不可重复读，幻读等)，可能产生死锁。为了解决这个问题，加锁是一个非常重要的技术，对实现数据库并发控制是一个好的方案。简单说，当一个执行sql语句的事务想要操作表记录之前，先向数据库发出请求，对你访问的记录集加锁，在这个事务释放这个锁之前，其他事务不能对这些数据进行更新操作。

2. 有哪些锁?
1.行级锁:防止其他事务修改此行;在使用以下语句时，Oracle会自动应用行级锁：INSERT、UPDATE、DELETE、SELECT … FOR UPDATE [OF columns] [WAIT n | NOWAIT];SELECT … FOR UPDATE语句允许用户一次锁定多条记录进行更新.使用commit或者rollback释放锁。

注意：MySql的innodb存储引擎默认是行级锁

特点:开锁大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。适合于有大量按索引更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理系统。

2.表级锁:5种
- 行共享 (ROW SHARE): 禁止排他锁定表，与行排他类似，区别是别的事务还可以在此表上加任何排他锁。（除排他（exclusive）外）

行排他(ROW EXCLUSIVE): 禁止使用排他锁和共享锁，其他事务依然可以并发地对相同数据表执行查询，插入，更新，删除操作，或对表内数据行加锁的操作，但不能有其他的排他锁（自身是可以的，没发现有什么用）
共享锁(SHARE): 锁定表，对记录只读不写，多个用户可以同时在同一个表上应用此锁，在表没有被任何DML操作时，多个事务都可加锁，但只有在仅一个事务加锁的情况下只有此事务才能对表更新；当表已经被更新或者指定要更新时（select for update），任何事务都不能加此锁了。
共享行排他(SHARE ROW EXCLUSIVE): 比共享锁更多的限制，禁止使用共享锁及更高的锁，在表没有被任何CRUD操作时，只有一个事务可以加锁，可以更新，书上说别的事务可以使用select forupdate锁定选中的数据行，可是实验后没被验证。
排他(EXCLUSIVE): 限制最强的表锁，仅允许其他用户查询该表的行。禁止修改和锁定表

//行级锁和表级锁是根据锁的粒度来区分的，行记录，表都是资源，锁是作用在这些资源上的。如果粒度比较小(比如行级锁)，可以增加系统的并发量但需要较大的系统开销，会影响到性能，出现死锁，因为粒度小则操作的锁的数量会增加;如果作用在表上，粒度大，开销小，维护的锁少，不会出现死锁，但是并发是相当昂贵的，因为锁定了整个表就限制了其它事务对这个表中其他记录的访问。

3.悲观锁:
Pessimistic Lock正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守悲观态度，事务每次去操作数据的时候都假设有其他事务会修改需要访问的数据，所以在访问之前都要求上锁，行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁，因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制（也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。一个典型的倚赖数据库的悲观锁调用： select * from account where name=”Erica” for update 这条sql 语句锁定了account 表中所有符合检索条件（name=”Erica”）的记录。本次事务提交之前（事务提交时会释放事务过程中的锁），外界无法修改这些记录。

Demo1

//MySQL默认使用autocommit模式，也就是说，当执行一个更新操作后，MySQL会立刻将结果进行提交。要使用悲观锁，必须关闭mysql数据库的自动提交属性set autocommit=0; 

//设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：
//0.开始事务
begin;  begin work;  start transaction; (三者选一就可以)

//1.查询商品信息，id=1这条记录在事务提交之前，其他事务无法修改这条数据（此时可以查询该记录，但如果查询语句也用for update结尾是不可以的）
select status from t_goods where id=1 for update;//MySql虽然默认行级锁，但如果不指定哪些记录的话（比如这里去掉 where id=1），则会执行表级锁

//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);

//3.修改商品status为2
update t_goods set status=2;

//4.提交事务
commit;  或者  commit work;

4.乐观锁:
Optimistic Lock,和悲欢锁相反，事务每次去操作数据之前，都假设其他事务不会修改这些需要访问的数据，所以在访问之前不要求上锁，只是在进行更新修改操作的时候判断一下在访
问的期间有没有其他人修改数据了。它适用于多读的应用类型，冲突真的发生比较少的时候就比较好，这样省去了开销的开销，可以提高吞吐量;但如果是真的经常要发生冲突的，那每次
还要去判断进行retry,反倒降低的性能，这个时候悲欢锁比较好。数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。
它的实现大多是基于数据版本version记录机制。举个例子：
#Demo：
给银行账户表t_account添加一个字段version作为数据版本。读取出数据时，将此版本号一同读出;更新数据时，将version加1，此时，将提交数据的版本数据与数据库表对应记录的
当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

Demo2


//t_account表                    
    +----+--------+-----------+---------+---------+    
    | id | name   | account   | money   | version |
    +----+--------+-----------+---------+---------+    
    |  1 | 张三   | 666666    | 1000    |    1    |

银行操作员A读取张三账户信息（account=666666，version=1，money=1000），然后向其账户转入300元

update t_account  set  money=money+300,version=version+1  where account=#{account}  and  version=#{version};  //更新成功

如果在操作员A读取张三账户信息后，但还未向数据库提交更新时，银行操作员B读取张三账户信息（account=666666，version=1，money=1000），然后操作员A提交更新成功，
然后操作员B向张三转账400元     

update t_account  set  money=money+400,version=version+1  where account=#{account}  and  version=#{version};   //此时更新失败，因为已经不存在account=666666，version=1的记录了

//和SVN版本冲突很类似，可以联系起来理解

小结:
//程序修改比较少查询比较多：乐观锁;
//程序查询比较少修改比较多：悲观锁。





#并发事务的丢失更新(lost update )         //参考：http://www.cnblogs.com/doucheyard/p/5662171.html

瓦蓝色的天空

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySql 事务&锁机制

事务一. MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务.1.在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。2.事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。3
复制链接

扫一扫