deadlock

2 Topics

Transaction was deadlocked on lock resources with another process ??
Hi, We have a cognos datastage job that updates a table. It fails with an error that the table is locked., see below ini_PerformanceManagement_tblEmployees,0: ODBC function "SQLExecute" reported: SQLSTATE = 40001: Native Error Code = 1,205: Msg = [Microsoft][ODBC Driver 17 for SQL Server][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. (CC_OdbcDBStatement::executeUpdate, file CC_OdbcDBStatement.cpp, line 1,058) Anyone any ideas about how to resolve this? Thanks for your time, Ollie
Ollie
Jun 12, 2025 Place SQL Server
72Views
0likes
1Comment
Deadlocks on High Frequency Updates
Using SQL Server 2022, I'm stress testing an UPDATE statement. I'm using a python script to send parallel requests to the database. The problem is that, as soon as the number of parallel requests exceed max_workers_count, 576 in my case, I get multiple errors of the form: ('40001', '[40001] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Transaction (Process ID 448) was deadlocked on lock | thread resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (1205) (SQLExecDirectW)') I wasn't able to reproduce the error with less requests than max_workers_count. The UPDATE request is the following: UPDATE dbo.UsersAnswer SET UsersSelectionType = ? WHERE For_Question = ? AND For_Quiz = ? AND FK_Answer = ?; Note that, I've tried with and without (UPDLOCK, ROWLOCK) and (UPDLOCK), but it doesn't change the outcome. Also, the updates are done for the same primary key. Finally, the UsersAnswer table is created as follows: CREATE TABLE [dbo].[UsersAnswer]( [For_Question] [smallint] NOT NULL, [For_Quiz] [uniqueidentifier] NOT NULL, [FK_Answer] [int] NOT NULL, [UsersSelectionType] [tinyint] NOT NULL, CONSTRAINT [PK_UsersAnswer] PRIMARY KEY CLUSTERED ( [For_Question] ASC, [For_Quiz] ASC, [FK_Answer] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[UsersAnswer] WITH CHECK ADD CONSTRAINT [FK_UsersAnswer_Answer_FK_Answer] FOREIGN KEY([FK_Answer]) REFERENCES [dbo].[Answer] ([PK_Answer]) GO ALTER TABLE [dbo].[UsersAnswer] CHECK CONSTRAINT [FK_UsersAnswer_Answer_FK_Answer] GO ALTER TABLE [dbo].[UsersAnswer] WITH CHECK ADD CONSTRAINT [FK_UsersAnswer_QQ_For_Question_For_Quiz] FOREIGN KEY([For_Question], [For_Quiz]) REFERENCES [dbo].[QQ] ([FK_Question], [FK_Quiz]) ON DELETE CASCADE GO ALTER TABLE [dbo].[UsersAnswer] CHECK CONSTRAINT [FK_UsersAnswer_QQ_For_Question_For_Quiz] GO Do you have any idea on what could cause the deadlock? The deadlock graph is huge, you can find it here. Thanks for your insights on this.
Solved
sebd
Jan 25, 2025 Place SQL Server
300Views
0likes
8Comments