具有SQL Server锁定的DataReader行为 [英] DataReader Behaviour With SQL Server Locking
问题描述
当通过DataReader从SQL Server查询返回大型数据集时，我们的数据层存在一些问题.当我们使用DataReader填充业务对象并将其序列化回客户端时，提取可能需要几分钟(我们正在向用户显示进度:-))，但是我们发现其中存在一些核心问题锁定受影响的表上正在进行的操作，这导致其他更新被阻止.
We are having some issues with our data layer when large datasets are returned from a SQL server query via a DataReader. As we use the DataReader to populate business objects and serialize them back to the client, the fetch can take several minutes (we are showing progress to the user :-)), but we've found that there's some pretty hard-core locking going on on the affected tables which is causing other updates to be blocked.
因此，我想我的一个天真问题是，在什么时候实际放弃了执行查询所导致的锁定?我们似乎发现锁一直保留到DataReader的最后一行已被处理并且DataReader实际上已关闭-看起来正确吗?迅速找到DataReader在后台的工作原理的101很好，因为我一直在努力寻找有关它的体面信息.
So I guess my slightly naive question is, at what point are the locks which are taken out as a result of executing the query actually relinquished? We seem to be finding that the locks are remaining until the last row of the DataReader has been processed and the DataReader is actually closed - does that seem correct? A quick 101 on how the DataReader works behind the scenes would be great as I've struggled to find any decent information on it.
我应该说，我意识到主要的问题是锁定问题，但是这里我只关心DataReader的行为.
I should say that I realise the locking issues are the main concern but I'm just concerned with the behaviour of the DataReader here.
推荐答案
-
在执行查询期间，如果网络缓冲区已满，SQL Server可以挂起查询.如果客户端无法跟上网络的读取，即会发生这种情况.它不调用SqlDataReader.Read().释放网络缓冲区后即恢复SQL Server查询.当客户端恢复SqlDataReader.Read()时.这意味着，当您从数据读取器读取数据集结果时，查询仍在服务器上执行.还有更多详细信息，例如网络缓冲区的大小，使用SqlBytes.Stream等在客户端进行的BLOB操作等，但其主旨是，速度较慢的客户端可能会导致查询被挂起，并且在客户端结束时查询结束
During the execution of a query SQL Server can suspend the query if the network buffers are full. This happens if the client does not keep up with reading the network, ie. it does not call SqlDataReader.Read(). The SQL Server query is resumed when network buffers are freed, ie. when the client resumes the SqlDataReader.Read(). This means that while you read your data set result from the data reader, the query is still executing on the server. There are more details, like size of network buffers, BLOB operations on the client side using SqlBytes.Stream and other, but the gist of the idea is that a slow client can cause the query to be suspended and the query ends when the client ends.
在正常隔离级别下读取数据(已提交读操作)时，SQL Server将在读取的行上放置短暂的共享锁.在较高的隔离级别下，锁将长期存在并保持到事务结束.
When reading data under normal isolation level (read commited) SQL Server will place short lived Shared locks on the rows it reads. Under higher isolation levels the locks are long lived and held untill the end of the transaction.
如果不使用任何事务，则每个SELECT语句都会在该语句的持续时间内创建一个隐式只读转换.
If no transactions are used, every SELECT statement will create an implicit read-only transation for the duration of the statement.
因此，从1、2和3可以看出，一个慢客户端在高隔离级别下运行查询将导致共享锁被长时间保留.
So from 1, 2, and 3 we can see that a slow client running a query under a high isolation level will cause Shared locks to be held a long time.
现在，您需要详细说明所观察的内容:
Now you need to detail what are you observing:
- 此查询持有什么样的锁?
- S，U，X?
- 行，页，表?
- 范围锁定?
- What kind of locks are being held by this query?
- S, U, X?
- row, page, table?
- range locks?
- 您是否使用REPEATABLE READ或SERIALIZATION隔离级别?如果是，为什么?
- 您是否使用锁定提示?如果是，为什么?
您最好的选择可能是快照隔离(至少需要SQL Server 2005)，作为快照隔离级别或作为读已提交快照.这样可以完全消除锁定问题，但可能会对tempdb造成一些IO压力.
Your best bet would probably be snapshot isolation (requires at least SQL Server 2005), either as snapshot isolation level or as read commited snapshot. This will eliminate the lock issue completely, but will create potentially some IO pressure on tempdb.
其他解决方案是使用游标，但会干扰现有的代码库，既复杂又容易出错(必须正确设置游标类型).
Other solution would be to use a cursor, but is intrussive on the exiting code base, complex, and still prone to errors (must get the cursor type right).
顺便说一句，我不建议更改客户端行为.我假设现在您正在SqlDataReader.Read循环中读取业务对象时将其编组，这就是这样做的方法.先读入内存，然后进行封送处理可能会在大型数据集上增加更多的问题.
BTW, I do not recommend changing the client behavior. I assume right now you're marshaling back the business objects as you read them , inside the SqlDataReader.Read loop, and that is the way to do it. Reading upfront into memory and then marshaling may add more problems on large data sets.
这篇关于具有SQL Server锁定的DataReader行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
