【SQL Server学习笔记】事务、锁定、阻塞、死锁
来源:互联网 发布:贵州移动集团软件下载 编辑:程序博客网 时间:2024/06/04 18:53
下面是一个网友提出的非常好的问题:http://bbs.csdn.net/topics/390626958
MS SQLSERVER2008的事务,锁,并发的问题
学习了SQL SERVER2008关于事务的讲解,关于锁的讲解,关于并发的理解。但是仍然有一些问题,不是很理解,希望大牛们给予知道。
问题1:并发访问的基础是多个外部进程,可以同时操作同一张表,或者操作同一个记录,总之是相同对象。这样带来的问题是,1、写覆盖,2、不可重复读,3、脏数据,4、幻数据。但是引入了锁以后,为什么锁就解决了上述问题哪?
问题2:比如说写覆盖,在有了共享锁以后,进程A,读取数据时,进程B不能够修改数据,那么进程A读取之后,进程B就可以修改数据了,但是发现进程A其实读到的,不还是错误数据吗?锁也没有解决问题呀?
问题3:关于死锁的问题,是不是说,死锁的发生,是因为两个进程在操作数据时,编写的数据事务有错误,需要修改数据处理的事务,从而解决死锁的问题?还是说,死锁与使用编程的方式实现同步是两个层面的概念?编程解决死锁的问题,是用户在内存中共享数据的处理情况决定的,而数据库的同步是不需要编程的?
下面是我的回答:
1、为什么说,引入了锁之后,就能解决上面的问题呢?其实说到底,这个锁就是用来解决,当有多个并发进程访问同一个数据时,保持数据一致性的。此外,还有一个非常重要,但很多人都忽视的概念,那就是隔离级别的概念,这个隔离级别控制了,进程间相互影响的程度。
所以上面的4个问题,不仅需要锁,还是需要隔离级别来控制,才能得到解决。
sql server的隔离级别有4中:
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串行化读
比如,在sql server中,任何一个进程在访问数据时,都会按照隔离级别来访问数据。
默认的隔离级别是read committed,也就是说比如,要访问一条数据,根据访问的性质,获取相应的锁。
如果有个进程A要读取数据,那么就会获取共享锁S,这个时候如果有另一个进程B,需要修改这条数据,那么他按照默认的隔离级别,就为了要修改这条数据,必须要申请一个独占锁X,于是他向sql server 申请x锁,sql server发现有一个进程获取了这条数据的S锁,正在读取数据,还有没读取完成,于是sql server 没有同意进程B的申请,于是B开始处于阻塞状态,他是被A进程阻塞的。
其实反过来也是一样的,如果A已经在修改数据,而B要读取数据,那么B会被A阻塞,这样就使得当有多个进程访问数据时,能实现数据的一致性,读的是一致性的数据,写的也是一致性的数据。
但是如果这个时候,用户修改了隔离级别为read uncommitted,那么进程就会读未提交的数据。
比如A进程修改了数据,还没有提交,B进程的隔离级别为read uncommitted,于是他可以不向sql server 申请共享锁,那么他就直接取内存中读取A已经修改的数据,而这个数据由于还没有提交,所以他读取到的数据是脏数据。
那么同样的,在另外2种隔离级别下,就能实现更加严格的隔离级别,也就是进程间的相互影响更小了。
2、数据库通过锁来实现数据的一致性。你说A读取完了数据,B就能修改了数据,等B修改了数据,A还是不能读取数据,只有等B修改且提交之后,A才能读取数据,这个时候A读取的就是一致的数据。
所谓一致的数据,是只状态,就是这个数据是处于一个确定的状态,而不是别人正在修改的数据,正改到一半呢,还没有全部改完,因为这个时候,如果别人发现,改错了,那么他就会回滚rollback。
这里讲另一个隔离级别,就是可重复读,很有意思。
比如现在有一个进程A,开启了一个事务,采用默认的read committed隔离级别,他在这个事务中有3个语句,第一个是读xx记录,第二个是修改yy记录,第三个还是读xx记录。
读第一个记录时值为1,接下来修改yy记录,修改完了再次读xx记录,发现值变成了2.
这是怎么回事呢?
其实在进程A修改yy记录时,有另一个进程B,去修改了xx记录,把只修改为2了。
由于是read committed隔离级别,在sql server中规定,在事务中读取数据时需要加共享锁S,但读取完成后,会立即释放共享S锁,于是B进程修改xx记录时,就顺利申请了X锁,修改完成后,提交了。
当A再次读取的时候,读取到了2,确实读取的也是已经提交的数据。
通过把隔离级别设置为可重复读,那么在一个事务中,要读取一条记录,所需要申请的共享锁S,要在事务提交或回滚的时候,也就是在事务结束的时候,才会被释放,这样就有效的阻止了进程B对xx记录的修改,那么对进程A来说,第一次和第二次读到的就都是1了。
3、死锁是一个普遍的问题,不是只有数据库才有死锁。
如果上过操作系统的课,都会学过里面的死锁的概念,任何程序,只要是需要访问资源的,都有可能产生死锁。
死锁的产生,主要是和不当的编写程序有关。
一种死锁的情形是,简单来说就是,A进程有了资源1,A又想占有资源2,而B进程有了资源e,但还想用资源1,那么就形成了一个循环,就像三角恋一样,没结果,死循环了就是。
当然,在操作系统和数据库中,都有死锁检测程序,比如在sql server 中就有一个“锁监视器”的线程,来检测死锁,如果一旦发现死锁,就会选择其中一个回滚代价较小的进程,进行回滚,这就解决了死锁问题。
在sql server内部有一个线程:Lock monitor,他的等待类型是:REQUEST_FOR_DEADLOCK_SEARCH,也就是死锁搜索请求,因为这个请求每个一段时间,会自动触发,就像一个定时任务一样
1、事务
隐式事务
/*==================================================================当以create,drop, fetch,open, revoke,grand, alter table,select,insert,delete,update,truncate table语句首先执行的时候,SQL Server会话自动打开一个新的事务,如果在会话中激活了隐式事务模式,那么这个事务会一直保持打开状态,直到rollback或commit语句这个事务才结束,如果忘记提交事务,那么在相应的隔离级别下,事务占用的锁可能不会释放,因此尽量不要用隐式事务。====================================================================*/--会话1set implicit_transactions onupdate tset v = 'ext12'set implicit_transactions offselect @@TRANCOUNT --输出:1,说明事务没有释放 --占用的X独占锁不会释放,会阻塞其他会话
--会话2,被会话1阻塞住了,不会返回任何记录select *from t在会话1中执行commit来提交事务,那么会话2马上就会返回记录了。
现在把两个会话的执行顺序调换一下:
--会话1set implicit_transactions on --打开了隐式事务select *from tset implicit_transactions offselect @@TRANCOUNT --输入:1,说明这个会话中的事务也没有提交
--会话2,会话2没有被会话1阻塞,--之所以这样是因为会话的默认隔离级别是read committed,--会话1中的事务虽然没有提交,但是select语句在这种隔离级别下,--运行完就会释放占用的S共享锁,所以不会阻塞写操作update tset v = 'ext'显示数据库最早的活动事务
/*==============================================================如果事务在数据库中始终打开,有可能会阻塞其他进程的操作,为什么是有可能而不是一定呢,原因就是:在默认隔离级别下的select语句查询到数据后就会立即释放共享锁。另外,日志备份也只会截断不活动事务的那部分日志,所以活动的事务会导致日志数据越来越多。为了找到没有提交的事务,可以用下面的命令显示某个数据库最早的活动事务.不过有个例外,就是下面的命令不会返回:不占用锁资源的未提交事务================================================================*/begin tran --开始显示事务select *from t --运行后立即释放共享锁select @@TRANCOUNT --输入:1,说明没有提交事务dbcc opentran('wc') --显示数据库最早的活动事务, --但是这儿显示"没有处于打开状态的活动事务"
通过会话来查询事务信息
--由于上面未提交事务中的select语句在默认的隔离级别下执行后自动释放了共享锁,--所以dbcc opentran命令并没有返回这个活动事务,--不过下面的视图解决了这个问题,可以找到所有活动事务。--找到活动事务select session_id, --session_id与transaction_id的对应关系 transaction_id, is_user_transaction, is_localfrom sys.dm_tran_session_transactions --会话中的事务,识别所有打开的事务 where is_user_transaction =1--找到活动事务对应的执行语句select c.session_id, --session_id与connection_id的对应关系 c.connection_id, c.most_recent_sql_handle, s.text from sys.dm_exec_connections c --执行连接,最近执行的查询信息cross apply sys.dm_exec_sql_text(c.most_recent_sql_handle) swhere c.session_id = 361 --活动事务的具体信息select t.transaction_id, t.name, --这里显示user_transaction t.transaction_begin_time, case t.transaction_type --事务类型 when 1 then '读/写事务' when 2 then '只读事务' when 3 then '系统事务' when 4 then '分布式事务' end 'transaction type', case t.transaction_state when 0 then '事务尚未完全初始化' when 1 then '事务已初始化但尚未启动' when 2 then '事务处于活动状态' when 3 then '事务已结束。该状态用于只读事务' when 4 then '已对分布式事务启动提交进程' when 5 then '事务处于准备就绪状态且等待解析' when 6 then '事务已提交' when 7 then '事务正在被回滚' when 8 then '事务已回滚' end 'transaction state'from sys.dm_tran_active_transactions t --活动的事务where transaction_id = 150764485
2、锁定
当一个用户要读取另一个用户正在修改的数据,或者一个用户正在修改另一个用户正在读取的数据,或者一个用户要修改另一个用户正在修改的数据,就会出现并发问题。锁定能防止并发问题。
资源的锁定方式称为锁定模式,SQL Server中的锁定模式:共享锁,意向锁,更新锁,排他锁,架构稳定锁,架构修改锁,大批量更新锁,键范围锁。不是所有锁模式都是兼容的,如:一个加了排他锁的资源不能再加其他锁,其他事务必须等待,直到释放排他锁。
可以锁定SQL Server中的各类对象,可以锁定的资源在粒度上差异很大,从细粒度(行、键)到粗粒度(数据库)。细粒度的锁允许用户能查询那些未被锁定的行,并发性更高,但是需要更多的锁资源(每个被锁定的行都需要一个锁资源);粗粒度的锁降低了并发性,但需要的锁资源很少。
在SQL Server中可锁定的资源:
DB(数据库)Metadata(系统元数据)Object(数据库对象:视图,函数,存储过程,触发器)Table(表)Hobt(堆或B树)Allocation Unit(按照数据的类型(数据,行溢出、大对象)分组的相关页面)Extent(8个8KB的页面)Page(8KB数据页面)Rid(行标示符对应一个堆表的行)Key(键范围上的锁、B树中的键)FileApplication查看锁的活动
select resource_type, --资源类型 resource_database_id, --资源所在的数据库id resource_associated_entity_id, --数据库中与资源相关联的实体的 ID。 --该值可以是对象ID、Hobt ID 或分配单元 ID, --具体视资源类型而定 object_name(resource_associated_entity_id,resource_database_id), resource_lock_partition, --已分区锁资源的锁分区ID。对于未分区锁资源值为 0 resource_description, --资源的说明,其中只包含从其他资源列中无法获取的信息 request_session_id, --请求资源的会话 request_type, --请求类型,该值为 LOCK request_mode, --请求的模式,对于已授予的请求,为已授予模式, --对于等待请求,为正在请求的模式(锁定模式) request_status --请求的当前状态, --可能值为 GRANTED、CONVERT 或 WAIT from sys.dm_tran_locksWHERE request_session_id = 361控制表的锁升级
每个锁都会消耗内存资源,当锁的数量增加时,那么所需要的内存就会增加,而系统内可用的内存就会减少。如果锁占用的内存比率超过一个阀值,SQL Server会将细粒度锁(行锁)升级为粗粒度锁(表锁),这个过程就是锁升级。
锁升级的优点是可以减少锁的数量,相应的减少内存的使用量,而缺点是由于锁住了更大的资源,所以会导致阻塞,降低并发性。
--默认值,不管是不是分区表,会在表级别启用锁升级ALTER TABLE tSET (lock_escalation = TABLE)--当表升级时,如果表已经分区,会在分区级别启用锁升级ALTER TABLE tSET (lock_escalation = auto)--在表级别禁用锁升级,如果用了TabLock提示或在Serializable隔离级别下查询,还是会有表锁ALTER TABLE tSET (lock_escalation = disable)
影响锁定的除了上面提到的锁定模式、锁的粒度,还有就是事务的隔离级别。
所谓隔离级别其实就是事务与事务之间相互影响的程度,比如,一个事务修改了数据,那么其他事务是否能看到这些修改的数据,无论事务是否提交。对于最高的隔离级别,这个事务所做的修改,其他任何事务都看不到;而最低的隔离级别,这个事务所做的修改,可以被其他任何事务看到。
SQL Server隔离级别:
1.read uncommitted能解决丢失更新的问题,但是会导致脏读。
2.read committed读取的是已提交的数据,所以解决了脏读的问题,但是会有不可重复读取的问题,也就是在一个事务中有两次读取,第一次读取的和第二次读取的同一条数据,可能值是不同的,因为在事务中的select语句在读取完之后就立即释放的共享锁,而此时有另一个事务把刚才第一个事务读取的那条数据修改了,这样第一次读和第二次读到的值就会不同。
3.repeatable read解决了不可重复读取的问题,也就是在一个事务中的前后两次读取,读取到的数据值是一样的,但是会有幻读的可能,也就是第一次读出的数据确实和第二次读取的数据一样,但是第二次读取的记录条数可能多于第一次读取的记录条数,因为在读取的时候确实是锁住了被读取的记录,但是这个表可能添加了新的记录。
4.serializable通过锁住查询范围内的键、键与键之间的范围来解决幻读的问题,比如where id >=5 and id <=10,加入表表中只有id为7,9的两条记录,那么5-6、7-8、9-10这3个范围都会被锁住。
5.在ALLOW_SNAPSHOT_ISOLATION下的snapshot这种隔离级别允许读取事务一致性版本的数据,但可能不是最新的版本,也就是说在一个事务中只能读到某个版本,比如,在一个事务中有两次读取,第一次读完后,数据被另一个事务修改且事务提交了,此时进行第2次读取,那么读出来的还是和第一次读取一样的数据,这就是在一个事务中如果数据被其他事务修改了,读出来的数据也一样。优点是数据读取不会阻塞写,写也不会阻塞读取。另外,如果两个事务同时修改同一行数据,会导致更新冲突错误。
6.在READ_COMMITTED_SNAPSHOT下的read committed隔离级别允许在同一事务中总是能读取运行的已提交的数据,而且数据读取不会阻塞写,写也不会阻塞读取,也不会导致更新冲突。
上面是关于锁定的概念,那么接下来就是如何找到阻塞的进程,并解决阻塞问题。
--会话1,修改数据,但没有提交事务BEGIN TRANselect @@SPID --输出:287UPDATE t SET v = '88888'WHERE idd = 1--会话2,由于会话一事务没有提交,导致阻塞BEGIN TRANselect @@SPID --输出:105UPDATE t SET v = '888'WHERE idd = 1--查询会话1的等待信息select session_id, --查询的会话,也就是被阻塞的会话 wait_duration_ms, --等待毫秒数 wait_type, --等待类型,如:LCK_M_X表示正在等待获取排他锁 blocking_session_id --阻塞session_id会话的会话from sys.dm_os_waiting_taskswhere session_id = 105--查询这个被阻塞的会话请求的资源情况select resource_type, request_status, request_mode, request_session_id from sys.dm_tran_locks where request_session_id = 105 --说明会话2在update时一共获取了4个锁,共享数据库锁、2个意向独占锁(锁定表、数据页),--一个键锁锁住那条要更新的记录,只有这个键锁的请求状态时wait,--其他3个锁状态为grant表示已经会话2已经获得了锁。--另一种查看阻塞会话的方法:--查看当前会话的执行请求select session_id, status, blocking_session_id, wait_type, wait_timefrom sys.dm_exec_requestswhere session_id = 105--配置语句等待锁释放的时间--设置语句的锁请求超时时段--超时时段是以毫秒为单位,超时后会返回锁定错误返回错误:(1 行受影响)消息 1222,级别 16,状态 51,第 7 行已超过了锁请求超时时段。语句已终止。
3、死锁当两个事务分别锁定了资源,而又继续请求对方已获取的资源,那么就会产生死锁。
发生死锁的原因:
A、会话以不同的顺序访问表。
B、会话长时间运行事务,在一个事务中更新了很多表或行,这样增加了冲突的可能。
C、会话1申请了一些行锁,会话2申请了一些行锁,之后决定将其升级为表锁。
如果这些行在相同的数据页面中,并且两个会话同时在相同的页面上升级锁粒度,就会产生死锁。
set lock_timeout 1000 --跟踪死锁--会话1set transaction isolation level serializablebegin tranupdate tset v ='563'where idd =2waitfor delay '00:00:10'update tset v = '963'where idd =1commit--会话2set transaction isolation level serializablebegin tranupdate tset v ='234'where idd =1waitfor delay '00:00:10'update tset v = '987'where idd=2commit再开启一个会话,开启跟踪:/*===================================================================开启跟踪标志位: DBCC TRACEON(trace#[,...n],-1) [With No_InfoMsgs]检查某种或某些标志位是开启,还是关闭: DBCC TRACESTATUS(trace#[,...n],-1) [With No_InfoMsgs]1.trace#:指定一个或多个需要开启或需要检查状态的跟踪标志位数字2. -1:如果指定了-1,则以全局方式打开某种或某些跟踪标志位3.with No_InfoMsgs:当命令中包含此参数时,则禁止DBCC输出信息性消息=====================================================================*/--跟踪1222能把详细的死锁信息返回到SQL Server的日志中--标志位-1表示跟踪标志位1222应该对所有SQL Server连接全局启用DBCC TraceOn(1222,-1)go--验证标志位是否启动DBCC TraceStatusgo--关闭标志位DBCC TraceOff(1222,-1)go设置死锁优先级--设置死锁的优先级,调整一个查询会话由于死锁而被终止运行的可能性SET DeadLock_Priority Low | Normal | High | numeric-priority --是当前连接很有可能被终止运行set deadlock_priority Low --SQL Server终止回滚代价较小的连接set deadlock_priority Normal --减少连接被终止的可能性,除非另一个连接也是High或数值优先级大于5set deadlock_priority High --数值优先级:-10到10的值,-10最有可能被终止运行,10最不可能被终止运行,--两个数字谁大,谁就越不可能在死锁中被终止set deadlock_priority 10
- 【SQL Server学习笔记】事务、锁定、阻塞、死锁
- SQL Server 的锁定和阻塞
- oracle学习笔记(阻塞、死锁)
- 死锁 阻塞的sql server处理
- SQL SERVER读书笔记:阻塞与死锁
- SQL Server的阻塞、死锁问题
- SQL Server 阻塞 死锁说明与分析
- SQLServer事务、阻塞、死锁
- 30分钟全面解析-SQL事务+隔离级别+阻塞+死锁
- 30分钟全面解析-SQL事务+隔离级别+阻塞+死锁
- 30分钟全面解析-SQL事务+隔离级别+阻塞+死锁
- 搞懂 SQL Server 的锁定和阻塞
- 快速搞懂 SQL Server 的锁定和阻塞
- 【转载】快速搞懂 SQL Server 的锁定和阻塞
- 快速搞懂 SQL Server 的锁定和阻塞
- 快速搞懂 SQL Server 的锁定和阻塞
- 快速搞懂 SQL Server 的锁定和阻塞
- 快速搞懂 SQL Server 的锁定和阻塞
- oracle 两表数据对比---minus .
- Objective-C中的枚举类型
- 对SqlLite的浅浅认识(1)
- C# Winform 右下角弹出框
- XML(2)----XML的基本常识
- 【SQL Server学习笔记】事务、锁定、阻塞、死锁
- MySql存储过程
- 深入理解Mysql字符集设置
- ftell\fseek\rewind函数
- java的HashCode方法
- 1033,2052 是什么意思?
- C#右下角弹出消息框
- 开发自己的Maven插件之六:使用自己的report-plugin
- 经典的sql语句