mySQL教程 第10章 事务和锁

  1. 云栖社区>
  2. 博客>
  3. 正文

mySQL教程 第10章 事务和锁

技术小阿哥 2017-11-03 12:47:00 浏览593
展开阅读全文

第10章 事务和锁

数据库事务(Database Transaction) ,是指作为单个逻辑工作单元执行的一系列操作。 事务处理可以确保除非事务性单元内的所有操作都成功完成,否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元,可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务,必须满足所谓的ACID(原子性、一致性、隔离性和持久性)属性。

表级锁

myISAM存储引擎和InnoDB存储引擎都支持表级锁。

myISAM存储引擎支持表级锁,为了保证数据的一致性,更改数据时,防止其他人更改数据,可以人工添加表级锁。我们可以使用命令对数据库的表枷锁,使用命令对数据库的表解锁。

给表加锁的命令Lock Table,给表解锁的命令Unlock Tables

现在是学习阶段,用lock Tables和Unlock Tables显示加锁和解锁。其实MyISAM引擎在用户读数据自动加read锁,更改数据自动加write锁。

准备实验环境

使用sqlmanager建立两个回话连接到MySQL。

验证给表加锁,对其他用户的影响。

clip_image001

clip_image002

注意数据库别名schoolDB session_1表明是第一个回话。

clip_image003

在添加一个回话

clip_image004

clip_image005

注意数据库别名是schoolDB session_2表明是第二个回话。

clip_image006

双击 session 1,点击红框图标

clip_image007

可以看到session1建立的回话。

clip_image008

创建存储引擎为myISAM的表。

CREATE TABLE `TStudent` (

`StudentID` varchar(15) NOT NULL,

`Sname` varchar(10) DEFAULT NULL,

`sex` char(1) DEFAULT NULL,

`cardID` varchar(20) DEFAULT NULL,

`Birthday` datetime DEFAULT NULL,

`Email` varchar(40) DEFAULT NULL,

`Class` varchar(20) DEFAULT NULL,

`enterTime` datetime DEFAULT NULL

) ENGINE=myISAM DEFAULT CHARSET=utf8;

创建成绩表

CREATE TABLE `TScore` (

`StudentID` varchar(15) DEFAULT NULL,

`subJectID` varchar(10) DEFAULT NULL,

`mark` decimal(10,0) DEFAULT NULL

) ENGINE=myISAM DEFAULT CHARSET=utf8;

插入记录

insert into TStudent values

('00001','张四非','男','132302189009082324','19820203','zsf@hotmail.com','JAVA',NOW()),

('00002','张二臣','男','132302192009082324','19890203','zec@hotmail.com','JAVA',NOW()),

('00003','李玉红','女','132302189009082324','19890203','LYH@hotmail.com','NET',NOW())

插入成绩

insert TScore values

('00001','0001',89),

('00001','0002',97),

('00002','0001',89),

('00002','0002',68),

('00003','0001',78),

('00003','0002',76)

在以同样的方式建立使用root建立一个回话session2

clip_image009

clip_image010

clip_image011

练习1:验证表级锁对用户并发性影响

Read锁是共享锁,不影响其他回话的读取,但是不能更新加read锁的数据。MyISAM表的读写是串行的,但这是总体而言的,在一定条件下,myISAM表也支持查询和插入操作的并发进行。

MyISAM有个系统变量concurrent Insert,设置为0时,不允许并发插入。

设置为1时,MyISAM表没有空洞,允许一个进程读取表时,另一个进程从表尾插入记录,这是默认设置。

设置为2时,无论MyISAM表中有没有空洞,都允许在表末尾并发插入记录。

1. 验证read锁

在session1执行以下语句给TStudent表加read锁,该锁允许其他回话能够查询,但是不能更改。

lock tables TStudent read local

在session2上执行以下语句

select * from TStudent

可以看到能够查询出结果。

clip_image012

2. 在session_2可以并发插入新记录

insert into TStudent values

('00004','刘立秋','男','132302189009082324','19820203','llq@hotmail.com','JAVA',NOW())

clip_image013

3. 在session_1上不能删除更新数据

添加read锁后,自己也不能更改数据

clip_image014

4. 在Session_1上不能查看没有锁定的表

clip_image015

5. Session2更新表中的一个记录

update TStudent set sname='韩利刚'

where studentid='00001'

你会发现一直没有执行完的结果出现

在Session1上执行以下命令 解锁表

unlock tables

可以看到session_2完成更新。

clip_image016

练习2:在session1给TStudent表添加write锁

write锁,是独占锁。其他回话不能查询加了write锁的表。

6. 在session1上给TStudent表添加write锁,在session2上查询TStudent表,你会处于发现等待状态。

lock tables TStudent write

clip_image017

7. 在session1上执行unlock tables,可以看到session立即出现查询结果。

clip_image018

行级锁

事务加锁,是这对所操作的行,对其他行不进行加锁处理。

准备实验环境

支持事务的存储引擎是InnoDB,必须将数据库或表的存储引擎设置InnoDB支持事务。

clip_image019

默认情况下mySQL是自动提交事务,如果需要通过明确的Commit和Rollback来提交和回滚事务,那么就需要通过明确的事务控制命令开始。

练习3:不能查看其它事务未提交的数据记录

以下练习使用显示事务为你演示,每个事务使用start transaction开始,使用commit提交。

以下为你展示:只有提交了的事务,数据变化才被写到数据库,其他回话才能看到其变化。

Session1执行以下命令

select * from TStudent where studentid='00010'

在session2执行以下命令

select * from TStudent where studentid='00010'

没有该记录

clip_image020

在session1上开始一个事务,插入一条记录

start transaction

insert into TStudent values

('00010','张非','男','132302189009082324','19820203','zsf@hotmail.com','JAVA',NOW())

在session2上,输入以下命令查询插入的记录,看好了,先点击clip_image021,再点击clip_image022

select * from TStudent where studentid='00010'

发现没有。因为session1的事务没有提交。

clip_image023

在session1提交事务

Commit

在session2查看插入的记录

select * from TStudent where studentid='00010'

clip_image024

练习4:使用select语句添加独占锁

默认情况下select不添加锁,你可以使用命令显式添加共享锁或排它锁。

在Session_1上,使用select….for update语句为studentid为00001的学生添加独占锁。

set autocommit=0 关闭自动提交

Select * from TStudent where studentid='00001' for update

update TStudent set sname='韩力刚' where studentid='00001'

commit

在Session上查询studentid是00002的记录,能够成功查询,可见InnoDB数据库引擎支持行级锁。

set autocommit=0

select * from TStudent where studentid='00001'

select * from TStudent where studentid='00001' for update

select * from TStudent where studentid='00001' for update

clip_image025

总结:Select 语句中使用 for update加锁记录不影响其他事务读取数据,但其他事务不能加独占锁。

练习5:使用select语句添加共享锁 (产生死锁的过程)

A事务添加共享锁后,B事务页可以添加共享锁。这时A事务udpdate锁定记录,处于等待中,于此同时B事务也update更新锁定的记录,就产生死锁。下面练习演示死锁的产生。

在session_1上的语句

set autocommit=0

select sname from TStudent where studentid='00001' lock in share mode

update TStudent set sname='韩利钢' where studentid='00001'

在session_2上的语句

set autocommit=0

select sname from TStudent where studentid='00001' lock in share mode

update TStudent set sname='韩力钢' where studentid='00001'

clip_image026

使用条件确定事务和回滚事务

以下代码实现了由条件控制事务的提交和回滚,事务是应该回滚还是提交,由程序员确定。

练习6:在存储过程中使用事务

创建存储过程,给指定学号的学生加分,如果超过100分,回滚事务。将加分的学生成绩放到临时表,然后再查临时表中最高分是否大于100分,如果大于100分就回滚事务,临时表用完就删除。

将TScore表的存储引擎更改为InnoDB,

clip_image027

create procedure addMark(stuid varchar(6),plusMark int)

begin

declare Pmark int;

CREATE TEMPORARY TABLE tmp_table(mark int);

start transaction;

update TScore set mark= mark+plusMark where StudentID=stuid;

insert tmp_table select mark from TScore where StudentID=stuid;

select max(mark) into Pmark from tmp_table;

if Pmark>100 then

rollback;

else

commit;

end if;

end查询学号是00001的学生,现有分数。

select * from TScore where studentid='00001'

call addMark('00001',1)

为该生所有科目加1分。

clip_image028

clip_image029

总结:事务是提交还是回滚,完全由开发人员定义。

关闭自动提交

默认mySQL自动提交SQL语句,如果打算显示提交,需要关闭自动提交。

关闭自动提交

set autocommit=0

查看自动提交的设置是否打开

show variables like '%autocommit%';

clip_image030




本文转自 onesthan 51CTO博客,原文链接:http://blog.51cto.com/91xueit/1137981,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
技术小阿哥
+ 关注