June 17, 2008 at 11:24 am
I have a view (vworder) thats defined like:
select * from tblorder with (nolock)
union all
select * from tblorderarhive with (nolock)
And I have a process that says if an order does not exist in the view, than move the order to tblorder, the check for existence is like:
where OrID NOT IN (SELECT OrID FROM vwOrder WITH (NOLOCK))
BUT if some order (say OrID= 123) is being modified (updated, we never deletes) while the process runs, it (OrID= 123) will try to get moved again, even though I have nolock on both the table and view level. The database is read committed.
Thanks for any help.
Kathleen
June 17, 2008 at 11:45 am
NOLOCK is the same as READUNCOMMITTED:
From BOL:
Specifies that dirty reads are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that then are rolled back by other transactions. This may generate errors for your transaction, present users with data that was never committed, or cause users to see records twice (or not at all).
Most importantly:
READUNCOMMITTED and NOLOCK cannot be specified for tables modified by insert, update, or delete operations. The SQL Server query optimizer ignores the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement.
Instead of the READUNCOMMITTED hint, perhaps READPAST should be used:
Specifies that the Database Engine not read rows that are locked by other transactions. Under most circumstances, the same is true for pages. The Database Engine skips past the rows or pages instead of blocking the current transaction until the locks are released.
For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5. READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.
SQL = Scarcely Qualifies as a Language
June 17, 2008 at 11:53 am
Thanks Carl for your help!
I might not have presented my case well. I was using nolock so I dont miss the orders that are being modified (I dont use nolock on the update statements, I might use rowlock though), but somehow nolock did not work for me, the orders that are modified are not visible from the view vworder. Not sure why?
June 17, 2008 at 12:03 pm
NOLOCK is a pretty scary thing to use, and you have misused it here. The problem you are having is probably the order being modified is moving from one page to another and your query is missing the record because it ends up in a page that was already read by the query.
If your row is on PageA and you run a query that is read uncommitted, you may read PageA and be reading PageB when an order is moved from PageC to PageA. If this happens, you will miss the record. This, among other things, is a big reason to not use NOLOCK or READ UNCOMMITTED. In your situation, you are clearly trying to run a process that conflicts with your application updates, but you do not want either process to wait.
You may be able to solve your particular issue with SNAPSHOT ISOLATION. This would read the last committed version of the record. For updates you would be ok because the version before the update started would be read by your process and you would not insert a new record. I do not know what you have for other insert processes, but they may present a different set of issues.
June 17, 2008 at 12:58 pm
Thanks Micheal, it makes sense. I am not sure if we are switching to snapshot isolation now. But a quick fix would be to have a small table that contains all the orderids, and check against it without nolock.
June 17, 2008 at 1:05 pm
That would probably do it. As another temporary stop-gap option, you could do the check you are doing now with NOLOCK, but when you find a record that you think needs to be inserted, look for it without the NOLOCK hint. This would make your INSERT wait on other processes but it would only be searching on a single record - drastically reducing it's ability to block something else.
You could also trick the optimizer and avoid your lock conflicts altogether. If your query checking for records that needs to be inserted was completely satisfied by an index (all fields were in the index or included fields) a SELECT statement will only share lock the index. If you can create an index on only fields that are not updated by your UI and this index can satisfy your "do I need a new record" query, you can essentially get the optimizer to only use the table for the update and only use the index for the select. They would then not be able to conflict with each other.
June 17, 2008 at 1:06 pm
There is an interesting document related to NOLOCK and READUNCOMMITED. which talks about reading row more than once and missing of rows
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
really helpfull to understad....thanks to TONY
subban
June 17, 2008 at 1:09 pm
Thank you all, very helpful!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply