当前位置: 澳门新濠3559 > 数据库 > 正文

不发布共享锁来阻止其他事务修改当前事务读取

时间:2019-09-15 04:13来源:数据库
 以前遇到过,但仅限于听同事说加上NOLOCK好一些,今天仔细研究测试了下,终于理解了,那么加与不加到底区别在哪呢? with(nolock)的功能: 不发布共享锁来阻止其他事务修改当前事务

  以前遇到过,但仅限于听同事说加上NOLOCK好一些,今天仔细研究测试了下,终于理解了,那么加与不加到底区别在哪呢?

with(nolock)的功能:

不发布共享锁来阻止其他事务修改当前事务读取的数据,的锁定造成阻塞时。本帖提供两种做法,可避免在 SQL Server 事务锁定时产生的不正常或长时间阻塞,让用户和程序也无限期等待,甚至引起 connection pooling 连接数超过容量。

本帖提供两种做法,可避免在 SQL Server 事务锁定时产生的不正常或长时间阻塞,让用户和程序也无限期等待,甚至引起 connection pooling 连接数超过容量。

    我先说下其区别,之后再做测试。

1: 指定允许脏读。不发布共享锁来阻止其他事务修改当前事务读取的数据,其他事务设置的排他锁不会阻碍当前事务读取锁定数据。允许脏读可能产生较多的并发操作,但其代价是读取以后会被其他事务回滚的数据修改。这可能会使您的事务出错,向用户显示从未提交过的数据,或者导致用户两次看到记录(或根本看不到记录)。有关脏读、不可重复读和幻读的详细信息,请参阅并发影响。

所谓的「阻塞」,是指当一个数据库会话中的事务,正在锁定其他会话事务想要读取或修改的资源,造成这些会话发出的请求进入等待的状态。SQL Server 默认会让被阻塞的请求无限期地一直等待,直到原来的事务释放相关的锁,或直到它超时 (根据 SET LOCK_TIMEOUT,本文后续会提到)、服务器关闭、进程被杀死。一般的系统中,偶尔有短时间的阻塞是正常且合理的;但若设计不良的程序,就可能导致长时间的阻塞,这样就不必要地锁定了资源,而且阻塞了其他会话欲读取或更新的需求。遇到这种情况,可能就需要手工排除阻塞的状态,而本文接下来要介绍两种排除阻塞的做法。

所谓的「阻塞」,是指当一个数据库会话中的事务,正在锁定其他会话事务想要读取或修改的资源,造成这些会话发出的请求进入等待的状态。SQL Server 默认会让被阻塞的请求无限期地一直等待,直到原来的事务释放相关的锁,或直到它超时 (根据 SET LOCK_TIMEOUT,本文后续会提到)、服务器关闭、进程被杀死。一般的系统中,偶尔有短时间的阻塞是正常且合理的;但若设计不良的程序,就可能导致长时间的阻塞,这样就不必要地锁定了资源,而且阻塞了其他会话欲读取或更新的需求。遇到这种情况,可能就需要手工排除阻塞的状态,而本文接下来要介绍两种排除阻塞的做法。

    大家都知道,每新建一个查询,都相当于创建一个会话,在不同的查询分析器里面进行的操作,可以影响到其他会话的查询,极端的情况可能会一直处于阻塞中,哪怕只是一个很简单的查询都“特别慢”。

2: READUNCOMMITTED 和 NOLOCK 提示仅适用于数据锁。所有查询(包括那些带有 READUNCOMMITTED 和 NOLOCK 提示的查询)都会在编译和执行过程中获取 Sch-S(架构稳定性)锁。因此,当并发事务持有表的 Sch-M(架构修改)锁时,将阻塞查询。例如,数据定义语言 (DDL) 操作在修改表的架构信息之前获取 Sch-M 锁。所有并发查询(包括那些使用 READUNCOMMITTED 或 NOLOCK 提示运行的查询)都会在尝试获取 Sch-S 锁时被阻塞。相反,持有 Sch-S 锁的查询将阻塞尝试获取 Sch-M 锁的并发事务。有关锁行为的详细信息,请参阅锁兼容性(数据库引擎)。

 

 

    BEGIN TRAN 是开始一个事务的意思,开始之后可执行一些SQL语句,接着需要执行COMMIT进行提交或者ROLLBACK进行回滚,否则就会出现上面的情况。但如果使用NOLOCK进行查询的时候,就不会因为别的回话没有提交或回滚,而受阻塞。所以概括起来,可以用以下语句来总结:

3: 不能为通过插入、更新或删除操作修改过的表指定 READUNCOMMITTED 和 NOLOCK。SQL Server 查询优化器忽略 FROM 子句中应用于 UPDATE 或 DELETE 语句的目标表的 READUNCOMMITTED 和 NOLOCK 提示。

日前公司 server-side 有组件,疑似因撰写时 exception-handling 做得不周全,导致罕见的特殊例外发生时,让 SQL Server 的事务未执行到 cmmmit 或 rollback,造成某些表或记录被「锁定 (lock)」。后来又有大量的 request,要透过代码访问这些被锁定的记录,结果造成了严重的长时间「阻塞」,最后有大量 process (进程) 在 SQL Server 呈现「等待中 (WAIT)」的状态。

日前公司 server-side 有组件,疑似因撰写时 exception-handling 做得不周全,导致罕见的特殊例外发生时,让 SQL Server 的事务未执行到 cmmmit 或 rollback,造成某些表或记录被「锁定 (lock)」。后来又有大量的 request,要透过代码访问这些被锁定的记录,结果造成了严重的长时间「阻塞」,最后有大量 process (进程) 在 SQL Server 呈现「等待中 (WAIT)」的状态。

    NOLOCK能使当前会话的查询,不受其它会话的事务所阻塞。但是这样做,就读取了其它事务的“修改后未提交的”数据。

这个东西是有利有弊,

由于 SQL Server 的「事务隔离级别」默认是 READ COMMITTED (事务期间别人无法读取),加上 SQL Server 的锁定造成阻塞时,默认是别的进程必须无限期等待 (LOCK_TIMEOUT = -1)。结果这些大量的客户端 request 无限期等待永远不会提交或回滚的事务,并一直占用着 connection pool 中的资源,最后造成 connection pooling 连接数目超载。

由于 SQL Server 的「事务隔离级别」默认是 READ COMMITTED (事务期间别人无法读取),加上 SQL Server 的锁定造成阻塞时,默认是别的进程必须无限期等待 (LOCK_TIMEOUT = -1)。结果这些大量的客户端 request 无限期等待永远不会提交或回滚的事务,并一直占用着 connection pool 中的资源,最后造成 connection pooling 连接数目超载。

    现在我们进行测试,一定要注意,必须在多个会话下才可以,也就是说,需要建三个查询分析器窗口。

使用with(nolock)时查询不受其他排它锁阻塞

查了一些书,若我们要查询 SQL Server 目前会话中的 lock 超时时间,可用以下的命令:

查了一些书,若我们要查询 SQL Server 目前会话中的 lock 超时时间,可用以下的命令:

    表用最简单的表,自己动手建一个。

举个例子:模拟事务正在进行
打开回话一:执行

SELECT @@LOCK_TIMEOUT

SELECT @@LOCK_TIMEOUT

    查询分析器一:执行

SELECT @@spid查看会话ID --查询当前会话

执行结果默认为 -1,意即欲访问的对象或记录被锁定时,会无限期等待。若欲更改当前会话的此值,可用下列命令:

澳门新濠3559,执行结果默认为 -1,意即欲访问的对象或记录被锁定时,会无限期等待。若欲更改当前会话的此值,可用下列命令:

       SELECT * FROM dbo.test_main

BEGIN TRAN

SET LOCK_TIMEOUT 3000

SET LOCK_TIMEOUT 3000

       得到

UPDATE TEST SET NAME='Timmy' WHERE ID =1;

后面的 3000,其单位为毫秒,亦即会先等待被锁定的对象 3 秒钟。若事务仍未释放锁,则会抛回如下代号为 1222 的错误信息,可供程序员编程时做相关的逾时处理:

后面的 3000,其单位为毫秒,亦即会先等待被锁定的对象 3 秒钟。若事务仍未释放锁,则会抛回如下代号为 1222 的错误信息,可供程序员编程时做相关的逾时处理:

        id    value
        1     one
        2     two
        3     three
        4     four

--ROLLBACK -- 不提交也不回滚
打开回话二:执行
SELECT * FROM TEST;

消息 1222,级别 16,状态 51,第 3 行
已超过了锁请求超时时段。

消息 1222,级别 16,状态 51,第 3 行
已超过了锁请求超时时段。

       接着执行如下:

打开回话三查询阻塞情况:
SELECT wt.blocking_session_id AS BlockingSessesionId
,sp.program_name AS ProgramName
,COALESCE(sp.LOGINAME, sp.nt_username) AS HostName
,ec1.client_net_address AS ClientIpAddress
,db.name AS DatabaseName
,wt.wait_type AS WaitType
,ec1.connect_time AS BlockingStartTime
,wt.WAIT_DURATION_MS/1000 AS WaitDuration
,ec1.session_id AS BlockedSessionId
,h1.TEXT AS BlockedSQLText
,h2.TEXT AS BlockingSQLText
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.databases db
ON db.database_id = tl.resource_database_id
INNER JOIN sys.dm_os_waiting_tasks AS wt
ON tl.lock_owner_address = wt.resource_address
INNER JOIN sys.dm_exec_connections ec1
ON ec1.session_id = tl.request_session_id
INNER JOIN sys.dm_exec_connections ec2
ON ec2.session_id = wt.blocking_session_id
LEFT OUTER JOIN master.dbo.sysprocesses sp
ON SP.spid = wt.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
打开会话四:执行

若将 LOCK_TIMEOUT 设置为 0,亦即当欲访问对象被锁定时,完全不等待就抛回代号 1222 的错误信息。此外,此一 SET LOCK_TIMEOUT 命令,影响范例只限当前会话 (进程),而非对某个表做永久的设置。

若将 LOCK_TIMEOUT 设置为 0,亦即当欲访问对象被锁定时,完全不等待就抛回代号 1222 的错误信息。此外,此一 SET LOCK_TIMEOUT 命令,影响范例只限当前会话 (进程),而非对某个表做永久的设置。

       BEGIN TRAN
       INSERT INTO test_main VALUES(5, 'five')

SELECT * FROM TEST WITH(NOLOCK)--会发现数据马上出来



       一行受影响   

这是由于加了with(nolock)会话一事务设置的排他锁不会阻碍当前事务读取锁定数据,所以会话四不会被阻塞

接下来我们在 SSMS 中,开两个会话 (查询窗口) 做测试,会话 A 创建会造成阻塞的事务进程,会话 B 去访问被锁定的记录。

接下来我们在 SSMS 中,开两个会话 (查询窗口) 做测试,会话 A 创建会造成阻塞的事务进程,会话 B 去访问被锁定的记录。

    查询分析器二:执行

 

 

 

      SELECT * FROM dbo.test_main

但是:假如由于某种原因,该事务回滚了, SELECT * FROM Book AS b WHERE b.BookName = 'Timmy' AND b.ID = 1 查询到的这边数据就是一条脏数据,又叫无效数据的读出,是指在数据库访问中,事务T1将某一直修改,然后事务T2读取该值,此后T1因为某种原因撤销对该值的修改,这就导致T2所读取到的数据是无效的

--会话 A
BEGIN TRAN;
UPDATE Orders SET EmployeeID=7 WHERE OrderID=10248
--rollback; --故意不提交或回滚

--会话 A
BEGIN TRAN;
UPDATE Orders SET EmployeeID=7 WHERE OrderID=10248
--rollback; --故意不提交或回滚

      则卡死,受上一会话所阻塞。查不出结果。

所以with(nolock)是有利有弊的
大体使用场景:

 

 

      补充:那么卡死怎么办呢?我们已经说过,要执行提交或者回滚操作才可以,那么在会话一中执行COMMIT即可。 之后此查询立刻显示结果。

基础数据表,这些表变更较少
历史数据库修改较少
业务允许出现脏读的情况
数据量超大的表,出于性能考虑,而允许脏读

 

 

    查询分析器三:执行

看下这三个区别:
SELECT * FROM TEST NOLOCK -- nolock起到了表的别名的作用

--会话 B
SELECT * FROM Orders WHERE OrderID=10248

--会话 B
SELECT * FROM Orders WHERE OrderID=10248

       SELECT * FROM test_main(NOLOCK)

SELECT * FROM TEST (NOLOCK);

 

 

      则显示如下

SELECT * FROM TEST WITH(NOLOCK);

 分别执行后,因为欲访问的记录是同一条,按照 SQL Server 「事务隔离级别」和「锁」的默认值,会话 B 将无法读取该条数据,而且会永远一直等下去 (若在现实项目里写出这种代码,就准备被客户和老板臭骂)。

 分别执行后,因为欲访问的记录是同一条,按照 SQL Server 「事务隔离级别」和「锁」的默认值,会话 B 将无法读取该条数据,而且会永远一直等下去 (若在现实项目里写出这种代码,就准备被客户和老板臭骂)。

       id    value
        1     one
        2     two
        3     three
        4     four

(NOLOCK)与WITH(NOLOCK)其实功能上是一样的,但08版本就不推荐省略with



        5     five

在使用链接服务器的SQL当中,(NOLOCK)不会生效,WITH(NOLOCK)才会生效

若将会话 B 先加上 SET LOCK_TIMEOUT 3000 的设置,如下,则会话 B 会先等待 3 秒钟,才抛出代号 1222 的「锁请求已超时」错误信息:

若将会话 B 先加上 SET LOCK_TIMEOUT 3000 的设置,如下,则会话 B 会先等待 3 秒钟,才抛出代号 1222 的「锁请求已超时」错误信息:

       但最后一行并没有真正存储在数据库中,因为会话一还没有进行提交,我们用NOLOCK就查询出来了。

 

 

  也许你会想,那什么情况下用NOLOCK呢?经过我们的分析,用NOLOCK是为了避免出现卡死状态,那我们就可以分析其环境了。

--会话 B
SET LOCK_TIMEOUT 3000
SELECT * FROM Orders WHERE OrderID=10248
--SET LOCK_TIMEOUT -1

--会话 B
SET LOCK_TIMEOUT 3000
SELECT * FROM Orders WHERE OrderID=10248
--SET LOCK_TIMEOUT -1

  一个经常操作的表,并且每次操作都很重要,这样一般要用到事务进行处理,因为可以避免出错的几率,

 

 

我们查询时,要用NOLOCK,否则遇上卡死的几率很大。别人执行一个事务,还没处理完呢,你就查询了,那就卡死了。有了NOLOCK就可以解决这个问题了。

 执行结果:

 执行结果:

消息 1222,级别 16,状态 51,第 3 行
已超过了锁请求超时时段。
语句已终止。

消息 1222,级别 16,状态 51,第 3 行
已超过了锁请求超时时段。
语句已终止。



另根据我之前写的文章「30 分钟快快乐乐学 SQL Performance Tuning」所述:

另根据我之前写的文章「30 分钟快快乐乐学 SQL Performance Tuning」所述:

撰写不当的 SQL 语句,会让数据库的索引无法使用,造成全表扫描或全聚集索引扫描。例如不当的:NOT、OR 算符使用,或是直接用 号做来串接两个字段当作 WHERE 条件,都可能造成索引失效,变成全表扫描,除了性能变差之外,此时若这句不良的 SQL 语句,是本帖前述会话 B 的语句,由于会造成全表扫描或聚集索引扫描,因此就一定会被会话 A 的事务阻塞 (因为扫描全表时,一定也会读到 OrderID=10248 这一条会话 A 正在锁定的记录)。

撰写不当的 SQL 语句,会让数据库的索引无法使用,造成全表扫描或全聚集索引扫描。例如不当的:NOT、OR 算符使用,或是直接用 号做来串接两个字段当作 WHERE 条件,都可能造成索引失效,变成全表扫描,除了性能变差之外,此时若这句不良的 SQL 语句,是本帖前述会话 B 的语句,由于会造成全表扫描或聚集索引扫描,因此就一定会被会话 A 的事务阻塞 (因为扫描全表时,一定也会读到 OrderID=10248 这一条会话 A 正在锁定的记录)。

下方的 SQL 语句,由于 OrderID 字段有设索引,因此下图 1 的「执行计划」,会以算法中的「二分查找法」在索引中快速查找 OrderID=10250 的记录。

下方的 SQL 语句,由于 OrderID 字段有设索引,因此下图 1 的「执行计划」,会以算法中的「二分查找法」在索引中快速查找 OrderID=10250 的记录。

SELECT * FROM Orders WHERE OrderID=10250

SELECT * FROM Orders WHERE OrderID=10250

SELECT * FROM Orders WHERE OrderID=10250 AND ShipCountry='Brazil'

SELECT * FROM Orders WHERE OrderID=10250 AND ShipCountry='Brazil'

澳门新濠3559 1
图 1 有正确使用到索引的 SQL 语句,以垂直的方向使用索引。用 AND 算符时,只要有任一个字段有加上索引,就能受惠于索引的好处,并避免全表扫描

澳门新濠3559 2
图 1 有正确使用到索引的 SQL 语句,以垂直的方向使用索引。用 AND 算符时,只要有任一个字段有加上索引,就能受惠于索引的好处,并避免全表扫描

此时若我们将这句 SQL 语句,当作前述会话 B 的语句,由于它和会话 A 所 UPDATE 的 OrderID=10248 不是同一条记录,因此不会受会话 A 事务未回滚的影响,会话 B 能正常执行 SELECT 语句。

此时若我们将这句 SQL 语句,当作前述会话 B 的语句,由于它和会话 A 所 UPDATE 的 OrderID=10248 不是同一条记录,因此不会受会话 A 事务未回滚的影响,会话 B 能正常执行 SELECT 语句。

但若我们将会话 B 的 SQL 语句,改用如下的 OR 算符,由于 ShipCountry 字段没有加上索引,此时会造成聚集索引扫描 (和全表扫描一样,会对整个表做逐条记录的 scan)。如此一来,除了性能低落以外,还会因为在逐条扫描时,读到会话 A 中锁定的 OrderID=10248 那一条记录,造成阻塞,让会话 B 永远呈现「等待中」的状态。

但若我们将会话 B 的 SQL 语句,改用如下的 OR 算符,由于 ShipCountry 字段没有加上索引,此时会造成聚集索引扫描 (和全表扫描一样,会对整个表做逐条记录的 scan)。如此一来,除了性能低落以外,还会因为在逐条扫描时,读到会话 A 中锁定的 OrderID=10248 那一条记录,造成阻塞,让会话 B 永远呈现「等待中」的状态。

SELECT * FROM Orders WHERE OrderID=10250 OR ShipCountry='Brazil'

SELECT * FROM Orders WHERE OrderID=10250 OR ShipCountry='Brazil'

澳门新濠3559 3
图 2 未正确使用索引的 SQL 语句,以水平的方向使用索引。用 OR 算符时,必须「所有」用到的字段都有加上索引,才能有效使用索引、避免全表扫描

澳门新濠3559 4
图 2 未正确使用索引的 SQL 语句,以水平的方向使用索引。用 OR 算符时,必须「所有」用到的字段都有加上索引,才能有效使用索引、避免全表扫描



发生阻塞时,透过以下命令,可看出是哪个进程 session id,阻塞了哪几个进程 session id,且期间经过了多少「毫秒 (ms)」。如下图 3 里 session id = 53 阻塞了 session id = 52 的进程。另透过 SQL Server Profiler 工具,也能看到相同的内容。

发生阻塞时,透过以下命令,可看出是哪个进程 session id,阻塞了哪几个进程 session id,且期间经过了多少「毫秒 (ms)」。如下图 3 里 session id = 53 阻塞了 session id = 52 的进程。另透过 SQL Server Profiler 工具,也能看到相同的内容。

SELECT blocking_session_id, wait_duration_ms, session_id FROM sys.dm_os_waiting_tasks

SELECT blocking_session_id, wait_duration_ms, session_id FROM sys.dm_os_waiting_tasks

澳门新濠3559 5
图 3 本帖前述会话 A 的 UPDATE 语句 (53),阻塞了会话 B 的 SELECT 语句 (52)

澳门新濠3559 6
图 3 本帖前述会话 A 的 UPDATE 语句 (53),阻塞了会话 B 的 SELECT 语句 (52)

透过以下两个命令,我们还能看到整个数据库的锁定和阻塞详细信息:

透过以下两个命令,我们还能看到整个数据库的锁定和阻塞详细信息:

SELECT * FROM sys.dm_tran_locks

SELECT * FROM sys.dm_tran_locks

EXEC sp_lock

EXEC sp_lock

澳门新濠3559 7
图 4 session id = 52 的 process 因阻塞而一直处于等待中 (WAIT)

澳门新濠3559 8
图 4 session id = 52 的 process 因阻塞而一直处于等待中 (WAIT)

另透过 KILL 命令,可直接杀掉造成阻塞的 process,如下:

另透过 KILL 命令,可直接杀掉造成阻塞的 process,如下:

KILL 53

KILL 53



欲解决无限期等待的问题,除了前述的 SET LOCK_TIMEOUT 命令外,还有更省事的做法,如下,在会话 B 的 SQL 语句中,在表名称后面加上 WITH (NOLOCK) 关键字,表示要求  SQL Server,不必去考虑这个表的锁定状态为何,因此也可减少「死锁 (dead lock)」发生的机率。但 WITH (NOLOCK) 不适用 INSERT、UPDATE、DELETE。

欲解决无限期等待的问题,除了前述的 SET LOCK_TIMEOUT 命令外,还有更省事的做法,如下,在会话 B 的 SQL 语句中,在表名称后面加上 WITH (NOLOCK) 关键字,表示要求  SQL Server,不必去考虑这个表的锁定状态为何,因此也可减少「死锁 (dead lock)」发生的机率。但 WITH (NOLOCK) 不适用 INSERT、UPDATE、DELETE。

SELECT * FROM Orders WITH (NOLOCK) WHERE OrderID=10248

SELECT * FROM Orders WITH (NOLOCK) WHERE OrderID=10248

类似的功能,也可如下,在 SQL 语句前,先设置「事务隔离级别」为可「脏读 (dirty read)」。

类似的功能,也可如下,在 SQL 语句前,先设置「事务隔离级别」为可「脏读 (dirty read)」。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Orders WHERE OrderID=10248

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Orders WHERE OrderID=10248

两种做法的效果类似,让会话 B 即使读到被锁阻塞的记录,也永远不必等待,但可能读到别人未提交的数据。虽然说这种做法让会话 B 不用请求共享锁,亦即永远不会和其他事务发生冲突,但应考虑项目开发实际的需求,若会话 B 要查询的是原物料的库存量,或银行系统的关键数据,就不适合用这种做法,而应改用第一种做法的 SET LOCK_TIMEOUT 命令,明确让数据库抛回等候逾时的错误代号 1222,再自己写代码做处理。

两种做法的效果类似,让会话 B 即使读到被锁阻塞的记录,也永远不必等待,但可能读到别人未提交的数据。虽然说这种做法让会话 B 不用请求共享锁,亦即永远不会和其他事务发生冲突,但应考虑项目开发实际的需求,若会话 B 要查询的是原物料的库存量,或银行系统的关键数据,就不适合用这种做法,而应改用第一种做法的 SET LOCK_TIMEOUT 命令,明确让数据库抛回等候逾时的错误代号 1222,再自己写代码做处理。



归根究柢,我们在编程时,就应该避免写出会造成长时间阻塞的 SQL 语句,亦即应最小化锁定争用的可能性,以下为一些建议:

归根究柢,我们在编程时,就应该避免写出会造成长时间阻塞的 SQL 语句,亦即应最小化锁定争用的可能性,以下为一些建议:

  • 尽可能让事务轻薄短小、让锁定的时间尽量短,例如把不必要的命令移出事务外,或把一个大量更新的事务,切成多个更新较少的事务,以改善并发性。
  • 将组成事务的 SQL 语句,摆到一个「批 (batch) 处理」,以避免不必要的延迟。这些延迟常由 BEGIN TRAN ... COMMIT TRAN 命令之间的网络 I/O 所引起。
  • 考虑将事务的 SQL 语句写在一个存储过程内。一般来说,存储过程的执行速度会比批处理的 SQL 语句快,且存储过程可降低网络的流量和 I/O,让事务可更快完成。
  • 尽可能频繁地认可 Cursor 中的更新,因为 Cursor 的处理速度较慢,会让锁定的时间较长。
  • 若无必要,使用较宽松的事务隔离级别,如前述的 WITH (NOLOCK) 和 READ UNCOMMITTED。而非为了项目开发方便,全部使用默认的 READ COMMITTED 级别。
  • 避免在事务执行期间,还要等待用户的反馈或交互,这样可能会造成无限期的持有锁定,如同本帖一开始提到的状况,最后造成大量的阻塞和数据库 connection 被占用。
  • 避免事务 BEGIN TRAN 后查询的数据,可能在事务开始之前先被引用。
  • 避免在查询时 JOIN 过多的表 (此指非必要的 JOIN),否则除了性能较差外,也很容易读到正被锁定或阻塞中的表和字段。
  • 应注意在一个没有索引的表上,过量的「行锁」,或一些锁定使用了过多的内存和系统资源时,SQL Server 为了有效地管理这些锁定,会尝试将锁定扩展为整个表的「表锁」,此时会很容易造成其他 process 在访问时的阻塞和等待。
  • 尽可能让事务轻薄短小、让锁定的时间尽量短,例如把不必要的命令移出事务外,或把一个大量更新的事务,切成多个更新较少的事务,以改善并发性。
  • 将组成事务的 SQL 语句,摆到一个「批 (batch) 处理」,以避免不必要的延迟。这些延迟常由 BEGIN TRAN ... COMMIT TRAN 命令之间的网络 I/O 所引起。
  • 考虑将事务的 SQL 语句写在一个存储过程内。一般来说,存储过程的执行速度会比批处理的 SQL 语句快,且存储过程可降低网络的流量和 I/O,让事务可更快完成。
  • 尽可能频繁地认可 Cursor 中的更新,因为 Cursor 的处理速度较慢,会让锁定的时间较长。
  • 若无必要,使用较宽松的事务隔离级别,如前述的 WITH (NOLOCK) 和 READ UNCOMMITTED。而非为了项目开发方便,全部使用默认的 READ COMMITTED 级别。
  • 避免在事务执行期间,还要等待用户的反馈或交互,这样可能会造成无限期的持有锁定,如同本帖一开始提到的状况,最后造成大量的阻塞和数据库 connection 被占用。
  • 避免事务 BEGIN TRAN 后查询的数据,可能在事务开始之前先被引用。
  • 避免在查询时 JOIN 过多的表 (此指非必要的 JOIN),否则除了性能较差外,也很容易读到正被锁定或阻塞中的表和字段。
  • 应注意在一个没有索引的表上,过量的「行锁」,或一些锁定使用了过多的内存和系统资源时,SQL Server 为了有效地管理这些锁定,会尝试将锁定扩展为整个表的「表锁」,此时会很容易造成其他 process 在访问时的阻塞和等待。

 本帖尚未提到死锁和其他更进阶的议题,等下次有空再继续泡茶聊天。  

 

编辑:数据库 本文来源:不发布共享锁来阻止其他事务修改当前事务读取

关键词: 澳门新濠3559