Frequently update and select Table Locking Problem

Hi guys,

I faced problem related to Locking and Isolation Level on Table(s).

My problems is there r some tables which r frequently updated, and I also want to fire select query over those tables every 1 seconds and want to get only committed records.

In current scenario we start transactions with ReadCommitted Lock for updating records. But in this scenario I can’t get select query result because of some of recourses r used by transactions so after some time it gives Deadlock error.

So I want solution like both operation run simultaneously and get only committed records at a time of transaction running

Please help me for solving my problem.

have you tried explicit dirty-read options in the select query ?

select * from mytable with (nolock) where mycolumn = 'hahaha'|||Yes I tried with that option, but it also return uncommitted data which i don't want.|||You cannot read "commited records" only of a transaction since those records actually does not exists untile the tran is closed: updated records are commited all together when you issue a commit transaction.|||

That's thing i knw, but the problem is when any trans for update or insert start on table I can't fire select query on that resources (means that table(s)) and it is going in waiting mode till trans is not committed or roleback.

So i resolve only this problems....

|||That's exactly the expected behavior if you use transactions.
You can use the 'with (rowlock)' option on the update (avoiding transactions) if you don't want other users to be put in wait, in that case a dirty read is allowed.|||

I got one solutions for resolving my problem using Snapshot Isolation.

But there are two types of Isolation READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION

so If u have any idea about them please inform me.

Is this resolve my problem or not?

what is the differance between them?

|||

Try use

select * from SomeTable with(READPAST)

Quote from Server Books:

READPAST

Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.

|||

Two possibilities that I know of, and the one you suggest is one of them. Using snapshot you will get the way the row looked before it was in the process of being edited.

Using readpast, as Alexander suggests is the other.

The question is what the purpose of your read queries is:

If you want a periodic count of where you are in a process, use snapshot

If you want to fetch the next, unlocked row for editing, then use readpast. A common use of this might be a set of rows that you have clerks working on. So you might execute a query like:

select <columns>
from workToDo
where status = 'NOTSTARTED'

In this case it is desirous to skip exclusively locked rows as they might be in the process of being updated. Using snapshot in this case would stink because you never want the user to collide with itself.

|||

Snapshot isolation level is not available in MS SQL 2000.

>> select <columns>
>> from workToDo
>> where status = 'NOTSTARTED'

If there is no index on "status" field then deadlock may occur anyway. Even if there is index on "status" field deadlock may occur because if table is updated with large amount of data and distribution of key values has changed then server may ignore index until UPDATE STATISTICS executed (explicitly or due to it is marked as auto-updatable).

|||Thanks to all for giving different solution. I finally solved my problem using snapshot.