Deadlock Issues in Sql Server - 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

General SQL Server Forums

New to SQL Server Programming

Deadlock Issues in Sql Server

Author

Topic

amruta488
Starting Member

1 Post

Posted - 2012-01-06 : 02:23:52

I have two query one is updating table and another is selecting information from table at a time.

If you try first query in sql server
Query1:
begin tran
update tAsset set UpdatedBy='admin'

then fire below query
Query2:
begin tran
select * from tAsset

then above query wait until update has lock on it.
So, I have use as below

Query3:
begin tran
select * from tAsset with(nolock)
it works fine for me.

I have problem in Query4 and Query5
Query4:
begin tran
update tAsset set UpdatedBy='admin'

Query5:
begin tran
update tAsset set UpdatedBy='admin'

Query4 is execute but Query5 is wait until Query4 has release lock.

How to handle this scenario in sql query?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-06 : 04:42:20

Do you actually have a deadlock, or just blocking?

p.s. Do you know what nolock does? Hint, it's not a 'go faster' switch. See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

--
Gail Shaw
SQL Server MVP

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-06 : 06:46:09

quote:

stuff
.....
How to handle this scenario in sql query?

In only the order I thought of them. Some are much more important than others:

1) Don't hold transactions open any longer than you need to
2) Make sure your operations are as fast as possible given other constraints (disk space / maintenence). This means good INDEXING.
3) try a different ISOLATION LEVEL (try READ_COMMITTED_SNAPSHOT if on 2005 or better)
4) Database design -- in a high throughput OLTP scenario then you want to be as normal as possible. Google NORMALISATION if that doesn't mean anything to you
5) Multi Threaded application design (Locking / Actor model / whatever -- basically be smarted when issuing requests)
6) ...
7) ...
8) ...
9) ...

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-06 : 10:07:12

0) Optimise the queries and tune the indexes so that SQL can take minimal locks and hold them only for a very short time.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources