12.事务控制和锁定语句

于 2020-09-03 13:13:12 发布
阅读量213 收藏
点赞数
CC 4.0 BY-SA版权
分类专栏： MySQL 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_43282511/article/details/108380866

文章目录


MySQL支持对MyISAM和MEMORY存储引擎的表进行表级锁定,对BOB存储引擎的表进行页级锁定,对InnnoDB存储引擎的表进行行级锁定.默认情况下,表锁和行锁都是自动获得的,不需要额外的命令。但是再有的情况下，用户需要明确的进行锁表或者进行事务的控制，以便确保整个事务的完整性，这样就需要使用事务控制华为锁定语句来完成。

1.LOACK TABLE和UNLOCK TABLE

LOCk TABLE 可以锁定用于当前线程的表。如果表被其他线程锁定，则当前线程会等待，直到可以获取所有的锁为止。

UNLOCK TABLES 可以释放当前线程获得的任何锁定。当前线程执行另一个LOCK TABLES时，或当与服务器的连接被关闭时，所有当前线程锁定的表被隐含的解锁，具体语法如下:

LOCK TABLES
	tbl_name [as alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
	[,tbl_name] [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES

如下是一个获得表锁和释放表锁的简单例子。

  • 创建测试表
 create table lock_demo(
 	id int auto_increment,
 	meg varchar(20),
	primary key(id)
 )engine=MYISAM;
-- Query OK, 0 rows affected (0.09 sec)
  • 插入数据
insert into lock_demo values(0,'scffgdsfg'),(0,'57777'),(0,'treg');
--Query OK, 3 rows affected (0.00 sec)
--Records: 3  Duplicates: 0  Warnings: 0
  • 锁定表
lock table lock_demo read;
--Query OK, 0 rows affected (0.00 sec)
  • 查询表中的数据
select * from lock_demo;
--+----+-----------+
--| id | meg       |
--+----+-----------+
--|  1 | scffgdsfg |
--|  2 | 57777     |
--|  3 | treg      |
--+----+-----------+
--3 rows in set (0.00 sec)
  • 重新开启一个窗口，新建一个 session这里标注为session2，而原来的session标注为session1
mysql -uroot -proot
use test;
  • 通过新建的session2查询数据
select * from lock_demo;
--+----+-----------+
--| id | meg       |
--+----+-----------+
--|  1 | scffgdsfg |
--|  2 | 57777     |
--|  3 | treg      |
--+----+-----------+
--3 rows in set (0.01 sec)

可以正常查询数据

  • 使用新建session2更新表
update lock_demo set msg='update message' where id = 1;
等待

更新操作一直处于等待状态。

  • session1释放锁
UNLOCK TABLES;
--Query OK, 0 rows affected (0.00 sec)

--session2获得锁更新成功
update lock_demo set meg='update message' where id = 1;
--Query OK, 1 row affected (0.00 sec)
--Rows matched: 1  Changed: 1  Warnings: 0

2. 事务控制

MySQL通过SET AUTOCOMMIT、START　TRANSACTION、ＣＯＭＭＩＴ和ＲＯＬＬＢＡＣＫ等语句支持本地事务，具体与法如下：

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT={0 | 1}

默认情况下,mysql是自动提交(autocommit)事务的,如果需要通过明确的Commit和Rollback来提交和回滚事务,那就需要明确的事务控制命令来控制事务.如果应用是从Oracle数据迁移到Mysql数据库,则需要确保应用中是否对事务进行了明确的管理.

  • START TRANSACTION或BEGIN语句可以开启一项新的事务.
  • COMMIT和ROLLBACK用来提交和回滚事务.
  • CHAIN和RELEASE字句分别来定义事务在提交前或回滚之后的操作,CHAIN会立即启动一个新事务,并且和刚才的事务具有相同的隔离级别,RELEASE则会断开和客户端的连接.
  • SET AUTOCOMMIT可以修改当前连接的提交方式,如果设置了SET AUTOCOMMIT=0,则设置之后的所有事务都需要明确的命令提交或者回滚.

如果只对某些语句进行事务控制的时候,可以使用START TRANSACTION命令来开启一个事务比较方便,这样事务结束后就可以回到自动提交事务的方式 如果希望事务都不是自动提交的,那么通过修改AUTOCOMMIT比较方便,这样就不用在每个事务开始的时候在执行START TRABSATION语句.

下面是一个通过START TRANSACTION来开启事务的示例

  • 新建表tran_demo
create table tran_demo(
	id int auto_increment,
	context varchar(50),
	primary key (id)
);
--Query OK, 0 rows affected (0.25 sec)
  • 在session1中查询表中数据
select * from tran_demo;
--Empty set (0.01 sec)
  • 在session2中查询表中数据
select * from tran_demo;
--Empty set (0.00 sec)
  • 在session中通过START TRANSACTION来启动一个事务,项表中插入一条数据没有提交
START TRANSACTION;
--Query OK, 0 rows affected (0.00 sec)
insert into tran_demo values(0,'this is a transaction demo');
--Query OK, 1 row affected (0.00 sec)
  • 在session2 中查询表tran_demo,表中数据任然为空
select * from tran_demo;
--Empty set (0.00 sec)
  • 在session1中提交事务
commit;
--Query OK, 0 rows affected (0.06 sec)
  • 再次在session2中查询表数据,可以查询到结果
select * from tran_demo;
--+----+----------------------------+
--| id | context                    |
--+----+----------------------------+
--|  1 | this is a transaction demo |
--+----+----------------------------+
--1 row in set (0.00 sec)
  • 通过session1再次向表中插入数据
insert into tran_demo values(0,'after commit');
Query OK, 1 row affected (0.07 sec)
  • 通过session2查询表中数据,可以查询到刚才插入的数据
select * from tran_demo;
--+----+----------------------------+
--| id | context                    |
--+----+----------------------------+
--|  1 | this is a transaction demo |
--|  2 | after commit               |
--+----+----------------------------+
--2 rows in set (0.00 sec)
  • 在session1中重新启动一个事务
START TRANSACTION;
--Query OK, 0 rows affected (0.00 sec)
  • 向表tran_demo中插入一条数据
insert into tran_demo values(0,'this is chain demo');
  • 使用commit and chain 提交事务
commit and chain;
--Query OK, 0 rows affected (0.04 sec)

此时自动开启了一个新的事务

insert into tran_demo values(0,'this is new transaction');
  • 在session2中查询表中数据
select * from tran_demo;
--+----+----------------------------+
--| id | context                    |
--+----+----------------------------+
--|  1 | this is a transaction demo |
--|  2 | after commit               |
--|  3 | this is chain demo         |
--+----+----------------------------+
--3 rows in set (0.00 sec)

查询不到刚才session1中插入的数据

  • 在session1中提交事务
commit;
--Query OK, 0 rows affected (0.04 sec)
  • 再次在session2中查询表tran_demo中数据
select * from tran_demo;
--+----+----------------------------+
--| id | context                    |
--+----+----------------------------+
--|  1 | this is a transaction demo |
--|  2 | after commit               |
--|  3 | this is chain demo         |
--|  4 | this is new transaction    |
--+----+----------------------------+
--4 rows in set (0.00 sec)

如果在锁表期间,用START TRANSATION来启动一个新的事务,会造成一个隐含的unlock table被执行,如下示例

  • 从session1和session2中分别查询表tran_demo表中id为10的记录为空
--session1
select * from tran_demo where id= 10;
--Empty set (0.00 sec)

--session2
select * from tran_demo where id= 10;
--Empty set (0.00 sec)
  • 在session1中对表tran_demo加表锁
lock table tran_demo write;
--Query OK, 0 rows affected (0.02 sec)
  • 在session2中查询表tran_demo的数据,读操作被阻塞
select * from tran_demo;
--等待
  • 在session1中通过 STARTTRANSACTION命令重新开启一个事务
START TRANSACTION;
--Query OK, 0 rows affected (0.00 sec)

--表锁被释放,session2中查询进程查询到数据
--+----+----------------------------+
--| id | context                    |
--+----+----------------------------+
--|  1 | this is a transaction demo |
--|  2 | after commit               |
--|  3 | this is chain demo         |
--|  4 | this is new transaction    |
--+----+----------------------------+
--4 rows in set (1 min 39.97 sec)

因此在同一个事务中最好不要使用不同存储引擎的表,否则ROLLBACK时需要对非事务类型的表进行特别的处理,因为COMMIT、ROLLBACK只能对事务类型的表进行提交和回滚。

通常情况下，只对提交的事务记录到二进制的日志中，但是如果一个事务中包含非事务类型的表，南无回滚操作也会被记录到二进制日志中，以确保非事务类型的表的更新可以被复制从（Slave）数据库中。

和二Oracle的事务管理相同，所有的DDL语句是不能回滚的，并且部分的DDL语句会造成隐式的提交。

在事务中可以定义SAVEPOINT,指定回滚事务的一部分,但是不能指定提交事务的一部分.对应复杂的应用,可以定义多个复杂的SAVEPOINT,满足不同的有条件时,回滚不同的SAVEPOINT.需要注意的是,如归定义了相同名字的SAVEPOINT,则后面定义的SAVEPOINT会覆盖前面的定义。对于不再使用的SAVEPOINT,可以通过RELEASE SAVEPOINT命令删除SAVEPOINT ,删除后的SAVEPOINT不再执行ROLLBACK TO SAVEPOINT命令．

如下是一个通过SAVEPOINT来指定需要回滚的事务的案例

模拟回滚事务

  1. 从session1中查找表tran_demo中context='SAVEPOINT’的记录,结果为空
select * from tran_demo where context='SAVEPOINT';
--Empty set (0.05 sec)
  1. 从session2中查询表tran_demo中context='SAVEPOINT’的记录,结果为空
select * from tran_demo where context='SAVEPOINT';
--Empty set (0.05 sec)
  1. 在session1中启动一个事务,并在表tran_demo中插入一条数据
start transaction;
--Query OK, 0 rows affected (0.00 sec)
insert into tran_demo values(0,'SAVEPOINT')
--Query OK, 1 row affected (0.00 sec)
  1. 在session1中可以查询到刚才插入的数据
select * from tran_demo where context = 'SAVEPOINT';
--+----+-----------+
--| id | context   |
--+----+-----------+
--|  5 | SAVEPOINT |
--+----+-----------+
--1 row in set (0.00 sec)
  1. 在session2中无法查询到刚才插入的数据
select * from tran_demo where context = 'SAVEPOINT';
--Empty set (0.00 sec)
  1. 在session1中定义savepoint,命名为test
savepoint test;
--Query OK, 0 rows affected (0.00 sec)
  1. 在session1中继续插入一条数据
insert into tran_demo values(0,'savepoint test');
--Query OK, 1 row affected (0.00 sec)
  1. session1中可以查询到两条数据
select * from tran_demo where context like 'savepoint%';
--+----+----------------+
--| id | context        |
--+----+----------------+
--|  5 | SAVEPOINT      |
--|  6 | savepoint test |
--+----+----------------+
--2 rows in set (0.01 sec)
  1. 在session2中查询数据,结果不存在
select * from tran_demo where context like 'savepoint%';
--Empty set (0.00 sec)
  1. 在session1中回滚到刚才定义的savepoint
rollback to savepoint test;
--Query OK, 0 rows affected (0.01 sec)

--查询数据只能查询到一条记录,因为刚才的数据被回滚
select * from tran_demo where context like 'savepoint%';
--+----+-----------+
--| id | context   |
--+----+-----------+
--|  5 | SAVEPOINT |
--+----+-----------+
--1 row in set (0.00 sec)
  1. 在session2中任然无法查询到数据
select * from tran_demo where context like 'savepoint%';
--Empty set (0.00 sec)
  1. session1中通过commit命令提交事务
commit;
--Query OK, 0 rows affected (0.07 sec)

--只能从表中查询到一条数据
select * from tran_demo where context like 'savepoint%';
--+----+-----------+
--| id | context   |
--+----+-----------+
--|  5 | SAVEPOINT |
--+----+-----------+
--1 row in set (0.00 sec)
  1. 通过session2只能查询到一条数据
select * from tran_demo where context like 'savepoint%';
--+----+-----------+
--| id | context   |
--+----+-----------+
--|  5 | SAVEPOINT |
--+----+-----------+
--1 row in set (0.00 sec)
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

姑苏_IT

创作不易,谢你打赏

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值