mysql 存储过程 行锁_mySQL教程 第10章 事务和锁

第10章 事务和锁

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

表级锁

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

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

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

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

准备实验环境

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

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

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

在添加一个回话

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

双击 session 1，点击红框图标

可以看到session1建立的回话。

创建存储引擎为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

练习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

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

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

insert into TStudent values

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

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

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

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

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

update TStudent set sname='韩利刚'

where studentid='00001'

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

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

unlock tables

可以看到session_2完成更新。

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

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

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

lock tables TStudent write

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

行级锁

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

准备实验环境

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

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

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

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

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

Session1执行以下命令

select * from TStudent where studentid='00010'

在session2执行以下命令

select * from TStudent where studentid='00010'

没有该记录

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

start transaction

insert into TStudent values

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

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

select * from TStudent where studentid='00010'

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

在session1提交事务

Commit

在session2查看插入的记录

select * from TStudent where studentid='00010'

练习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

总结：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'

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

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

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

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

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

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分。

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

关闭自动提交

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

关闭自动提交

set autocommit=0

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

show variables like '%autocommit%';

广告