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
I am Getting same error -: Transaction (Process ID 162) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
Hi Pinal,
I am recieving this error in a MOSS 2007 Content Database when Alter Index and Update Statistics are executed in a Maintance Plan.
Failed:(-1073548784) Executing the query “ALTER INDEX [missing_index_116] ON [dbo].[AllDocs] REORGANIZE WITH ( LOB_COMPACTION = ON )
” failed with the following error: “The indexes on table “AllDocs” cannot be reorganized because there is already an online index build or rebuild in progress on the table.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
Next Step in Maintenance Plan:
Failed:(-1073548784) Executing the query “UPDATE STATISTICS [dbo].[AllDocs]
WITH FULLSCAN
” failed with the following error: “Transaction (Process ID 157) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.
What coul be the problem here?
Thanks in advance
Great blog
I have a select statement and id returned by the select statement i am giving to update statement in procedure i am executing It in a stored procedure with no lock and row lock in code i am using Transactions and thread pool to. I am getting dead lock problem can u please suggest me.
As I stated in an earlier reply on this thread: This error is nothing to do with lock contention, it is to do with resources.
There is a subtle difference in the wording of the error which means you are short of memory, disc or threads.
Find out what is stressing your server and sort it out.
It could be badly written SQL, the need of an index or just the need for bigger KIT.
If a particular statement look at how it can be improved e.g. using temp tables
My exact error is “Transaction (Process ID 69) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”
And when i execute “sp_lock” i am getting a huge number of records when i am executing this application. My application contain threads and each thread executes for loop to execute procedure. So each thread executes loop around 2 to 10 times procedure and there will be minimum 4000 threads.
Sounds like you have too much of everything. Even too many locks. Can you not load data into a work table and then execute a more complex procedure less times to process the queued data.
Thanks for Replying I dont know about work table and i am using sql connection in c# please suggest me how to use work table to solve this issue. I used semaphores to control thread pool but i am unable to control.
Thanks for replying me. Do u want me to load table in to temp table and then search in temp table to reduce the lock on the table.
I am using Classic asp and SQL server 2000.
For Update this error is show :-
Microsoft OLE DB Provider for ODBC Drivers error ‘80004005’
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 67) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
I am using Classic asp and SQL server 2000.
For Update this error is show :-
Microsoft OLE DB Provider for ODBC Drivers error ’80004005′
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 67) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Hello Pinal,
I want to know about Tracing of Deadlocks. Is there any way to find the deadlocks in database using simple query or store procedure ? I do not want to use SQL Profiler.
Thanks in Advance
I converted my asp code to asp.net code and in asp didnt get deadlocks but in .net I am getting deadlock errors all over – I strongly believe the ado.net driver is causing this not the code – unless pooling is the cause?
Run-time error ‘-2147467259(80004005)’:
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server
please give solution !
thnks …
Run-time error ‘-2147467259(80004005)’:
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find server
having problem with that to connect database from aplication .. beginner used MYSQL Server2005
thank.. for solutions !
You need to make sure if the server is connected via network
If you know that you have a function/SP/trigger that will take a while to execute and may cause a deadlock, you can manually lock the critical section using the application lock procedures in SQL Server:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-getapplock-transact-sql?view=sql-server-2017
Hi Pinal,
when deadlock continuously occurred for particular databse.In this time can we have change to Isolation level.The default isolation is Readcommited.
can we changed Readuncommited isolation level when deadlock occured continuously.
Please give me reply.
Thanks,
Satya
Hi,
I’m using temp table in my stored procedure and it returns two tables. its a dynamic query in stored procedure. I’m using asp.net & c#.net and sql server 2008. I’m getting deadlock as the error goes like this.
Transaction (Process ID 148) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
kdivya
Transaction (Process ID 202) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Retry the update command when you get error 1205 like below. This will retry the loop for 3 times.
DECLARE @Retry INT
DECLARE @ErrorNo INT
RETRYUPDATE:
BEGIN TRY
BEGIN TRAN
SQL
COMMIT TRAN
END TRY
BEGIN CATCH
SET @ErrorNo = ERROR_NUMBER()
ROLLBACK TRAN
IF (@ErrorNo = 1205 )
BEGIN
IF(@Retry <= 3)
BEGIN
SET @Retry = @Retry + 1
WAITFOR DELAY '00:00:00.08' — Wait for 5 ms
GOTO RETRYUPDATE — Go to Label RETRYUPDATE
END
END
END CATCH
Hi Pinal,
I run a report and it was taking more time and then I stop run away reports. Usually, they are in the RB Query tool and have hit the Run Query icon. They just use the RedX to close the query tool. That seems to be what is leaving the SQL hanging out on the server running for days, thus, blocking ETL. How to kill those process by automated way? When ETL process is running I wanted to give first priority for ETL process and then report or any .. How can we automated this?
Thanks in advance.
Chandu
Add OPTION (MAXDOP 1) for communication buffer deadlocks
I have this error being thrown every morning when I have most transactions happening with few seconds.
My stored procedure looks like this:
create procedure dbo.sp_storproc
(
@ServerID varchar(32),
@Key varchar(32),
@Values varchar(32)
)
as
set nocount on
declare @Error int, @RowCount int
update myTable with (RowLock)
set Values = @Values
where ServerID=@ServerID and Key = @Key
select @RowCount = @@ROWCOUNT
select @Error = @@ERROR
if @Error0
begin
return -1
end
if @RowCount = 0
begin
insert into myTable with (RowLock) (ServerID, Key, Value)
values (@ServerID, @Key, @Values)
select @Error = @@ERROR
if @Error0
begin
return -1
end
end
what is happening, I have same application running on 5 servers and trying to run this sp at the same time,
sometimes I can see in about 2500 rows are inserted per second, but sometimes I can see that only 1 record
is inserted and the error log shows me that deadlock exception happened exactly at that same second.
Each server passes it’s own @ServerID parameter, so there is no contention for the values accross the servers.
Please advise on the solution of how to avoid deadlock and let all records to be inserted, because usually
out of 9000 records that needs to be inserted with 10 or 5 seconds there is 5 or 6 records that are not
inserted and they’re in my exception log.
Hi Pinal,
Am facing this deadlock error “1205” on a SELECT statement using Datastage ETL tool. Any help would be appreciated.
I’m surprised that no one here has mentioned or suggested using READ COMMITTED SNAPSHOT… You may want to read up on it. It’s a simple database setting that will help a lot of you out big time…
My friend and I share an apple ID. Does this have anything to do with it? if so how do I get rid of this error? thanks
i m getting dis error n want a solution on how to resolve it.