数据库中的事务和锁

标签: 数据库 | 发表时间:2012-08-18 00:15 | 作者:zztfj
出处:http://blog.csdn.net

数据库中的事务和锁

一、事务的ACID特性

1、 A (Atomicity)  原子性

事务必须是原子工作单元;对于其数据修改,要么全都执行,要么全都不执行。

2、 C (Consistency)一致性

事务在完成时,必须使所有的数据都保持一致状态。在相关数据库中,所有规则都必须应用于事务的修改,以保持所有数据的完整性。事务结束时,所有的内部数据结构(如 B树索引或双向链表)都必须是正确的。

3、I (Isolation)   隔离性 

并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务识别数据时数据所处的状态,要么是另一并发事务修改它之前的状态,要么是第二个事务修改它之后的状态,事务不会识别中间状态的数据。

4、D (Durability)  持久性

事务完成之后,它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

 

二、完整的事务

BEGIN a transaction: 设置事务的起始点

COMMIT a transaction: 提交事务,使事务提交的数据成为持久,不可更改的部分.

ROLLBACK a transaction:撤消一个事务,使之成为事务开始前的状态.

SAVE a transaction:建立一个标签,做为部分回滚时使用,使之恢复到标签初的状态.

 

事务的语法:

BEGIN TRAN[SACTION] [<transaction name>|<@transaction variable>][WITH MARK [’<description>’]][;] 

COMMIT [TRAN[SACTION] [<transaction name>|<@transaction variable>]][;]

ROLLBACK TRAN[SACTION] [<transaction name>|<save point name>|<@transaction variable>|<@savepoint variable>][;]

SAVE TRAN[SACTION] [<save point name>| <@savepoint variable>][;]

 

事务完整的例子:

USE AdventureWorks2008; -- We’re making our own table - what DB doesn’t matter
-- Create table to work with
CREATE TABLE MyTranTest
(
OrderID INT PRIMARY KEY IDENTITY
);
-- Start the transaction
BEGIN TRAN TranStart;
-- Insert our first piece of data using default values.
-- Consider this record No1. It is also the 1st record that stays
-- after all the rollbacks are done.
INSERT INTO MyTranTest
DEFAULT VALUES;
-- Create a "Bookmark" to come back to later if need be
SAVE TRAN FirstPoint;
-- Insert some more default data (this one will disappear
-- after the rollback).
-- Consider this record No2.
INSERT INTO MyTranTest
DEFAULT VALUES;
-- Roll back to the first savepoint. Anything up to that
-- point will still be part of the transaction. Anything
-- beyond is now toast.
ROLLBACK TRAN FirstPoint;
INSERT INTO MyTranTest
DEFAULT VALUES;
-- Commit the transaction
COMMIT TRAN TranStart;
-- See what records were finally committed.
SELECT TOP 2 OrderID
FROM MyTranTest
ORDER BY OrderID DESC;
-- Clean up after ourselves
DROP TABLE MyTranTest;


 

 

三、锁

锁定是 Microsoft SQL Server数据库引擎用来同步多个用户同时对同一个数据块的访问的一种机制。

在事务获取数据块当前状态的依赖关系(比如通过读取或修改数据)之前,它必须保护自己不受其他事务对同一数据进行修改的影响。事务通过请求锁定数据块来达到此目的。锁有多种模式,如共享或独占。锁模式定义了事务对数据所拥有的依赖关系级别。如果某个事务已获得特定数据的锁,则其他事务不能获得会与该锁模式发生冲突的锁。如果事务请求的锁模式与已授予同一数据的锁发生冲突,则数据库引擎实例将暂停事务请求直到第一个锁释放。

 

四大冲突问题

1、脏读

某个事务读取的数据是另一个事务正在处理的数据。而另一个事务可能会回滚,造成第一个事务读取的数据是错误的。

 

2、不可重复读

在一个事务里两次读入数据,但另一个事务已经更改了第一个事务涉及到的数据,造成第一个事务读入旧数据。

 

3、幻读

幻读是指当事务不是独立执行时发生的一种现象。例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入一行新数据。那么,以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行,就好象发生了幻觉一样。

 

4、更新丢失

多个事务同时读取某一数据,一个事务成功处理好了数据,被另一个事务写回原值,造成第一个事务更新丢失。

 

锁模式:

1、共享锁

共享锁(S 锁)允许并发事务在封闭式并发控制下读取 (SELECT)资源。有关详细信息,请参阅 并发控制的类型。资源上存在共享锁(S锁)时,任何其他事务都不能修改数据。读取操作一完成,就立即释放资源上的共享锁(S锁),除非将事务隔离级别设置为可重复读或更高级别,或者在事务持续时间内用锁定提示保留共享锁(S锁)。

2、更新锁(U锁)

更新锁在共享锁和排他锁的杂交。更新锁意味着在做一个更新时,一个共享锁在扫描完成符合条件的数据后可能会转化成排他锁。

 

这里面有两个步骤:

1) 扫描获取Where条件时。这部分是一个更新查询,此时是一个更新锁。

2) 如果将执行写入更新。此时该锁升级到排他锁。否则,该锁转变成共享锁。

 

更新锁可以防止常见的死锁。

 

3、排他锁

排他锁(X 锁)可以防止并发事务对资源进行访问。排他锁不与其他任何锁兼容。使用排他锁(X锁)时,任何其他事务都无法修改数据;仅在使用 NOLOCK提示或未提交读隔离级别时才会进行读取操作。

 

事务隔离级别 

SQL Server通过SET TRANSACTION ISOLATION LEVEL语句设置事务隔离级别: 

SET TRANSACTION ISOLATION LEVEL

    { READ UNCOMMITTED

    | READ COMMITTED

    | REPEATABLE READ

    | SNAPSHOT

    | SERIALIZABLE

    }

[ ; ]

 

Read Committed是SQL Server和Oracle的预设隔离等级。

 

 

1、READ UNCOMMITTED

Read UnCommitted事务可以读取事务已修改,但未提交的的记录。

Read UnCommitted事务会产生脏读(Dirty Read)。

Read UnCommitted事务与select语句加nolock的效果一样,它是所有隔离级别中限制最少的。

 

2、READ COMMITTED

一旦创建共享锁的语句执行完成,该锁顶便释放。

Read Committed是SQL Server的预设隔离等级。

Read Committed只可以防止脏读。

--先创建表: 
CREATE TABLE tb(id int,val int) 
INSERT tb VALUES(1,10) 
INSERT tb VALUES(2,20) 
  
然后在连接1中,执行: 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION
    SELECT * FROM tb;  --这个SELECT结束后,就会释放掉共享锁 
      
    WAITFOR DELAY '00:00:05'  --模拟事务处理,等待5秒 
      
    SELECT * FROM tb;   --再次SELECT tb表 
ROLLBACK  --回滚事务 
  
在连接2中,执行 
UPDATE tb SET
    val = val + 10 
WHERE id = 2; 
  
-------- 
回到连接1中.可以看到.两次SELECT的结果是不同的. 
因为在默认的READ COMMITTED隔离级别下,SELECT完了.就会马上释放掉共享锁. 


 

 

3、 REPEATABLE READ

REPEATABLE READ事务不会产生脏读,并且在事务完成之前,任何其它事务都不能修改目前事务已读取的记录。

其它事务仍可以插入新记录,但必须符合当前事务的搜索条件——这意味着当前事务重新查询记录时,会产生幻读(Phantom Read)。

 

4、 SERIALIZABLE

SERIALIZABLE可以防止除更新丢失外所有的一致性问题,即:

 

1.语句无法读取其它事务已修改但未提交的记录。

2.在当前事务完成之前,其它事务不能修改目前事务已读取的记录。

3.在当前事务完成之前,其它事务所插入的新记录,其索引键值不能在当前事务的任何语句所读取的索引键范围中。

 

5、 SNAPSHOT

Snapshot事务中任何语句所读取的记录,都是事务启动时的数据。

这相当于事务启动时,数据库为事务生成了一份专用“快照”。在当前事务中看到不其它事务在当前事务启动之后所进行的数据修改。

Snapshot事务不会读取记录时要求锁定,读取记录的Snapshot事务不会锁住其它事务写入记录,写入记录的事务也不会锁住Snapshot事务读取数据。

 

四、悲观锁和乐观锁

1、悲观锁

悲观锁是指假设并发更新冲突会发生,所以不管冲突是否真的发生,都会使用锁机制。
悲观锁会完成以下功能:锁住读取的记录,防止其它事务读取和更新这些记录。其它事务会一直阻塞,直到这个事务结束.
悲观锁是在使用了数据库的事务隔离功能的基础上,独享占用的资源,以此保证读取数据一致性,避免修改丢失。

悲观锁可以使用Repeatable Read事务,它完全满足悲观锁的要求。


2、乐观锁

乐观锁不会锁住任何东西,也就是说,它不依赖数据库的事务机制,乐观锁完全是应用系统层面的东西。

如果使用乐观锁,那么数据库就必须加版本字段,否则就只能比较所有字段,但因为浮点类型不能比较,所以实际上没有版本字段是不可行的。

 

3、死锁

当二或多个工作各自具有某个资源的锁定,但其它工作尝试要锁定此资源,而造成工作永久封锁彼此时,会发生死锁。例如:
1.           事务 A取得数据列 1 的共享锁定。
2.           事务B取得数据列 2 的共享锁定。
3.           事务A现在要求数据列 2 的独占锁定,但会被封锁直到事务B 完成并释出对数据列 2 的共享锁定为止。
4.           事务B现在要求数据列 1 的独占锁定,但会被封锁直到事务A 完成并释出对数据列 1 的共享锁定为止。
等到事务B 完成后,事务A 才能完成,但事务B被事务A 封锁了。这个状况也称为「循环相依性」(Cyclic Dependency)。事务A相依于事务B,并且事务B也因为相依于事务A 而封闭了这个循环。

 

SQL Server遇到死锁时会自动杀死其中一个事务,而另一个事务会正常结束(提交或回滚)。

SQL Server对杀死的连接返回错误代码是1205,异常提示是:

Your transaction (process ID #52) was deadlocked on {lock | communication buffer | thRead} resources with another process and has been chosen as the deadlock victim. Rerun your transaction.

 

例如以下操作就会产生死锁,两个连接互相阻塞对方的update。

连接1: 
begin tran 
  
select * from customers  
  
update customers set CompanyName = CompanyName 
  
   
waitfor delay '00:00:05'
  
   
select * from Employees 
–因为Employees被连接2锁住了,所以这里会阻塞。 
update Employees set LastName = LastName  
commit tran 
   
连接2: 
begin tran 
  
select * from Employees 
  
update Employees set LastName = LastName 
  
   
waitfor delay '00:00:05'
  
   
select * from customers  
--因为customers被连接1锁住了,所以这里会阻塞。 
update customers set CompanyName = CompanyName 
commit tran


 

 

4、如何避免死锁

(1).按同一顺序访问对象。(注:避免出现循环)
(2).避免事务中的用户交互。(注:减少持有资源的时间,较少锁竞争)
(3).保持事务简短并处于一个批处理中。(注:同(2),减少持有资源的时间)
(4).使用较低的隔离级别。(注:使用较低的隔离级别(例如已提交读)比使用较高的隔离级别(例如可序列化)持有共享锁的时间更短,减少锁竞争)
(5).使用基于行版本控制的隔离级别:2005中支持快照事务隔离和指定READ_COMMITTED隔离级别的事务使用行版本控制,可以将读与写操作之间发生的死锁几率降至最低:
SET ALLOW_SNAPSHOT_ISOLATION ON --事务可以指定 SNAPSHOT事务隔离级别;
SET READ_COMMITTED_SNAPSHOT ON  --指定 READ_COMMITTED隔离级别的事务将使用行版本控制而不是锁定。默认情况下(没有开启此选项,没有加with nolock提示),SELECT语句会对请求的资源加S锁(共享锁);而开启了此选项后,SELECT不会对请求的资源加S锁。
注意:设置 READ_COMMITTED_SNAPSHOT选项时,数据库中只允许存在执行 ALTER DATABASE命令的连接。在 ALTER DATABASE完成之前,数据库中决不能有其他打开的连接。数据库不必一定要处于单用户模式中。
(6).使用绑定连接。(注:绑定会话有利于在同一台服务器上的多个会话之间协调操作。绑定会话允许一个或多个会话共享相同的事务和锁(但每个回话保留其自己的事务隔离级别),并可以使用同一数据,而不会有锁冲突。可以从同一个应用程序内的多个会话中创建绑定会话,也可以从包含不同会话的多个应用程序中创建绑定会话。在一个会话中开启事务(begin tran)后,调用exec sp_getbindtoken @Token out;来取得Token,然后传入另一个会话并执行EXEC sp_bindsession @Token来进行绑定(最后的示例中演示了绑定连接)。

 

五、.NET中使用指定的隔离级别开始一个事务

BeginTransaction函数有多个重载,其中一个可以指定事务的隔离级别

BeginTransaction(IsolationLevel):以指定的隔离级别启动数据库事务。

 

注意:在事务提交或回滚后,该事务的隔离级别为自动提交模式的所有后续命令保存 ( SQL Server默认设置) 中。这样将产生意外结果,例如 REPEATABLE READ隔离级别持续并阻止其他用户使用某一行。若要重置隔离级别为默认值 (读取操作),执行 Transact-SQL 设置事务隔离级别读作的语句或调用 SqlConnection.BeginTransaction 后面紧跟 SqlTransaction.Commit

 

作者:zztfj 发表于2012-8-18 0:15:26 原文链接
阅读:17 评论:0 查看评论

相关 [数据库] 推荐:

数据库sharding

- - 数据库 - ITeye博客
当团队决定自行实现sharding的时候,DAO层可能是嵌入sharding逻辑的首选位置,因为在这个层面上,每一个DAO的方法都明确地知道需要访问的数据表以及查询参数,借助这些信息可以直接定位到目标shard上,而不必像框架那样需要对SQL进行解析然后再依据配置的规则进行路由. 另一个优势是不会受ORM框架的制约.

数据库索引

- - CSDN博客推荐文章
索引是由用户创建的、能够被修改和删除的、实际存储于数据库中的物理存在;创建索引的目的是使用户能够从整体内容直接查找到某个特定部分的内容. 一般来说,索引能够提高查询,但是会增加额外的空间消耗,并且降低删除、插入和修改速度. 1.聚集索引:表数据按照索引的顺序来存储的. 2.非聚集索引:表数据存储顺序与索引顺序无关.

数据库事务

- - 数据库 - ITeye博客
事务传播发生在类似以下情形:. 假设methodB的配置是:. 如果methodA在事务里,那么methodB也在这个事务中运行. 如果methodA不在事务里,那么methodB重新建立一个事务运行. 如果methodA在事务里,那么methodB也在这个事务中运行. 如果methodA不在是事务里,那么methodB在非事务中运行.

数据库优化

- - 数据库 - ITeye博客
程序运行效率,优化应用程序,在SP编写过程中应该注意以下几点: . a) SQL的使用规范: .   i.尽量避免大事务操作,慎用holdlock子句,提高系统并发能力.   ii.尽量避免反复访问同一张或几张表,尤其是数据量较大的表,可以考虑先根据条件提取数据到临时表中,然后再做连接.   iii.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该改写;如果使用了游标,就要尽量避免在游标循环中再进行表连接的操作.

数据库调优

- - 数据库 - ITeye博客
1、1、调整数据结构的设计. 这一部分在开发信息系统之前完成,程序员需要考虑是否使用ORACLE数据库的分区功能,对于经常访问的数据库表是否需要建立索引等. 这一部分也是在开发信息系统之前完成,程序员在这一步需要考虑应用程序使用什么样的体系结构,是使用传统的Client/Server两层体系结构,还是使用Browser/Web/Database的三层体系结构.

MySQL数据库的修复

- Xin - 博客园-首页原创精华区
找到mysql的安装目录的bin/myisamchk工具,在命令行中输入:. 然后myisamchk 工具会帮助你恢复数据表的索引. 好象也不用重新启动mysql,问题就解决了. 当你试图修复一个被破坏的表的问题时,有三种修复类型. 如果你得到一个错误信息指出一个临时文件不能建立,删除信息所指出的文件并再试一次--这通常是上一次修复操作遗留下来的.

Oracle 发布 NoSQL 数据库

- 冷月 - 博客园新闻频道
  Oracle 作为全球最大的关系型数据库提供商,在其产品链条中,也加入了 NoSQL 数据库这一环,而且这个新的数据库名字很霸气,就叫 NoSQL Database,想起了当年新浪微博更换 weibo.com 域名之时的一个笑话:. 原来有三家人做面包,张三家的面包叫三张牌面包,李四家的牌子叫李四牌面包,王五家出品的是王五牌面包,而突然有一天,张三家的面包改名了,叫面包牌面包.

WineHQ 数据库泄漏

- gnawux - LinuxTOY
运行于 *Nix 之上的开源跨平台 Win32 API 兼容层 WineHQ 的 AppDB 和 Bugzilla 数据库被黑客攻击. CodeWeavers CEO Jeremy 在信中提到黑客利用某种方式获取了 WineHQ 的 AppDB 和 Bugzilla 的访问,并且下载了完整数据库文件.

数据库复制-Goldengate

- - 人月神话的BLOG
参考: http://wenku.baidu.com/view/4fd7ea22bcd126fff7050b5d.html. GoldenGate TDM(交易数据管理)软件是一种基于日志的结构化数据复制软件,它通过解析源数据库在线日志或归档日志获得数据的增删改变化,再将这些变化应用到目标数据库,实现源数据库与目标数据库同步、双活.