September 8, 2010 at 7:58 am
I have the following scenario.
1. rows exist in a table
2. C# data app, stateless dal layer queries for pending rows. Each row is sent to a receiving C# web service.
3. Web service is stateless and inserts the data into a new row in same table.
4. For dev environment the source and target tables are the same table in the same db. In real world these may be different dbs on different servers.
5. table pk is an int identity. No other keys in the index.
The problem is that I'm getting a deadlock on the receiving web svc when it tries to insert the new row.
This is an extremely simple engine..The web service is literally copying rows so the can be further processed later by yet another application.
The block is happening on the pk index page.
I have used DBCC TRACEON(1222,-1) to get the deadlock info from the sql log.
This seems a little odd because the sending application and the receiving application are working on different rows. Sending app is reading rows and updating status from unprocessed to sent, receiving app is writing the data as a new row.
I'm not sure how to fix this. All I can think of is that the apps are running so quickly that the web svc cannot get a page lock on the index page when adding new rows, even though the sending app is only reading the index pages.
Does anybody have any suggestions ? Below is a snipped from my trace log but I'm having trouble figuring out what its really telling me. All I'm really sure of is the keylock and the object name of PK_DataHeader
Any suggestions would be appreciated. Thanks.
Bill
09/08/2010 09:23:09,spid14s,Unknown,keylock hobtid=72057594047823872 dbid=8 objectname=InteliChart_TransportAndMapping.dbo.DataHeader indexname=PK_DataHeader id=lockc6b6b40 mode=X associatedObjectId=72057594047823872
09/08/2010 09:23:09,spid14s,Unknown,waiter id=process56e2f8 mode=S requestType=wait
09/08/2010 09:23:09,spid14s,Unknown,waiter-list
09/08/2010 09:23:09,spid14s,Unknown,owner id=process55a4d8 mode=X
09/08/2010 09:23:09,spid14s,Unknown,owner-list
** EXCLUSIVE LOCK ON PK ?
09/08/2010 09:23:09,spid14s,Unknown,keylock hobtid=72057594047823872 dbid=8 objectname=InteliChart_TransportAndMapping.dbo.DataHeader indexname=PK_DataHeader id=locka1048c0 mode=X associatedObjectId=72057594047823872
09/08/2010 09:23:09,spid14s,Unknown,resource-list
09/08/2010 09:23:09,spid14s,Unknown,(@0 smallint<c/>@1 smallint<c/>@2 varchar(16)<c/>@3 varchar(16)<c/>@4 varchar(16)<c/>@5 varchar(16)<c/>@6 uniqueidentifier<c/>@7 datetime<c/>@8 int<c/>@9 char(8)<c/>@10 uniqueidentifier<c/>@11 smallint<c/>@12 int<c/>@13 smallint<c/>@14 varchar(24)<c/>@15 varchar(26)<c/>@16 varchar(36)<c/>@17 varchar(4)<c/>@18 varchar(4)<c/>@19 smallint<c/>@20 smallint<c/>@21 smallint<c/>@22 int<c/>@23 int)insert [dbo].[DataHeader]([UpdateStatus]<c/> [ReceiveStatus]<c/> [Queue]<c/> [Src_Facility]<c/> [Src_Application]<c/> [Dst_Facility]<c/> [Dst_Application]<c/> [Header_Guid]<c/> [Header_DateTime]<c/> [ParentHeaderGuid]<c/> [InteliChartClientID]<c/> [PersonID]<c/> [EPersonID]<c/> [MsgType]<c/> [MsgDetail_Guid]<c/> [MPI_Update_DateTime]<c/> [MPI_Update_Status]<c/> [REG_Update_DateTime]<c/> [REG_Update_Status]<c/> [VersionTag]<c/> [WebService_Send_DateTime]<c/> [WebService_ACK_DateTime]<c/> [Transported]<c/> [Reconciled]<c/> [ReconciledDateTime]<c/> [CRC]<c/> [Port]<c/> [Person_MatchMethod]<c/> [Engine_Agent]<c/> [Engine_Queue]<c/> [MSH_MsgDateTime]<c/> [MSH_MsgControlID]<c/> [PID_AlternatePatientID]<c/> [PID_DOB]<c/> [PID_DriversLicenseNumber]<c/> [PID_ExternalID]<c/> [PID_FirstName]<c/> [PID_Gender]<c/> [
09/08/2010 09:23:09,spid14s,Unknown,inputbuf
09/08/2010 09:23:09,spid14s,Unknown,unknown
09/08/2010 09:23:09,spid14s,Unknown,frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
09/08/2010 09:23:09,spid14s,Unknown,where @@ROWCOUNT > 0 and [Header_Guid] = @6
09/08/2010 09:23:09,spid14s,Unknown,from [dbo].[DataHeader]
09/08/2010 09:23:09,spid14s,Unknown,select [Header_seq]
09/08/2010 09:23:09,spid14s,Unknown,frame procname=adhoc line=3 stmtstart=3346 sqlhandle=0x020000004f0fb716ddeff3153443a1c718227ff61e95c2c6
09/08/2010 09:23:09,spid14s,Unknown,executionStack
09/08/2010 09:23:09,spid14s,Unknown,process id=process56e2f8 taskpriority=0 logused=1940 waitresource=KEY: 8:72057594047823872 (9f00fa13533d) waittime=4477 ownerId=1259100 transactionname=user_transaction lasttranstarted=2010-09-08T09:23:05.040 XDES=0x132587f8 lockMode=S schedulerid=3 kpid=7588 status=suspended spid=63 sbid=2 ecid=0 priority=0 transcount=1 lastbatchstarted=2010-09-08T09:23:05.043 lastbatchcompleted=2010-09-08T09:23:05.040 clientapp=.Net SqlClient Data Provider hostname=IHS-DEV-BP hostpid=3496 loginname=InteliChart isolationlevel=read committed (2) xactid=1259100 currentdb=8 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
09/08/2010 09:23:09,spid14s,Unknown,(@0 smallint<c/>@1 smallint<c/>@2 varchar(16)<c/>@3 varchar(16)<c/>@4 varchar(16)<c/>@5 varchar(16)<c/>@6 uniqueidentifier<c/>@7 datetime<c/>@8 int<c/>@9 char(8)<c/>@10 uniqueidentifier<c/>@11 smallint<c/>@12 int<c/>@13 smallint<c/>@14 varchar(24)<c/>@15 varchar(26)<c/>@16 varchar(36)<c/>@17 varchar(4)<c/>@18 varchar(4)<c/>@19 smallint<c/>@20 smallint<c/>@21 smallint<c/>@22 int<c/>@23 int<c/>@24 varchar(128)<c/>@25 int)insert [dbo].[DataHeader]([UpdateStatus]<c/> [ReceiveStatus]<c/> [Queue]<c/> [Src_Facility]<c/> [Src_Application]<c/> [Dst_Facility]<c/> [Dst_Application]<c/> [Header_Guid]<c/> [Header_DateTime]<c/> [ParentHeaderGuid]<c/> [InteliChartClientID]<c/> [PersonID]<c/> [EPersonID]<c/> [MsgType]<c/> [MsgDetail_Guid]<c/> [MPI_Update_DateTime]<c/> [MPI_Update_Status]<c/> [REG_Update_DateTime]<c/> [REG_Update_Status]<c/> [VersionTag]<c/> [WebService_Send_DateTime]<c/> [WebService_ACK_DateTime]<c/> [Transported]<c/> [Reconciled]<c/> [ReconciledDateTime]<c/> [CRC]<c/> [Port]<c/> [Person_MatchMethod]<c/> [Engine_Agent]<c/> [Engine_Queue]<c/> [MSH_MsgDateTime]<c/> [MSH_MsgControlID]<c/> [PID_AlternatePatientID]<c/> [PID_DOB]<c/> [PID_DriversLicenseNumber]<c/> [PID_ExternalID]<c/> [PID_Fi
09/08/2010 09:23:09,spid14s,Unknown,inputbuf
09/08/2010 09:23:09,spid14s,Unknown,unknown
09/08/2010 09:23:09,spid14s,Unknown,frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
09/08/2010 09:23:09,spid14s,Unknown,where @@ROWCOUNT > 0 and [Header_Guid] = @6
09/08/2010 09:23:09,spid14s,Unknown,from [dbo].[DataHeader]
09/08/2010 09:23:09,spid14s,Unknown,select [Header_seq]
09/08/2010 09:23:09,spid14s,Unknown,frame procname=adhoc line=3 stmtstart=3392 sqlhandle=0x02000000b872333bdd09d0d98935b8ab97e66215c37c239c
09/08/2010 09:23:09,spid14s,Unknown,executionStack
09/08/2010 09:23:09,spid14s,Unknown,process id=process55a4d8 taskpriority=0 logused=1984 waitresource=KEY: 8:72057594047823872 (a0000dfb1b95) waittime=4477 ownerId=1259097 transactionname=user_transaction lasttranstarted=2010-09-08T09:23:05.040 XDES=0x13258250 lockMode=S schedulerid=1 kpid=7216 status=suspended spid=64 sbid=2 ecid=0 priority=0 transcount=1 lastbatchstarted=2010-09-08T09:23:05.040 lastbatchcompleted=2010-09-08T09:23:05.040 clientapp=.Net SqlClient Data Provider hostname=IHS-DEV-BP hostpid=3496 loginname=InteliChart isolationlevel=read committed (2) xactid=1259097 currentdb=8 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
09/08/2010 09:23:09,spid14s,Unknown,process-list
09/08/2010 09:23:09,spid14s,Unknown,deadlock victim=process56e2f8
September 8, 2010 at 9:02 am
A couple things
1. Are you pooling connections?
2. Are implicit transactions set on?
On the surface, a read ought to not lock for any period of time. Does this read disconnect? Is it a simple SELECT without any cursor or reason to hold onto a shared lock on the table?
September 8, 2010 at 9:33 am
Thanks for the suggestions but as far as I know
1. sending app has a connection to read the data but selects one row at a time and returns out of the dal layer. The connection stays open thru multiple calls to dal layer. Will be processing many rows and seemed better to leave it open than constantly opening and closing it.
2. web svc layer is stateless. Opens a connection, starts a tran, insert table 1, insert table 2, commit/rollback/close connection.
not pooling connections at this time.whatever the default c#/.net/sql 2k5 behaviour is what we got.
no cursors, nothing odd at all.
Its not really a business problem because the sending app reads a row and calls web svc B, but web svc writes a whole new row..is more of a physical index page thing (i think). I may have to try reorging the index. Right now the Int/Identity pk is a clustered index. This is a fairly busy table with lots of adds so thats a busy index..maybe even a hot spot sort of thing.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply