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 Read Committed 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.

It seems to me that a deadlock error would only happen if you were trying to update the same tables in different orders at the same time - i.e., Update tableA, tableB running at the same time as Update tableB, tableA. Txn1 would be waiting for Txn2 to finish, and Txn2 would be waiting for Txn1 to finish.

If what you are getting is just a locking block, it might be a different story.

|||

No in my scenario not happen as u told. In that only one trans running but by default the isolation level is READ_COMMITTED and in that when i trying to run select query it is going in waiting mode till trans is committed or roleback.

but i want to run both simultaniously and get only committed data

|||

Which version you are using 2000 or 2005? If you are using 2005, then i think you could use Snapshot isolation level.

|||

I am use 2005 .

I also found Snapshot Isolation solutions from google, but now my confusation is related to which of belowing two r better for my problem.

ALLOW_SNAPSHOT_ISOLATION

READ_COMMITTED_SNAPSHOT

Also I don't know differance about both of them. Please if u have any idea about them then tell me.

Thanks for giving this hint to solve my problem

|||

There is an excellent article about SQL Server 2005 Row Versioning Based Transaction Isolation.Here is the URL:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5snapshotisol.asp

Please go through the article carefully and workout the examples specified in it.It would work for u.

Let me know if have any problems.