I am working on a problem in my odbc application, which i have isolated to the MS SQL Server query analyzer.
The problem is related to the deadlock faced by one of the session, when 2 transactions each running from 2 sessions are trying to INSERT a record from their respective session with the duplicate values for the columns icol1 and cCol2 and then one of the
session UPDATE the value of column icol1 in the previously inserted record. After the completion of the test, I see 1 record created in the table from one of the session, but other session becomes deadlock victim.
Following is the table structure:
CREATE TABLE [sgarg].[test_buf](
[icol1] [int] NULL DEFAULT ((0)),
[ccol2] [varchar](30) NULL,
[ccol3] [varchar](30) NULL,
[RECID] [bigint] NULL
) ON [PRIMARY]
Following are the index definitions
CREATE UNIQUE NONCLUSTERED INDEX [test_buf##ixprim] ON [sgarg].[test_buf]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [test_buf#_#recid] ON [sgarg].[test_buf]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPU
View Complete Post