SQL SERVER – Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction

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 (http://blog.SQLAuthority.com)

About these ads

86 thoughts on “SQL SERVER – Fix : Error 1205 : Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction

  1. 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

  2. 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.

  3. 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.

  4. 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.

  5. 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?

  6. 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

  7. 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

  8. Pingback: SQL SERVER - Guidelines and Coding Standards Part - 2 Journey to SQL Authority with Pinal Dave

  9. 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?

  10. 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.

  11. 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.

  12. 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… :)

  13. 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…

  14. 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.

  15. 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

  16. 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

  17. 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 ?

  18. 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…

  19. 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!!

  20. I have a merge replication environment with one host is
    publisher/distributor and one subscriber. The merge uses continuous replication.

    My MSSQL’s are 2000SP4.

    Error Message:

    The process could not enumerate changes at the ‘Publisher’.
    (Source: Merge Replication Provider (Agent); Error number: -2147200999)
    ——————————————————————————-
    Transaction (Process ID 70) was deadlocked on lock resources with another
    process and has been chosen as the deadlock victim. Rerun the transaction.
    (Source: SERVA_SQL (Data source); Error number: 1205)

    Please advise on the above case.

    sai.

  21. OLE DB error: OLE DB or ODBC error: Transaction (Process ID 477) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.; 40001.

    Please provide me ans

  22. I have using sql server 2008 R2 Enterprise Edition.

    in one stored procedure ,i am updating on table A only it won’t touch other object and it working fine from past 3 years.
    but suddenly we are getting below error

    Transaction (Process ID 290) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    here our code touch only on object A nothing else and the error says lock on some communication buffer.

    please help me it is problem of objects or memory

  23. I have finally come to the conclusion that this error simply means you are short of resources on the server. I saw that whereas the server usually used 1.6GB for the SQLServer process this had dropped to 0.7. I re-booted the server and the problem went away. I am keeping a much closer eye on system resources now.

  24. We have some issue with deadlock , coz in a single transaction we r calling around 10 sps.due to that when multiple users try to do the same process,it gets locked.We have used NoLock during the selection.Can we used nested transaction for this?. Could you please give any suggestion for this.

  25. Transaction (Process ID 103) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    i have run this command :SET DEADLOCK_PRIORITY NORMAL but still it is given error.

    please help me about this problem.

  26. Could the “1205″ be a result of indexes during INSERT. We have 2 ETL processes, running in parallel that insert into the same table (no issues with duplication). When we originally created the table we did not have any indexes. The parallel ETL processes did not have any problems. After creating the indexes, whenever we ran the processes, we began encountering the “1205″ issue. We have other similar parallel processes with other tables (with indexes) that don’t encounter the “1205″ but this may be due to the volume of inserts.

  27. Hi
    I have also received same kind of error as Query Exception: Transaction (Process ID 112) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction . We are using a procedure which can be called in the page when many users might be browsing at that time , we are seeing this error in the event logs , i have used deadlock priority to normal but still the issue exists . If any possible solution please let me know . We have indexes on the related tables also accordingly .This issue is using so much of resources that is causing serious errors further after continuous deadlocks happens. thanks in advance.

  28. As I have said before this error is telling you that you are short of resources and is not a simple locking problem.
    Look at intermediate tuple sizes or other processes running at the same time. Use worst query reports to find the issue or is it that your server wont cope with the number of concurrent sessions?

    • Hi! This error happend in my application last week, but I don’t use transactions in this routine code.
      What can it be? Problem in the server? Can anybody help me?

      I don’t know what to say to my customer, I think it’s some problem there, not in my program.

      Thank you!
      Fabiano – Brazil.

        • Yes, our application doesn’t have transactions envolving this table locked. I think is memory issue, we don’t have explicit lockings, so there’s no another explication…

          Thanks!

  29. If deadlock happen what we should we do on the workstations? can anyone please advise? I need some guidance to resolve this issue.

  30. Am also getting the same problem in SQL SERVER 2005, but each time the PROCESS ID is differ.

    Transaction (PROCESS ID 86) WAS DEADLOCKED ON LOCK RESOURCES WITH ANOTHER PROCESS AND HAS BEEN CHOSEN AS THE DEADLOCK VICTIM. RERUN THE TRANSACTION

    Could you please let me know how to fix this resolve this problem this time as well as to knock out this problem in my everynew project.

    Regards,
    Narendran,
    Tweet me at @MyNaren89.
    [phone removed]

  31. Hi !!
    We are facing Deallocks while calling 2 sps in our system from last 2 3 months …the sps have insert, update queries on a set of tables … First time when we got the deadlock, we solved the issue adding NoLock to a SELECT query which was causing the deadlock.. afterwords we started getting deadlocks on another table to which we add Primary Key.. now again the deadlock is appearing on another table .. We cant go and add PKs or NoLocks on every table .. but need some solution.. can anybody help ??

  32. hi,

    i have an update query with the below structure, and i got “Transaction was deadlocked” error how do i resolve, the main concept of the query is i am updating one table values to another table value ( not taking long time to execute)

    UPDATE tableA SET
    tableA.emp_imageURL = tableB.emp_imageURL
    FROM
    (select emp_id,emp_imageURL from Attendance_Entry WITH (NOLOCK) where ident_id=’7767′ ) as tableA
    RIGHT JOIN
    (select emp_id,emp_imageURL=MIN(emp_imageURL) from Permanant_Emp_Details WITH (NOLOCK)
    where emp_id in (select distinct emp_id from Attendance_Entry WITH (NOLOCK) where ident_id=’7767′)
    group by emp_id)
    AS tableB
    ON (tableA.emp_id = tableB.emp_id)

  33. Hi
    i got “Transaction WAS DEADLOCKED ” in update query with below query structure, the concept of query is i am updating one table values from another table values

    UPDATE tableA SET
    tableA.emp_imageURL = tableB.emp_imageURL
    FROM
    (select emp_id,emp_imageURL from Attendance_Entry WITH (NOLOCK) where ident_id=’7767′ ) as tableA
    RIGHT JOIN
    (select emp_id,emp_imageURL=MIN(emp_imageURL) from Permanant_Emp_Details WITH (NOLOCK)
    where emp_id in (select distinct emp_id from Attendance_Entry WITH (NOLOCK) where ident_id=’7767′)
    group by emp_id)
    AS tableB
    ON (tableA.emp_id = tableB.emp_id)

    Regards,
    Elango

  34. Hi … I have the below problem
    Transaction (Process ID 55) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. – 0

    do you know how to fix this error?

    thanks

  35. Hello. Our SQL 2008 server is experiencing this deadlock error:

    2011-11-30 10:47:08.71 spid6s Deadlock encountered …. Printing deadlock information
    2011-11-30 10:47:08.71 spid6s Wait-for graph
    2011-11-30 10:47:08.71 spid6s
    2011-11-30 10:47:08.71 spid6s Node:1
    2011-11-30 10:47:08.71 spid6s OBJECT: 2:1824150806:0 CleanCnt:2 Mode:Sch-S Flags: 0×1
    2011-11-30 10:47:08.71 spid6s Grant List 3:
    2011-11-30 10:47:08.71 spid6s Owner:0x00000002CA27ED00 Mode: Sch-S Flg:0×40 Ref:1 Life:00000000 SPID:70 ECID:0 XactLockInfo: 0x000000030A829340
    2011-11-30 10:47:08.71 spid6s Requested by:
    2011-11-30 10:47:08.71 spid6s ResType:LockOwner Stype:’OR’Xdes:0x0000000212C45970 Mode: Sch-M SPID:70 BatchID:0 ECID:0 TaskProxy:(0x00000002F3B8E510) Value:0x4cc23c0 Cost:(-30/0)

    Does anyone have advice on addressing this error? It appears in the error log file every 10 seconds.

    Thanks,
    Tim

  36. I am Getting same error from application users so like to know how we can reduce the lock time in application level.
    Anyone have idea to solve this error please?

  37. 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

  38. 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

  39. 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.

  40. 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.

  41. 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.

  42. 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

  43. 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?

  44. 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 !

  45. 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

  46. 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.

  47. 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

  48. 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

  49. 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.

  50. Hi Pinal,

    Am facing this deadlock error “1205″ on a SELECT statement using Datastage ETL tool. Any help would be appreciated.

  51. 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…

  52. 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

  53. Thank you for the clear explanation, it really saved me a couple hours and helped to quickly resolved the issue I was having with dead locks queries in my application.

  54. Below was the error failed on schedulind job.
    Executed as user: ANSWERS\administrator. … DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_3 DTSRun OnError: DTSStep_DTSExecuteSQLTask_3, Error = -2147467259 (80004005) Error string: Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1205 (4B5) Error string: Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 … Process Exit Code 1. The step failed.

  55. I am facing the same problem in my UAT environment, the error:

    “Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.”

    doesn’t show up in my DEV environment with the same FrontEnd application, just changing the connection string.

    Do you know what can be causing this behaviour?
    Could be a difference between two database servers?

    • Hi Miguel Ramos Alarcón,

      I’m also facing the same issue. Do you have any updates on this issue?

      Thanks for helping me in this regard.

      • Thanks for your reply Ashok. we have a lack of design in our database. In dev environment we don’t have as much information as we have in PROD server, besides bad coding implemented in our stored procedures, so we had a lot of blocking issues. Let me know if you need anything else.

        MRA

  56. Hi Pinal,

    I am using two stored procedures

    1. Delete a row from table.
    2. Create a new row from existing row.

    When two users called this two SPs concurrently, then i am getting this error message:
    “Transaction (Process ID) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction”

    Sometime its coming for 1st SP and Sometime its coming for 2nd SP.

    Please suggest.

  57. Hi Pinal,
    Could you please help me in this issue, I have the following error. and I need to know where the deadlock is occurred.

    Below is the error:
    Transaction (Process ID 84) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    Below is the procedure:
    ALTER PROCEDURE [dbo].[DEGLOCK_PROC]
    (
    @P_ID int out,
    @P_A int,
    @P_B varbinary(max),
    @P_C varchar(50),
    @P_D varchar(50),
    @P_E varchar(50),
    @P_F varchar(50),
    @P_G varbinary(max),
    @P_H int,
    @P_I int,
    @P_J varchar(10),
    @P_K bit,
    @P_L int,
    @P_M int,
    @P_N int,
    @P_O int,
    @P_P VARCHAR(50),
    @P_Q int,
    @P_R xml,
    @P_S xml,
    @P_T nvarchar(max),
    @P_U nvarchar(max),
    @P_V nvarchar(200)

    )
    AS
    BEGIN
    SET XFT_CORT ON;

    DECLARE @X varchar(10), @Y int

    SELECT
    @X = X,
    @Y = Y
    FROM dbo.FOO
    WHERE
    A = @P_A
    AND L = @P_L
    AND H = @P_H
    AND I = @P_I
    AND N = @P_N
    AND O = @P_O
    AND (@P_D IS NOT NULL AND D = @P_D)

    BEGIN TRANSFTION

    IF @Y IS NULL
    BEGIN
    INSERT INTO dbo.FOO
    (
    A,
    B,
    C,
    D,
    E,
    F,
    G,
    H,
    I,
    J,
    K,
    L,
    M,
    N,
    O,
    P
    )
    SELECT
    @P_A ,
    @P_B ,
    @P_C ,
    @P_D ,
    @P_E ,
    @P_F,
    @P_G ,
    @P_H ,
    @P_I ,
    @P_J ,
    @P_K,
    @P_L,
    @P_M,
    @P_N,
    @P_O,
    @P_P

    SET @P_ID = SCOPE_IDENTITY();
    END
    ELSE
    BEGIN
    IF CAST(@P_J AS DECIMAL)> CAST(@X AS DECIMAL)
    BEGIN
    UPDATE dbo.FOO
    SET
    X = @P_J,
    K = @P_K,
    B = @P_B,
    C = @P_C,
    E = @P_E,
    G = @P_G,
    M = @P_M
    FROM dbo.FOO
    WHERE ID = @ID
    END
    SET @P_ID = @ID

    END

    IF EXISTS(SELECT 1 FROM dbo.Bar WHERE ID = @P_ID)
    BEGIN
    UPDATE dbo.Bar
    SET
    AA = @P_R,
    AB = @P_S
    WHERE ID = @P_ID
    END
    ELSE
    BEGIN
    INSERT INTO dbo.Bar
    (
    ID,
    AC,
    AA,
    AB
    )
    SELECT
    @P_ID,
    @P_Q,
    @P_R,
    @P_S
    END

    INSERT INTO dbo.BAZ
    (
    ID,
    AD,
    AE,
    AF
    )
    SELECT
    @P_ID,
    @P_T,
    @P_U,
    @P_V
    WHERE NOT EXISTS
    (
    SELECT 1 FROM dbo.BAZ WHERE ID = @P_ID
    )

    COMMIT TRANSFTION
    END

  58. Hello,

    I am stuck with an issue. I have written a stored procedure, which has few insert into statements, updates and deletes.
    Even If one of the tables used in the sp does not exists in the Database, the stored procedure gets created successfully, but at the time of execution it generates Deadlock.
    How can i handle this scenario ?

    Thanx in advance !

  59. I’m having this problem with my procedure
    Could you help me?

    USE [rvsJITDB]
    GO
    /****** Object: StoredProcedure [dbo].[INSERT_CNTDELIVERY] Script Date: 10/24/2014 09:54:57 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER PROCEDURE [dbo].[INSERT_CNTDELIVERY] AS

    – Insert into CNTDelivery la trascodificación definida.

    INSERT INTO CNTDELIVERY (
    –CNTMANAGER & GOODSCNT
    CNTID, BAY_NO, BOX_NO,BOX_QTY,
    –CNTDELIVERY_TEMP
    ORDID, BUILDER, PLANT, LINE, LINEPT, JITORDER, SEQUENCE, MODEL, FEATUREORDER, CHASSIS, RECEPT_DT, PROCESS_DT,
    DELIVERY_DT, PART_QTY, TYPEREG, INTERCHANGE_ID, CALLOFF_ID, ADDCALLOFF, SUPPLIER_ID, SYNCHRO_ID, SPECIALDATA,
    PILOTRUN_ID, CUSTOMPN, PARTGROUP_ID, PARTTYPE_ID,PROVIDER_ID, SUPPLYPT, QUANTITY,
    – GOODSATTRIBUTE
    LOCALPN,DESCRIPTION,
    MNG_CTL,LBL_CTL,CNT_CTL,DATESEQ,STATUS,BAY_SIDE,NCOPY )

    SELECT DISTINCT
    –RTRIM(B.CNTID), – JIT
    (CASE
    WHEN B.CNTID IS NULL THEN ‘NC’ –SECUENCIA NORMAL
    ELSE RTRIM(B.CNTID)
    END) AS CNTID,
    0, – BAY_NO
    0, – BOX_NO
    1, – BOX_QTY
    A.ORDID, – ORDID
    A.BUILDER, – BUILDER
    A.PLANT, – PLANT
    A.LINE, – LINE
    A.LINEPT, – POINT
    A.JITORDER, – ORDER
    A.SEQUENCE, – SEQUENCE
    A.MODEL,
    A.FEATUREORDER,
    A.CHASSIS,
    A.RECEPT_DT, – RECEPTION DATE
    A.PROCESS_DT, – PROCESS DATE
    A.DELIVERY_DT, – DELIVERY DATE
    A.PART_QTY, – Nº PARTS NUMBER BY JITORDER
    A.TYPEREG,
    A.INTERCHANGE_ID,
    A.CALLOFF_ID,
    – G.PRN_ORD,
    A.ADDCALLOFF,
    A.SUPPLIER_ID, – SUPPLIER (STATIC)
    A.SYNCHRO_ID,
    A.SPECIALDATA,
    A.PILOTRUN_ID,
    A.CUSTOMPN, – VW REFERENCE
    A.DESCRIPTION, — DESCRIÇÃO
    A.PARTGROUP_ID,
    – A.PARTTYPE_ID,
    A.PROVIDER_ID,
    A.SUPPLYPT, – SUPPLY POINT (STATIC)
    A.QUANTITY, – QUANTITY (1)
    CASE WHEN G.LOCALPN IS NULL THEN A.CUSTOMPN
    ELSE G.LOCALPN
    END AS LOCALPN,
    — G.DESC1_DESC AS DESCRIPTION,
    CASE WHEN G.DESC1_DESC IS NULL THEN ‘SEM DESCRIÇÃO’
    ELSE G.DESC1_DESC
    END AS DESCRIPTION,

    0, – MNG_CTL
    1 AS LBL_CTL, – LBL_CTL
    0, – CNT_CTL
    CONVERT(VARCHAR,LEFT(’000′,3-LEN(DATEPART(DAYOFYEAR,A.RECEPT_DT))))
    +CONVERT(VARCHAR,DATEPART(DAYOFYEAR,A.RECEPT_DT)) + CONVERT(VARCHAR,LEFT(’0000′,4-LEN(CONVERT(INT,A.SEQUENCE))))
    + CONVERT(VARCHAR,A.SEQUENCE),
    G.PRN_ORD, –STATUS
    ‘A’,
    1
    –AÑADE FILTROS A GOODSCNT
    FROM CNTDELIVERY_TEMP A
    LEFT OUTER JOIN GOODSCNT B
    ON A.CUSTOMPN LIKE RTRIM(B.LOCALPN) –AND CONVERT(VARCHAR,A.LINE) LIKE B.LINE
    INNER JOIN CNTMANAGER C ON
    ((B.CNTID IS NULL AND ‘NC’=C.CNTID)
    OR
    (B.CNTID IS NOT NULL AND B.CNTID=C.CNTID))
    AND A.LINE=C.LINE
    LEFT OUTER JOIN GOODSATTRIBUTE G ON A.CUSTOMPN = G.CUSTOMPN
    INNER JOIN ADMPRINTER P ON A.CUSTOMPN LIKE P.CUSTOMPN
    /*WHERE
    (SELECT COUNT(*) FROM GOODSCNT K WHERE A.CUSTOMPN LIKE RTRIM(K.LOCALPN) AND CONVERT(VARCHAR,A.LINE) LIKE K.LINE AND K.FILTERED=1)=0
    */
    –COMENTAR CUENDO FUNCIONE MARKCNT PARA DOBLE BOLSA
    –EXEC INSERT_CNTDELIVERY_TRATA_DOBLE_BOLSA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s