Deadlocked on transaction - 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

Old Forums

CLOSED - General SQL Server

Deadlocked on transaction

Author

Topic

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2005-02-05 : 08:52:11

what is the reason of following error ?
how can control / avoid this error ?

Server: Msg 1205, Level 13, State 61, Line 1
Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

when I run following query in query analyzer.

select a.*, b.docdate, b.fromorig, b.docdate1, b.attachment, b.doctype, b.docno, b.docto, b.filenoa
from livelink.dtree a, mateenm.PROJDOCSATTRS_allin b, mateenm.projattrs_allin c
where a.dataid = b.id and a.dataid=c.id and c.regionname='Attr_16336_5'
ORDER BY docdate1 desc, fromorig, CAST(c.vallong as Varchar(1000))

The above query normally use in livelink software.
in livelink software is also take time to retrieve the records
from sql server database.

what is the reasons of deadlock on transaction ?
how can control / avoid the error ?

regards.

Mateen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-05 : 09:27:39

SqlServer's Books on line (BOL) has a lot of information available about deadlocks.

According to your error msg, your deadlock is a result of "lock" resources as opposed to "thread" or "com buffer" resouces.
The (very) general reason for a "lock" deadlock is:
process 1 has a lock on resource A and is requesting a lock on resource B
process 2 has a lock on resource B and is requesting a lock on resource A

BOL:

quote:

To help minimize deadlocks:

Access objects in the same order.
Avoid user interaction in transactions.
Keep transactions short and in one batch.
Use a low isolation level.
Use bound connections.

Minimizing Deadlocks topic in BOL details each of these suggestions.

Be One with the Optimizer
TG

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2005-02-06 : 09:36:28

Thanks for your response.

I get help from book online.

In using Bound Connections I run
sp_getbindtoken
sp_bindsession

procedures, but query retrieve record time reduce
19 seconds to 17 seconds only.

In my database srv_getbindtoken procedure not available.

there is any ways I can use something in my query to avoid the
deadlock ?

How I can improve the query quick retrieval time ?
query quickly retrieve the records.

in sql query analyzer query take 17 seconds to retrieve the
records and same query take 30 seconds to retrieve the records
when it run by livelink (open text) software.

any help.

regards.

Mateen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-06 : 11:06:26

I don't have any personal experience with "bound connections". But if your application is expecting to use a bound connection to avoid locks but sp_getbindtoken is not being called, that could definately be your problem. As I understand it, bound connections allows 2 seperate processes participate in the same business transaction without locking each other out. Is that what your are doing?

quote:
How I can improve the query quick retrieval time ?
query quickly retrieve the records.

Without regards to bound connections, one possibility to speed up your db call would be to localize the sql code by making it into stored procedure and use live link (or any connection) to exec the procedure instead of executing the code remotely.
edit:

quote:
there is any ways I can use something in my query to avoid the
deadlock ?

If you don't mind "diry reads" you could set the transaction isolation level to Read Uncommitted.
(or use "with(nolock)" hints in your from clause)

~~You could also post your code to get optimization advice.~~
edit: i see you posted your code in previous post

Have you read the BOL topics:
Using Bound Connections
When to use Bound Connections

Be One with the Optimizer
TG

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2005-02-07 : 05:02:41

thanks for response.

I want to confirm that when I run the
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
ie

USE pubs
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
SELECT au_lname FROM authors WITH (NOLOCK)
GO

1. How I can revers the SERIALIZABLE ?
ie. How I can go back to my previous setting ?

I am not sure what will be effect in my other Queries.
I have problem in only few Queries, which slow retrieived the records

2. nolock will show dirty records (deleted records)
it should not to show dirty records

3. how can make Query (above query) into stored procedure ?

regards.

Mateen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-07 : 08:46:45

Serializable is the most restrictive isolation level. I would think the default (Read Committed) would be sufficient. check out isolation level topics in BOL for complete descriptions.

1. first of all, when you explicitly change the isolation level using a "set" command, that change only affects the current session. ie: that query analyzer window, or the SP that that contains the command. It doesn't affect other sessions.

You can reset the level the same way, using the Set command. But you only need to re-set it if your subsequent statements (in the same session) require a different setting.

2. It won't show "deleted" records, but it will show records that are currently being deleted by other users. I just want to make sure you understand the difference.

3. first of all, in your statement you have a "BEGIN Transaction" but all you are doing is a select statement. There is no transaction taking place in a select statement. Furthur any time you explicitly BEGIN a transaction you should also make sure you either COMMIT or ROLLBACK that transaction. It will definately mess things up if you leave open transactions out there. There are only a few specific situations where you want to explicitly use "begin Tran" and "Commit/Rollback Tran". The most common is when you have several statements that are performing inserts, updates, and/or deletes where you want all of them to succeed or all to roll back. Otherwise don't even use explicit transactions. SqlServer will implicitly use transaction control on all transactions performed.

Here is an exaple of your code as an SP:


USE pubs
GO

If Object_ID('MyStoredProcedure') > 0
	drop proc MyStoredProcedure
GO
Create Procedure MyStoredProcedure

as

--This isolation level setting is being overridden by your hint below "with (nolock)"
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

/*This statement shouldn't be here.  You aren't performing any transactions.*/
--BEGIN TRANSACTION


SELECT au_lname FROM authors WITH (NOLOCK)

GO

Be One with the Optimizer
TG

mateenmohd
Constraint Violating Yak Guru

297 Posts

Posted - 2005-02-08 : 05:02:08

thanks for your nice response.

I want to clear one more thing that when I run the following

USE pubs
GO

If Object_ID('MyStoredProcedure') > 0
drop proc MyStoredProcedure
GO
Create Procedure MyStoredProcedure
as

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

select a.*, b.docdate, b.fromorig, b.docdate1, b.attachment, b.doctype, b.docno, b.docto, b.filenoa
from livelink.dtree a, mateenm.PROJDOCSATTRS_allin b, mateenm.projattrs_allin c
where a.dataid = b.id and a.dataid=c.id and c.regionname='Attr_16336_5'
ORDER BY docdate1 desc, fromorig, CAST(c.vallong as Varchar(1000))
WITH (NOLOCK)

GO

ie.

If Object_ID('MyStoredProcedure') > 0
drop proc MyStoredProcedure

it drop my procedure, it means there are locking in the transaction
if there is no lock on the transaction (query) ie
Object_ID('MyStoredProcedure') = 0
then it will not drop the procedure ?

regards

Mateen

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-08 : 08:06:47

dropping and re-creating the procedure has nothing to do with what the procedure is doing. You only want to create the stored procedure 1 time. Once it has been created (which is all this statement does) you simply need to "execute" it. However, if you want to change the procedure sometime you would either need to "alter" the procedure or "drop" and re-create the procedure first. The "If" statement simply checks for the existance of the procedure before it tries to create it. The statement (above) creates the procedure, it does not run it.

To execute the procedure:
exec MyProcedure

Please be sure to read BOL topics: Sql Stored Procedures, Create Procedure, and Alter Procedure.

Ok, now for your SP code:
your "with (nolock)" is a Table Hint, not a query hint. So it needs to be after all tables that you want to read uncommitted data. In the code below, all the code in the SP will have the isolation level set to serializable, but just table b overrides that setting and uses read uncommitted. Also its a good idea to start using the new JOIN syntax as I have done below.


Set Transaction Isolation Level Serializable

select	a.*, 
	b.docdate, 
	b.fromorig, 
	b.docdate1, 
	b.attachment, 
	b.doctype, 
	b.docno, 
	b.docto, 
	b.filenoa 
from	livelink.dtree a

JOIN	mateenm.PROJDOCSATTRS_allin b with (nolock)
		ON a.dataid = b.id 

JOIN	mateenm.projattrs_allin c 
		ON a.dataid=c.id 

where	c.regionname='Attr_16336_5' 

ORDER BY 
	docdate1 desc, 
	fromorig, 
	CAST(c.vallong as Varchar(1000))

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources