SQL语法整理[10]——事务与锁

 

SQL语法整理[10]——事务与锁

什么是事务

事务是单个的逻辑工作单元

如果某一事务成功，则在该事务中进行的所有数据更改提交，成为数据库中的永久组成部分。如果事务运行的过程中出现了错误，则数据修改就会被全部取消(在数据库中称之为ROLLBACK)。

事务用通俗的话来解释就是要么全部执行，要么全部不执行，这就是原子性。

 

事务分为以下几种：

a.自动提交事务：每条单独的语句都是事务。每一条语句实际上都是事务，对于INSERT   来说，输入的这些数值，如果有一个数据类型不符或者数值的长度过大，就会报错，也就是说，要么所有的数据都插入到一行中，要么所有的数据都没有插入到数据库中，这就是一个自动提交事务。

b.显示事务：每个事务均以BEGIN TRANSACTION语句开始，以COMMIT或ROLLBACK结束。

c.隐性事务：在前一个事务完成时，新事务启动，但仍以COMMIT或ROLLBACK语句显示完成。

d.批处理级事务：只能应用于多个活动结果集(MARS)，在MARS会话中，启动的Transact—SQL显示或隐性变为批处理级事务。当批处理完成时，没有提交或回滚的事务将自动由SQL Server进行。

 

并发性问题

例如：现有两家小的银行需要帐户之间转帐，通过两个存储过程来描述这次转帐。

(1)不使用事务造成的数据不一致性

/*创建两个存储用户储蓄的账户*/

/*创建两张结构相同的表格，表格名称分别为bank1与bank2*/

CREATE    TABLE    bank1

(

    deposit_id       INT,                ---储蓄账户编号

    name           CHAR(20),          ---储蓄账户的名称

    deposit         MONEY             --储蓄额

)

GO

CREATE    TABLE    bank2

(

    deposit_id       INT,                ---储蓄账户编号

    name           CHAR(20),          ---储蓄账户的名称

    deposit         MONEY             --储蓄额

)

GO

 

/*在表格bank1以及bank2中录入信息*/

---在bank1中录入两条信息

INSERT    INTO    bank1(deposit_id,name,deposit)

VALUES(1001,’王云’,10000)

GO

INSERT    INTO    bank1(deposit_id,name,deposit)

VALUES(1002,’李治’,20000)

GO

---在bank2中录入两条信息

INSERT    INTO    bank2(deposit_id,name,deposit)

VALUES(1003,’张冶’,9000)

GO

INSERT    INTO    bank2(deposit_id,name,deposit)

VALUES(1004,’李婷’,30000)

GO

 

 

/*创建两个转账的存储过程*/

/*创建名称为transfer_bank1与transfer_bank2的存储过程*/

CREATE   PROCEDURE    transfer_bank1   @deposit_id  INT   ,@deposit    MONEY

AS

   UPDATE       bank1

   SET   deposit=deposit+@deposit

   WHERE    deposit_id =@deposit_id

GO

 

CREATE   PROCEDURE    transfer_bank2   @deposit_id  INT   ,@deposit    MONEY

AS

   UPDATE       bank2

   SET   deposit=deposit+@deposit

   WHERE    deposit_id =@deposit_id

GO

 

/*使用存储过程实现转账功能*/

/*从账户编号为1001的储蓄用户转移5000到账户编号为1003的储蓄用户*/

EXECUTE    transfer_bank1   1001,-5000

GO

EXECUTE    transfer_bank2    1003,5000

GO

 

 

/*使用WAITFOR DELAY来模拟两个账户之间转账的时间延迟*/

EXECUTE     transfer_bank1     1001,-5000

GO

WAITFOR    DELAY   ’20:00’    ---代表延时20分钟以后执行以下语句

EXECUTE     transfer_bank2     1003,5000

GO

选中上述代码运行，然后打开SQL Server Configuration Manager管理器，将SQL Server(MSSQLSERVER)服务停止；

然后将SQL Server(MSSQLSERVER)服务重新启动。

 

<当服务器再重新启动时，就会发现bank1中的王云帐户，已经将5000元转移出去了，但是在bank2中，张治帐户并没有接收到。这就是在没有维护事务一致性的情况下，造成的结果。>

 

(2)通过事务维护原子性

/*使用显示事务来将转帐标识*/

/*从账户编号1002的储蓄用户转移给账户编号为1004的储蓄用户5000*/

--使用WAITFOR  DELAY模拟数据转移过程中的时间延迟

USE  school

GO

BEGIN    TRANSACTION    T1

EXECUTE     transfer_bank1     1002,-10000

GO

WAITFOR    DELAY   ’20:00’    ---代表延时20分钟以后执行以下语句

EXECUTE     transfer_bank2     1004,10000

COMMIT   TRANSACTION   T1

 

选中上述代码运行，然后打开SQL Server Configuration Manager管理器，将SQL Server(MSSQLSERVER)服务停止；

然后将SQL Server(MSSQLSERVER)服务重新启动查看结果

 

<从运行来看，当转帐的操作被显示的事务标识后，如果在执行的过程中服务器出现异常，则当数据库重新启动以后就会发现数据没有被改变，这就实现了通过显示事务来维护数据的一致性。>

 

锁的介绍

锁是Microsoft SQL Server Database Engine用于同步多个用户同时对一个数据块的访问的一种机制。为了解决并发访问问题，SQL Server提供了锁的机制，通过加锁来限定用户访问资源的并发问题。

 

死锁及处理：

在事务和锁的使用过程中，死锁是一个不可避免的现象。在下列两种情况下，可以发生死锁。

第一种情况是，当两个事务分别锁定了两个单独的对象，这时每一个事务都要求在另外一个事务锁定的对象上获得一个锁，因此每一个事务都必须等待另外一个事务释放占有的锁，这时，就发生了死锁。这种死锁是最典型的死锁形式。

第二种情况是，当在一个数据库中，有若干个长时间运行的事务执行并行的操作，当查询分析器处理一种非常复杂的查询例如连接查询时，那么由于不能控制处理的顺序，有可能发生死锁现象。

 

当发生死锁现象时，除非某个外部进程断开死锁，否则死锁中的两个事务都将无限期等待下去。SQL Server2005的SQL Server Database Engine自动检测SQL Server中的死锁循环。数据库引擎选择一个会话作为死锁牺牲品，然后终止当前事务(出现错误)来打断死锁。如果监视器检测到循环依赖关系，通过自动取消其中一个事务来结束死锁。在发生死锁的两个事务中，根据事务处理时间的长短作为规则来确定其优先级。处理时间长的事务具有较高的优先级，处理时间较短的事务具有较低的优先级。在发生冲突时，保留优先级高的事务，取消优先级低的事务。

——大二的期末考试整理 peace_power@126.com