CODEWITHSUNDEEP
sqlsql-server
Ghlouw
Ghlouw

Reputation: 1460

SQL query intermittently returns no results

I have a very straight forward select query such as this:

SELECT * FROM PWCWarehouse (nolock) WHERE PWCWarehouseID = 4

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?

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.

Any ideas what is happening here?

Upvotes: 3

Views: 1390

Answers (2)

Amit Bens
Amit Bens

Reputation: 1355

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.

Upvotes: 1

JNK
JNK

Reputation: 65187

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.

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.

Upvotes: 5

Related Questions