SQL Server学习笔记之事务、锁定、阻塞、死锁用法详解

这篇文章主要介绍了SQL Server学习笔记之事务、锁定、阻塞、死锁用法,结合实例形式分析了SQL Server中事务、锁定、阻塞、死锁的概念、功能及相关使用技巧,需要的朋友可以参考下

本文实例讲述了SQL Server学习笔记之事务、锁定、阻塞、死锁用法。分享给大家供大家参考,具体如下:

1、事务

隐式事务

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

/*==================================================================

当以create,drop,

fetch,open,

revoke,grand,

alter table,select,insert,delete,update,truncate table

语句首先执行的时候,SQL Server会话自动打开一个新的事务,

如果在会话中激活了隐式事务模式,那么这个事务会一直保持打开状态,

直到rollback或commit语句这个事务才结束,如果忘记提交事务,

那么在相应的隔离级别下,事务占用的锁可能不会释放,因此尽量不要用隐式事务。

====================================================================*/

–会话1

set implicit_transactions on

update t

set v = 'ext12'

set implicit_transactions off

select @@TRANCOUNT –输出:1,说明事务没有释放

–占用的X独占锁不会释放,会阻塞其他会话

?

1

2

3

–会话2,被会话1阻塞住了,不会返回任何记录

select *

from t

在会话1中执行commit来提交事务,那么会话2马上就会返回记录了。

现在把两个会话的执行顺序调换一下:

?

1

2

3

4

5

6

–会话1

set implicit_transactions on –打开了隐式事务

select *

from t

set implicit_transactions off

select @@TRANCOUNT –输入:1,说明这个会话中的事务也没有提交

?

1

2

3

4

5

6

–会话2,会话2没有被会话1阻塞,

–之所以这样是因为会话的默认隔离级别是read committed,

–会话1中的事务虽然没有提交,但是select语句在这种隔离级别下,

–运行完就会释放占用的S共享锁,所以不会阻塞写操作

update t

set v = 'ext'

显示数据库最早的活动事务

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

/*==============================================================

如果事务在数据库中始终打开,有可能会阻塞其他进程的操作,

为什么是有可能而不是一定呢,

原因就是:在默认隔离级别下的select语句查询到数据后就会立即释放共享锁。

另外,日志备份也只会截断不活动事务的那部分日志,所以活动的事务

会导致日志数据越来越多。

为了找到没有提交的事务,可以用下面的命令显示某个数据库最早的活动事务.

不过有个例外,就是下面的命令不会返回:不占用锁资源的未提交事务

================================================================*/

begin tran –开始显示事务

select *

from t –运行后立即释放共享锁

select @@TRANCOUNT –输入:1,说明没有提交事务

dbcc opentran('wc') –显示数据库最早的活动事务,

–但是这儿显示"没有处于打开状态的活动事务"

通过会话来查询事务信息

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

–由于上面未提交事务中的select语句在默认的隔离级别下执行后自动释放了共享锁,

–所以dbcc opentran命令并没有返回这个活动事务,

–不过下面的视图解决了这个问题,可以找到所有活动事务。

–找到活动事务

select session_id, –session_id与transaction_id的对应关系

transaction_id,

is_user_transaction,

is_local

from 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) s

where 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中可锁定的资源:

?

1

2

3

4

5

6

7

8

9

10

11

12

DB(数据库)

Metadata(系统元数据)

Object(数据库对象:视图,函数,存储过程,触发器)

Table(表)

Hobt(堆或B树)

Allocation Unit(按照数据的类型(数据,行溢出、大对象)分组的相关页面)

Extent(8个8KB的页面)

Page(8KB数据页面)

Rid(行标示符对应一个堆表的行)

Key(键范围上的锁、B树中的键)

File

Application

查看锁的活动

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

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_locks

WHERE request_session_id = 361

控制表的锁升级

每个锁都会消耗内存资源,当锁的数量增加时,那么所需要的内存就会增加,而系统内可用的内存就会减少。如果锁占用的内存比率超过一个阀值,SQL Server会将细粒度锁(行锁)升级为粗粒度锁(表锁),这个过程就是锁升级。

锁升级的优点是可以减少锁的数量,相应的减少内存的使用量,而缺点是由于锁住了更大的资源,所以会导致阻塞,降低并发性。

?

1

2

3

4

5

6

7

8

9

–默认值,不管是不是分区表,会在表级别启用锁升级

ALTER TABLE t

SET (lock_escalation = TABLE)

–当表升级时,如果表已经分区,会在分区级别启用锁升级

ALTER TABLE t

SET (lock_escalation = auto)

–在表级别禁用锁升级,如果用了TabLock提示或在Serializable隔离级别下查询,还是会有表锁

ALTER TABLE t

SET (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

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

–会话1,修改数据,但没有提交事务

BEGIN TRAN

select @@SPID –输出:287

UPDATE t

SET v = '88888'

WHERE idd = 1

–会话2,由于会话一事务没有提交,导致阻塞

BEGIN TRAN

select @@SPID –输出:105

UPDATE 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_tasks

where 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_time

from sys.dm_exec_requests

where session_id = 105

–配置语句等待锁释放的时间

–设置语句的锁请求超时时段

–超时时段是以毫秒为单位,超时后会返回锁定错误返回错误:(1 行受影响)消息 1222,级别 16,状态 51,第 7 行已超过了锁请求超时时段。语句已终止。

3、死锁

当两个事务分别锁定了资源,而又继续请求对方已获取的资源,那么就会产生死锁。

发生死锁的原因:

A、会话以不同的顺序访问表。

B、会话长时间运行事务,在一个事务中更新了很多表或行,这样增加了冲突的可能。

C、会话1申请了一些行锁,会话2申请了一些行锁,之后决定将其升级为表锁。

如果这些行在相同的数据页面中,并且两个会话同时在相同的页面上升级锁粒度,就会产生死锁。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

set lock_timeout 1000

–跟踪死锁–会话1

set transaction isolation level serializable

begin tran

update t

set v ='563'

where idd =2

waitfor delay '00:00:10'

update t

set v = '963'

where idd =1commit–会话2

set transaction isolation level serializable

begin tran

update t

set v ='234'

where idd =1

waitfor delay '00:00:10'

update t

set v = '987'

where idd=2

commit

再开启一个会话,开启跟踪:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

/*===================================================================

开启跟踪标志位:

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 TraceStatus

go

–关闭标志位

DBCC TraceOff(1222,-1)

go

设置死锁优先级–设置死锁的优先级,调整一个查询会话由于死锁而被终止运行的可能性

SET DeadLock_Priority Low | Normal | High | numeric-priority

–是当前连接很有可能被终止运行

set deadlock_priority Low

–SQL Server终止回滚代价较小的连接

set deadlock_priority Normal

–减少连接被终止的可能性,除非另一个连接也是High或数值优先级大于5

set deadlock_priority High

–数值优先级:-10到10的值,-10最有可能被终止运行,10最不可能被终止运行,

–两个数字谁大,谁就越不可能在死锁中被终止

set deadlock_priority 10

希望本文所述对大家SQL Server数据库程序设计有所帮助。

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/25217/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-09-23 1:06:56
下一篇 2022-09-24 11:41:03

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!