Select, Update , Insert on single table SQLServer2

I have a client application which runs three threads "Select", "Update","Insert". Also there are three stored procedures "Select" , "Update" , "Insert" which are used by corresponding threads. Default isolation level "READCOMMITTED" is mentioned in "Select" stored procedure.

We are using SQL Server 2005

The way selection and updation occur is,

Suppose there are 10 records in Table1. "Select" thread starts from say SK 1, "Update" thread starts updating from SK 7. Similarily runs the "Insert" thread.

When we run multiple client applications around 10. We get transaction deadlocks. Please Helllllllp!!!!!!!!!!!

Any solution would be greatly appreciated

Can you post your code and table structure?

Otherwise try to identify the deadlock using the tracedlag 1204 and SQL server profiler.

Roji. P. Thomas
http://toponewithties.blogspot.com

This code runs in thread. This is for Select. Similarily for Insert and Update

While (blnStopSelectThread = False)
lngSelectSeqNo = GetSelectSequenceNo()
dsSelect = ExecuteSQLReturnDs("EXEC WFMS_TESTLOCK_SELECT '" & lngSelectSeqNo "'")
------------- Do something-----------------
End While

Table Structure
Table has 70 columns. Those I am using are "SK","ObjectId" and "DeleteFlag".I select based on "SK" and update the "DeleteFlag" as "Y"

Thanks for the post

Please post the procedure code and tell us what indexes you have on the table.

Roji. P. Thomas
http://toponewithties.blogspot.com

---Select---

ALTER PROCEDURE [dbo].[WFMS_TESTLOCK_SELECT]
@SKVARCHAR(50)
AS


SELECT TOP 1
PJ_CONFIG_ITEM_ID
FROM
PMA_PROJECTS WITH (READCOMMITTED)
WHERE
PJ_SK = @SK
ORDER BY PJ_SK

------Update-----
ALTER PROCEDURE [dbo].[WFMS_TESTLOCK_UPDATE]
@PJ_SKVARCHAR(50)
AS

UPDATE PMA_PROJECTS
SET PJ_DELETE_FLAG = 'Y'
WHERE PJ_SK = @PJ_SK

------Insert----------

ALTER PROCEDURE [dbo].[WFMS_TESTLOCK_INSERT]
@PROJECTIDVARCHAR(50),
@MAPIDVARCHAR(50),
@CHILDOBJECTID VARCHAR(60),
@OBJECTSEQNONUMERIC
AS


INSERT INTO PMA_PROJECTS
(
PJ_ID,
PJ_CONFIG_ITEM_ID,
PJ_DESCRIPTION,
PJ_PIT_SK,
PJ_STATUS,
PJ_LEVEL_NO,
PJ_SEQ_NO,
PJ_LEAF_INDICATOR,
PJ_TEMPLATE_FLAG,
PJ_TEMPLATE_PROJECT_ID,
PJ_SK_REF,
PJ_FILE_NAME,
PJ_PRIORITY,
PJ_REMARKS,
PJ_COMPANY_ID,
PJ_USAGE_TYPE,
PJ_USER_CREATED,
PJ_DATE_CREATED,
PJ_OBJECT_REF,
PJ_MAP_ASSIGN_ENTITY_SK
)
VALUES
(
@PROJECTID,
@CHILDOBJECTID,
@CHILDOBJECTID,
3,
'AVAILABLE',
2,
@OBJECTSEQNO,
'Y',
'N',
@MAPID,
24871,
'',
10,
NULL,
'HTC',
'WKF',
'ADMIN',
GETDATE(),
NULL,
NULL
)
-----Indexes-------------
1.NONCLUSTERED INDEX on "PJ_CONFIG_ITEM_ID" i.e ObjectId
2.NONCLUSTERED INDEX on "PJ_DELETE_FLAG" i.e DeleteFlag

We are close [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br />What is your PK? clustered index?<br /><br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />

Yes.

quote:Originally posted by Gershom

Yes.

Yes what? Which column is the clusterd index key?


Roji. P. Thomas
http://toponewithties.blogspot.com

Sorry. PJ_SK is the primary key.

PJ_SK is the only clustered index col

OK. Both your non-clustered indexes are useless. Just drop them. Also the "TOP 1 " in the select is unnecessary since the column PJ_SK is the primary key.

Repeat the test after dropping the index and let us know the result.

Roji. P. Thomas
http://toponewithties.blogspot.com

Dear Gershom

Plz reply if the solution given by ROji has worked.
I am just as eagor as any other

Anil

Never Give Up

Testing

It works with snapshot_isolation_level.