September 23, 2005 at 4:22 am
"Transaction (Process id 119) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
I am updating four tables through a stored procedure. I have put the stored procedure in a begin and commit transaction block. Since this insertion is a lengthy process it takes some time to commit.
Now when any other user try to update data in these table. It gives the above mentioned error.
Please help.....
Thanks in advance.
September 23, 2005 at 5:10 am
I think you must modify index in the table, try to make unique or primary key, because the lock process only will lock 1 record. If you don't define index in every table, SQL will lock one Table (it's means all record)
September 23, 2005 at 7:43 am
Not necessarily. If the update affects a large portion of the table there's a good chance that SQL will take page, extent or even table locks.
Sanjeev, can you post the definition of the tables involved, the indexes on them and the updates that your doing?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 23, 2005 at 9:38 am
Have you taken a look at the deadlock output after turning on the deadlock trace flags: 1204, 1205 and 3605 ?
That will give you more information on what is going on based on which you can take corrective action.
September 26, 2005 at 3:24 am
Try the code below it is what is recommended by my book SQL Server 2000 A beginner's guide by Dusan Petkovic. But this code is for SQL Server 2005 so test it. The key is to write a conditional statement that will return SQL Server @@ ERROR 1205 which is Deadlock. Run a search for SET DEADLOCK_PRIORITY in the BOL (books online). Try the link below for more about the sample code.
Hope this helps.
CREATE PROCEDURE DeadLock_Test AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET DEADLOCK_PRIORITY LOW
DECLARE @Err INTEGER
DECLARE @ErrMsg VARCHAR(200)
RETRY:
BEGIN TRY
BEGIN TRANSACTION
UPDATE tblContact SET LastName = 'SP_LastName_1' WHERE ContactID = 1
UPDATE tblContact SET LastName = 'SP_LastName_2' WHERE ContactID = 2
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SET @Err = @@ERROR
IF @Err = 1205
ROLLBACK TRANSACTION
INSERT INTO ErrorLog (ErrID, ErrMsg) VALUES (@Err, 'Deadlock recovery attempt.')
WAITFOR DELAY '00:00:10'
GOTO RETRY
IF @Err = 2627
SET @ErrMsg = 'PK Violation.'
IF @ErrMsg IS NULL
SET @ErrMsg = 'Other Error.'
INSERT INTO ErrorLog (ErrID, ErrMsg) VALUES (@Err, @ErrMsg)
END CATCH
http://www.campbellassociates.ca/blog/CategoryView.aspx?category=SQL%20Server
Kind regards,
Gift Peddie
Kind regards,
Gift Peddie
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply