Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other’s object. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.
Fix/Workaround/Solution:
Deadlock priority can be set by user. In other words, user can choose which process should stop to allow other process to continue. SQL Server automatically chooses the process to terminate which is running completes the circular chain of locks. Sometime, it chooses the process which is running the for shorter period then other process.
To reduce the chance of a deadlock:
- Minimize the size of transaction and transaction times.
- Always access server objects in the same order each time in application.
- Avoid cursors, while loops, or process which requires user input while it is running.
- Reduce lock time in application.
- Use query hints to prevent locking if possible (NoLock, RowLock)
- Select deadlock victim by using SET DEADLOCK_PRIORITY.
SQL SERVER 2005 has new priority HIGH as well as numeric-priority.
SQL SERVER 2005 Syntax
SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
<numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
Example:
The following example sets the deadlock priority to NORMAL.
SET DEADLOCK_PRIORITY NORMAL;
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
94 Comments. Leave new
Got the exact error but unable to resolve as I am trying to shink the database
Hi..
I have also received the Error “Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction”
.
could you please tell me how to fix this error. I am using two diffeent VB Applications whis use same SQL Server 2000 database.
I have found the one process which became a victim. Could you please let me know where to set up the DEADLOCK_PRIORITY as i am new in SQL server 2000. I really appreciate your help.
Regards
Nitesha
Hi..
I have also received the Error “Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction”
.
could you please tell me how to fix this error. I am using two diffeent VB Applications whis use same SQL Server 2000 database.
I have found the one process which became a victim. Could you please let me know where to set up the DEADLOCK_PRIORITY as i am new in SQL server 2000. I really appreciate your help.
Regards
Nitesha
Hi,
Could u please tell me how this error occured .Were you using 2 workspaces and trying to access the database one after the other.
Hi Raj,
You can understand by this example:
spid1 : locked record A
spid2 : locked record B
Now suppose spid1 need record B but it can not get as it is locked by spid2. So starts waiting to end spid2. If spid2 also need record A, it would also start waiting to end spid1. In this case both processes are waiting to end another one and not releasing there own locked record.
Such case of circuler blocking is called Deadlock.
Regards,
Pinal Dave
Hi,
I got dead lock issue, in which i try to save new data in my application by multiple user its dead lock and sp_who2, sys.sysprocesses or errorlog is not healping and in application no of users are more so how to resolve permanently, cant set deadlock_priority.
This is the simplest, but best explanation of what is happening I’ve read!
Pinal, you have an awesome website! I am a novice at SQL and I have run into this issue:
System Error System Error DB: Exception encountered. Table: PATIENT/STUDY Operation: FINDFIRST Id: Error: Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
State:40001,Native:1205,Origin:[Microsoft][ODBC SQL Server Driver][SQL Server]
How do I fix this, what is happening is the Dicom service which is reporting this is stopping once a day with this message and it is critical because it is a hospital and this service need to run non-stop. Could you please help?
Thank you in advance.
Can I find out which users are using which process?
Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other’s object.
I have a feeling that one of the users does not need to be there. I logged on with two seperate accounts and setup replication.
The first, which I used the wrong account might be in there still.
Hi Randy!
You can use the stored procedure SP_WHO2
Regards.
MS SQL 2005 is supposed to be a multi-user database, unlike Access, so why isn’t it smart enough to queue up the requests and process them in a linear fashion if it’s otherwise going to cause a deadlock?
I’ve got a SP that takes less than 300ms to run consisting of a few selects, updates and inserts. I saw the deadlock error on 4 computers when users tried to run the same SP (with different params) within a second or two of each other. Only 1 person didn’t get an error. SQL should just queue the requests to avoid creating deadlock victims. I’m very annoyed with it, I thought it was smarter than that. It should be for what it costs. :-(
actually sql is smart. unfortunately people who write sql queries are not. dead locks mostly happen because of queries that take tooo long to execute whatever the reason might be.
it can be missing indexes, cursors no optimizer hints etc etc.
also, deadlocks happen because unfortunately 2 users are trying to access an object at the same time. its unfortunate….. doesnt happen often. and how would sql know that 2 people are planning to use an object at similar times? and if sql starts queuing the transactions and checking the objects all the time before executing them, just to save a dead lock from happening, imagine how it would be if its a prod server with billions of transaction happening every day?
Hi , me facing the same error , We are running stored procedure one by one with out keeping a time gap, now the problem is that i have to track where in the procedure deadlock is coming .. i have to figure out that so that we can fix that part of coding ..
Its real urgent do help..
If possible send the Solution in my mail ashish_vbtecno@indiatimes.com
Hi,
I am observing this error when i do a set of action in application using SQL Server 2008 Database.
SQL Message: Transaction (Process Id ??) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I understand what deadlock is, but I am not able to relate it to the action in Application.
If the deadlock happen when it was doing Insert into table, does that tell something about deadlock
I’m receiving this with my accounting package, generally when posting batches. If I understand this correctly, the SQL server is telling the application that it’s not able to write its transaction at this time.
What the application SHOULD do at this point, is to pause and attempt to re-post its data to the SQL server after a short delay. Would that be correct?
Dear Friends,
I am using SQL 2000 with RAMCO ERP, HP DL360G5 Server with 8GB RAM, RAID 5, HDD 4x15000RPM & utlization 7.74GB RAM Total Database size 70GB but too much blocking happned in my server. After Restart server will functional normal for 4 to 5 hours after again reduced performance. SP4 with HOtfix all installed in server Setting
name minimum maximum config_value run_value
———————————– ———– ———– ———— ———–
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 10 10
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 51 51
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 4 4
max server memory (MB) 4 2147483647 7449 7449
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 6666 6666
min server memory (MB) 0 2147483647 3071 3071
nested triggers 0 1 1 1
network packet size (B) 512 32767 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 1 1
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 0 0
set working set size 0 1 1 1
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
Please help me how to reduce blocking.
I am working with SQL SERVER 2008.I am facing the same issues of deadlock in the database in specific two stored procedures.Both of them has few updates and inserts in the physical tables of the database.I have also used temporary table variable to store data temporarily and have then to fetch it in the same stored procedure.My procedures are being called from .net 2.0. I am using the database isolation level as read committed. I have also used proper transaction blocks and try -catch handler in my stored procedure.The issue is only geting replicated in the production environment which has 250 users accessing the system concurrently.Can anyone please suggest the cause and the fix of this issue.
To the moderaor,
Please ensure that special characters (like etc) are blocked in your site. Malicious code could be injected into this site by hackers.
Regards,
A Well Wisher… :)
Hi every one….
i am developing one sever- client that means one centralized application using vb.net and sql server 2000.
so i have a major doubt in concurrent transactions…. because in my project there are two clients and one server are available…. so how can i control the concurrent transactions and how can i resume the transactions which are trying to execute in time of another transaction execution…..
so pls tell me how to handle this situations
i have only a few days…
It would be helpful if you posted how to see what DEADLOCK_PRIORITY is already set to AND how to see what is dead lock. I have a stored procedure that’s getting this error. It was working at one point but now it is not. Nothing has changed as far as I know at this time.
Hi All,
I am facing a deadlock issue in SQL Server 2008, few stored procedures when call concurrently are giving the deadlock.
Have digged in and found that a single table with Clustered index on PK & no index on FK is involved in all deadlock.
A delete statement is triggered in SP on the table with rowlock hint.
Please suggest
Thanks in advance
Hello Smita,
Create an index on FK table. That would make the delete process faster and reduce the possibility of deadlocks.
If this not solve the issue than please share the code structure and details of deadlock.
Regards,
Pinal Dave
I havea bunch of stored procedures. In all stored procedures, I get only one lock at a time, using sp_getapplock, and then release it using sp_releaseapplock. Theoritically there’s no way I can get a deadlock, because I request only one lock, and if aqcquired I don’t request another. It means the stored procedures will wait one another. But I still get a deadlock, is that possible and why ?
Hi Pinal…
Thanks for your blog !
Another “strange” case with deadlock :
2 statements and a beautiful deadlock 1205 message at this arrival:
statement 1 :
BEGIN TRAN
UPDATE mytable SET field=NULL where x=45
statement 2:
UPDATE mytable SET field=NULL where x=45 — here i’m locked,
sounds good…
and now ….
statement 1:
UPDATE mytable SET field=NULL where x=45
statement 2:
Transaction (Process ID xxx) was deadlocked on lock resources with
another process and has been chosen
as the deadlock victim. Rerun the transaction.
It seems statement2 acquired a lock also ?
Why did I do that while it is blocked itself by the begin tran ?
If I try this under Sybase 15.0, I don’t have this message…
On a clients machine I started getting 1205 on horribly complex SP which has been running for 8 years. I guess the volumes have climbed. So I start down then path of re-engineering the proc but I found 2 things.
1. the proc fails when run in a test database (copy of live) with no other users.
2. Much simpler month end proc has started failing with 1205. This is run by SQL Agent 23:00 Sunday evening with no other users – I have a bouncer proc to make sure of that.
This makes NO SENSE!!