sql-server – 增加的网络延迟会导致MS SQL Server中的表锁吗？

如果我通过高延迟网络对SQL Server数据库进行单次调用,是否会因延迟而发生表锁定？假设我查询表A以获取某些记录,并且SQL Server必须通过慢速网络返回该数据 – 当服务器通过网络发送响应时,表A上是否存在读锁定,或者SQL Server是否在发送之前释放锁定响应？

此外,答案会根据答复的大小而有所不同吗？如果它只需要返回几KB到几百MB,那会有所作为吗？

创建显式事务,运行查询和关闭事务显然会导致表锁定,因为事务的持续时间与我的延迟相关.

解决方法

If client takes long time to receive data and in turn send
acknowledgement to SQL Server that it has received the data SQL Server
has to wait,due to this wait SQL Server will not release the locks
held by the query unless acknowledgement is received from client.

这不准确,它取决于隔离级别.

在默认情况下,READ COMMITTED锁定在语句执行期间不会保留. READ COMMITTED不提供语句级读取一致性,唯一的保证是您无法读取未提交的数据.获取并保持共享锁以读取该行然后释放.

除非你有LOB类型.

LOB类型可能非常大,无法缓冲.必须获取并保持共享锁,直到语句完成,基本上在READ COMMITTED处给出REPEATABLE READ行为.

If I’m making a single call to an MSSQL database over a high-latency
network,will table locks occur due to that latency?

延迟不会导致表锁定,不会.但是,如果已获取表锁,延迟将延长它.

引用一个比我更了解这种机制的人(@RemusRusanu)：

Results are returned back to the client program as the execution
proceeds. As rows ‘bubble’ up the execution tree,the top operator is
usually tasked with writing these rows into network buffers and
sending them to back to the client. The result is not created first
into some intermediate storage (memory or disk) and then sent back to
the client,instead it is sent back as is being created (as the query
executes). Sending the result back to the client is,of course,
subject to the network flow control protocol. If the client is not
actively consuming the result (eg. by calling SqlDataReader.Read())
then eventually the flow control will have to block the sending side
(the query that is being executed) and this in turn will suspend the
execution of the query. The query resumes and produces more results
(continue iterating the execution plan) as soon as the network flow
control relieves the required network resources. 07001

如果结果没有像SQL Server提供的那样快速消耗,无论是客户端还是网络,我们都会看到ASYNC_NETWORK_IO等待累积.重申一下,这不会影响所获得的锁,只会影响它们的持续时间.

（编辑：天瑞地安资讯网_黄海网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!