文档章节

sql事务和锁

独坐苔痕但观罗敷
 独坐苔痕但观罗敷
发布于 2017/02/06 15:40
字数 2373
阅读 26
收藏 0

一:事务

事务具有原子性,一致性,隔离性,持久性:

  1. 原子性:事务必须是一个自动工作的单元,要么全部执行,要么全部不执行。
  2. 一致性:事务结束的时候,所有的内部数据都是正确的。
  3. 隔离性:并发多个事务时,各个事务不干涉内部数据,处理的都是另外一个事务处理之前或之后的数据。
  4. 持久性:事务提交之后,数据是永久性的,不可再回滚。

在SQL Server中事务被分为3类常见的事务:

  1. 自动提交事务:是SQL Server默认的一种事务模式,每条Sql语句都被看成一个事务进行处理,你应该没有见过,一条Update 修改2个字段的语句,只修该了1个字段而另外一个字段没有修改。。
  2. 显式事务:T-sql标明,由Begin Transaction开启事务开始,由Commit Transaction 提交事务、Rollback Transaction 回滚事务结束。
  3. 隐式事务:使用Set IMPLICIT_TRANSACTIONS ON 将将隐式事务模式打开,不用Begin Transaction开启事务,当一个事务结束,这个模式会自动启用下一个事务,只用Commit Transaction 提交事务、Rollback Transaction 回滚事务即可。

显示事务:

  1. Begin Transaction:标记事务开始。
  2. Commit Transaction:事务已经成功执行,数据已经处理妥当。
  3. Rollback Transaction:数据处理过程中出错,回滚到没有处理之前的数据状态,或回滚到事务内部的保存点。
  4. Save Transaction:事务内部设置的保存点,就是事务可以不全部回滚,只回滚到这里,保证事务内部不出错的前提下。

例:

---开启事务
begin tran
--错误扑捉机制,看好啦,这里也有的。并且可以嵌套。
begin try  
   --语句正确
   insert into lives (Eat,Play,Numb) values ('猪肉','足球',1)
   --Numb为int类型,出错
   insert into lives (Eat,Play,Numb) values ('猪肉','足球','abc')
   --语句正确
   insert into lives (Eat,Play,Numb) values ('狗肉','篮球',2)
end try
begin catch
   select Error_number() as ErrorNumber,  --错误代码
          Error_severity() as ErrorSeverity,  --错误严重级别,级别小于10 try catch 捕获不到
          Error_state() as ErrorState ,  --错误状态码
          Error_Procedure() as ErrorProcedure , --出现错误的存储过程或触发器的名称。
          Error_line() as ErrorLine,  --发生错误的行号
          Error_message() as ErrorMessage  --错误的具体信息
   if(@@trancount>0) --全局变量@@trancount,事务开启此值+1,他用来判断是有开启事务
      rollback tran  ---由于出错,这里回滚到开始,第一条语句也没有插入成功。
end catch
if(@@trancount>0)
commit tran  --如果成功Lives表中,将会有3条数据。

--表本身为空表,ID ,Numb为int 类型,其它为nvarchar类型
select * from lives

例2:使用set xact_abort(设置 xact_abort on/off , 指定是否回滚当前事务,为on时如果当前sql出错,回滚整个事务,为off时如果sql出错回滚当前sql语句,其它语句照常运行读写数据库。 需要注意的时:xact_abort只对运行时出现的错误有用,如果sql语句存在编译时错误,那么他就失灵啦。)。

delete lives  --清空数据
set xact_abort off
begin tran 
    --语句正确
   insert into lives (Eat,Play,Numb) values ('猪肉','足球',1)   
   --Numb为int类型,出错,如果1234..那个大数据换成'132dsaf' xact_abort将失效
   insert into lives (Eat,Play,Numb) values ('猪肉','足球',12345646879783213)
   --语句正确
   insert into lives (Eat,Play,Numb) values ('狗肉','篮球',3)
commit tran
select * from lives

二:锁

在多用户都用事务同时访问同一个数据资源的情况下,就会造成以下几种数据错误。

  • 更新丢失:多个用户同时对一个数据资源进行更新,必定会产生被覆盖的数据,造成数据读写异常。
  • 不可重复读:如果一个用户在一个事务中多次读取一条数据,而另外一个用户则同时更新啦这条数据,造成第一个用户多次读取数据不一致。
  • 脏读:第一个事务读取第二个事务正在更新的数据表,如果第二个事务还没有更新完成,那么第一个事务读取的数据将是一半为更新过的,一半还没更新过的数据,这样的数据毫无意义。
  • 幻读:第一个事务读取一个结果集后,第二个事务,对这个结果集经行增删操作,然而第一个事务中再次对这个结果集进行查询时,数据发现丢失或新增。

然而锁定,就是为解决这些问题所生的,他的存在使得一个事务对他自己的数据块进行操作的时候,而另外一个事务则不能插足这些数据块。这就是所谓的锁定。

锁定从数据库系统的角度大致可以分为6种:

  • 共享锁(S):还可以叫他读锁。可以并发读取数据,但不能修改数据。也就是说当数据资源上存在共享锁的时候,所有的事务都不能对这个资源进行修改,直到数据读取完成,共享锁释放。
  • 排它锁(X):还可以叫他独占锁、写锁。就是如果你对数据资源进行增删改操作时,不允许其它任何事务操作这块资源,直到排它锁被释放,防止同时对同一资源进行多重操作。
  • 更新锁(U):防止出现死锁的锁模式,两个事务对一个数据资源进行先读取在修改的情况下,使用共享锁和排它锁有时会出现死锁现象,而使用更新锁则可以避免死锁的出现。资源的更新锁一次只能分配给一个事务,如果需要对资源进行修改,更新锁会变成排他锁,否则变为共享锁。
  • 意向锁:SQL Server需要在层次结构中的底层资源上(如行,列)获取共享锁,排它锁,更新锁。例如表级放置了意向共享锁,就表示事务要对表的页或行上使用共享锁。在表的某一行上上放置意向锁,可以防止其它事务获取其它不兼容的的锁。意向锁可以提高性能,因为数据引擎不需要检测资源的每一列每一行,就能判断是否可以获取到该资源的兼容锁。意向锁包括三种类型:意向共享锁(IS),意向排他锁(IX),意向排他共享锁(SIX)。
  • 架构锁:防止修改表结构时,并发访问的锁。
  • 大容量更新锁:允许多个线程将大容量数据并发的插入到同一个表中,在加载的同时,不允许其它进程访问该表。

这些锁之间的相互兼容性,也就是,是否可以同时存在。 

 

现有的授权模式

 

 

 

 

 

请求的模式

IS

S

U

IX

SIX

X

意向共享 (IS)

共享 (S)

更新 (U)

意向排他 (IX)

意向排他共享 (SIX)

排他 (X)

1)排它锁

--在第一个连接中执行以下语句 
begin tran 
update table1 
set A='aa' 
where B='b2' 
waitfor delay '00:00:30' --等待30秒 
commit tran 
--在第二个连接中执行以下语句 
begin tran 
select * from table1 
where B='b2' 
commit tran 

结果:若同时执行上述两个语句,则select查询必须等待update执行完毕才能执行即要等待30秒

2)共享锁 

--在第一个连接中执行以下语句 
begin tran 
select * from table1 holdlock -holdlock人为加锁 
where B='b2' 
waitfor delay '00:00:30' --等待30秒 
commit tran 
 
--在第二个连接中执行以下语句 
begin tran 
select A,C from table1 
where B='b2' 
update table1 
set A='aa' 
where B='b2' 
commit tran 

结果:若同时执行上述两个语句,则第二个连接中的select查询可以执行而update必须等待第一个事务释放共享锁转为排它锁后才能执行 即要等待30秒 

3)死锁 

--在第一个连接中执行以下语句 
begin tran 
update table1 
set A='aa' 
where B='b2' 
waitfor delay '00:00:30' 
update table2 
set D='d5' 
where E='e1' 
commit tran 
 
--在第二个连接中执行以下语句 
begin tran 
update table2 
set D='d5' 
where E='e1' 
waitfor delay '00:00:10' 
update table1 
set A='aa' 
where B='b2' 
commit tran 

结果:同时执行,系统会检测出死锁,并中止进程.

用到的表结构:

table1(A,B,C) 

A  B   C 

a1 b1 c1 

a2 b2 c2 

a3 b3 c3 

table2(D,E) 

D   E 

d1 e1 

d2 e2 

其他补充:

  • HOLDLOCK 持有共享锁,直到整个事务完成,应该在被锁对象不需要时立即释放,等于SERIALIZABLE事务隔离级别 
  • NOLOCK 语句执行时不发出共享锁,允许脏读 ,等于 READ UNCOMMITTED事务隔离级别 
  • PAGLOCK 在使用一个表锁的地方用多个页锁 
  • READPAST 让sql server跳过任何锁定行,执行事务,适用于READ UNCOMMITTED事务隔离级别只跳过RID锁,不跳过页,区域和表锁 
  • ROWLOCK 强制使用行锁 
  • TABLOCKX 强制使用独占表级锁,这个锁在事务期间阻止任何其他事务使用这个表 
  • UPLOCK 强制在读表时使用更新而不用共享锁 

引用:

  1. http://bbs.csdn.net/topics/340000063
  2. http://www.cnblogs.com/knowledgesea/p/3714417.html

 

© 著作权归作者所有

上一篇: Web app meta设置
独坐苔痕但观罗敷
粉丝 1
博文 45
码字总数 13549
作品 0
宜宾
程序员
私信 提问
MySQL设置锁、事务隔离级别的常用命令

锁 1、查看表锁, Tablelockswaited较高表明锁争用比较严重 [sql] view plain copy show status like 'table%' 2、查看行锁,Innodbrowlockwaits和InnoDBrowlocktime_avg较高表明锁争用比较严......

kim_o
2018/06/09
42
0
SQL Server常用命令

查看被锁表: select requestsessionid spid,OBJECTNAME(resourceassociatedentityid) tableName from sys.dmtranlocks where resource_type='OBJECT' spid 锁表进程 tableName 被锁表名 解锁......

straymouse
2017/05/06
0
0
SQL Server 中的锁与并发

原文出处:nogos 并发可以定义为多个进程同时访问或修改共享数据的能力。处于活动状态而互不干涉的并发用户进程的数量越多,数据库系统的并发性就越好。当一个正在修改数据的进程阻止了其他进...

nogos
2016/08/10
0
0
SQL Server中锁与事务隔离级别

SQL Server中的锁分为两类: 共享锁 排它锁 锁的兼容性:事务间锁的相互影响称为锁的兼容性。 SQL Server中可以锁定的资源包括:RID或键(行)、页、对象(如表)、数据库等等。 在试图修改数...

雪飞鸿
2018/07/15
0
0
T-SQL:事务锁下的并发处理(十五)

1.事务 在sql servce 中 事务是一个工作单元 可能包含查询和修改数据以及修改数据定义等多个活动 也可以显示或隐式定义事务边界 显示定义事务 BEGIN TRAN 开始 如果要提交事务 使用 COMMIT T...

薛定谔家的猫
2018/08/19
0
0

没有更多内容

加载失败,请刷新页面

加载更多

消息中间件——RabbitMQ的高级特性

前言 前面我们介绍了RabbitMQ的安装、各大消息中间件的对比、AMQP核心概念、管控台的使用、快速入门RabbitMQ。本章将介绍RabbitMQ的高级特性。分两篇(上/下)进行介绍。 消息如何保障100%的...

Java架构师ya七
49分钟前
8
0
如何编写高质量的 JS 函数(1) -- 敲山震虎篇

本文首发于 vivo互联网技术 微信公众号 链接:https://mp.weixin.qq.com/s/7lCK9cHmunvYlbm7Xi7JxQ 作者:杨昆 一千个读者,有一千个哈姆雷特。 此系列文章将会从函数的执行机制、鲁棒性、函...

vivo互联网技术
今天
7
0
学会这5个Excel技巧,让你拒绝加班

在网上,随处都可以看到Excel技巧,估计已看腻了吧?但下面5个Excel技巧会让你相见恨晚。关键的是它们个个还很实用 图一 技巧1:快速删除边框 有时当我们处理数据需要去掉边框,按Ctrl+Shif...

干货趣分享
今天
11
0
JS基础-该如何理解原型、原型链?

JS的原型、原型链一直是比较难理解的内容,不少初学者甚至有一定经验的老鸟都不一定能完全说清楚,更多的"很可能"是一知半解,而这部分内容又是JS的核心内容,想要技术进阶的话肯定不能对这个...

OBKoro1
今天
11
0
高防CDN的出现是为了解决网站的哪些问题?

高防CDN是为了更好的服务网络而出现的,是通过高防DNS来实现的。高防CDN是通过智能化的系统判断来路,再反馈给用户,可以减轻用户使用过程的复杂程度。通过智能DNS解析,能让网站访问者连接到...

云漫网络Ruan
今天
17
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部