I have a very straight forward select query such as this:
我有一个非常直接的选择查询，比如:
SELECT * FROM PWCWarehouse (nolock) WHERE PWCWarehouseID = 4
在PWCWarehouseID = 4的PWCWarehouse (nolock)中选择*。
This query is called a lot and most of the time it returns the record just fine, but occasionally it returns nothing, even though the record IS still there. What could be the reason for this? Could it be because of locking by other processes that are updating the record? I have been trying different Isolation levels and currently have Isolation level = RepeatableRead so that should be possible to read volatile data?
这个查询被调用了很多次，而且大多数时候它返回的记录都很好，但是偶尔它也不会返回任何东西，即使记录仍然在那里。原因何在?是不是因为其他进程的锁定更新了记录?我一直在尝试不同的隔离级别，并且目前有隔离级别= RepeatableRead，这样就可以读取volatile数据了吗?
There are only 6 records in this table and they never increase or decrease, the only operations on this table are the above read and updates.
该表中只有6条记录，它们从不增加或减少，该表上的惟一操作是上面的读取和更新。
Any ideas what is happening here?
有什么想法吗?
2 个解决方案
#1
5
It's almost definitely a locking issue.
这几乎肯定是一个锁定问题。
If you are updating that record (potentially) at the same time as you are trying to read it, you should NOT use the NOLOCK hint since this will allow dirty reads.
如果在尝试读取记录的同时(可能)更新该记录，则不应该使用NOLOCK提示，因为这会导致脏读。
If the record has been deleted and is being inserted, or has been updated but not committed, it will not be visible to your SELECT using NOLOCK.
如果记录已经被删除并且正在被插入，或者已经更新但没有提交，那么您的SELECT使用NOLOCK将不可见。
#2
1
If you want to read row that are changed but still not committed, you can use ReadUncommitted isolation level. This should allow you to read without locks while still have non-committed changes visible.
如果希望读取已更改但仍未提交的行，可以使用ReadUncommitted隔离级别。这应该允许您在没有锁的情况下阅读，同时仍然可以看到未提交的更改。
#1
5
It's almost definitely a locking issue.
这几乎肯定是一个锁定问题。
If you are updating that record (potentially) at the same time as you are trying to read it, you should NOT use the NOLOCK hint since this will allow dirty reads.
如果在尝试读取记录的同时(可能)更新该记录，则不应该使用NOLOCK提示，因为这会导致脏读。
If the record has been deleted and is being inserted, or has been updated but not committed, it will not be visible to your SELECT using NOLOCK.
如果记录已经被删除并且正在被插入，或者已经更新但没有提交，那么您的SELECT使用NOLOCK将不可见。
#2
1
If you want to read row that are changed but still not committed, you can use ReadUncommitted isolation level. This should allow you to read without locks while still have non-committed changes visible.
如果希望读取已更改但仍未提交的行，可以使用ReadUncommitted隔离级别。这应该允许您在没有锁的情况下阅读，同时仍然可以看到未提交的更改。