Deadlock Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Deadlock Issue

Author

Topic

vijayreddy1243
Starting Member

2 Posts

Posted - 2010-05-07 : 05:52:05

I was facing some serious deadlock issues in a high transaction table.

For some select statement i have nolock .

But for few i can not put nolock, as i need accurate data.
this select statement becomes a deadlock victim at times.

can someone throw some light on this please ?

Thanks,
Vijay

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-07 : 07:43:55

quote:
Originally posted by vijayreddy1243

I was facing some serious deadlock issues in a high transaction table.

For some select statement i have nolock .

But for few i can not put nolock, as i need accurate data.
this select statement becomes a deadlock victim at times.

can someone throw some light on this please ?

Thanks,
Vijay

Did you forget to post the select statement? :)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

Sachin.Nand

2937 Posts

Posted - 2010-05-07 : 07:46:54

Set your transaction isolation level read commited snapshot.

PBUH

vijayreddy1243
Starting Member

2 Posts

Posted - 2010-05-07 : 08:22:13

if i set isolation level read commited , its causing deadlock

Thanks,
Vijay

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-05-07 : 10:19:00

There is a good chance that setting the database to read_committed_snapshot will resolve deadlocks on selects. You should make sure you understand how it works before you implement it.

Using Row Versioning-based Isolation Levels
http://msdn.microsoft.com/en-us/library/ms179599.aspx

Using Snapshot Isolation
http://msdn.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx

Commands to set the database to read_committed_snapshot should be run with no users connected to the database.

use master
alter database [MyDatabase] set allow_snapshot_isolation  on
alter database [MyDatabase] set read_committed_snapshot on

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources