Hi

I am getting below error:

Transaction (Process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

I have given trace logs and table,SPs. When client application restarts due to power failure, all these 3 SPs are called from 200 clients.(server is supported with UPS).

All the SPs are trying to update,select(,insert) a single table at the same time.

Can you please help to sort this problem?


CREATE TABLE [dbo].[STB](
[STBId] [char](12) NOT NULL,
[IpAddress] [varchar](15) NULL,
[RestartTime] [datetime] NULL,
[RoomNo] [varchar](10) NULL,
[ImageVersion] [varchar](10) NULL,
[DeployImageVersion] [varchar](10) NULL,
[IpUpdated] [datetime] NULL,
[TVstate] [smallint] NULL,
[CurrentFeatureUsed] [int] NULL,
CONSTRAINT [PK_STB] PRIMARY KEY CLUSTERED
(
[STBId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


-----

CREATE PROCEDURE [dbo].[QueryRoomNoFromSTBIP]
@STBId char(12) = null,
@IPAddress char(15) = null
AS
If @STBId is null or @IPAddress is NULL
return -1
If exists (select RoomNo from STB with(NOLOCK) where STBId = @STBId and IPAddress = @IPAddress)--STB-1 IP-1
Begin
--Update the Restart Time
update STB set RestartTime=getdate() where STBId = @STBId
select RoomNo from STB with(NOLOCK) where STBId = @STBId and IPAddress = @IPAddress
End
Else if exists (select RoomNo from STB with(NOLOCK) where STBId = @STBId)--STB-1 IP-0
Begin
if exists(select STBId from STB with(NOLOCK) where IPAddress = @IPAddress )--STB-Different IP-Yes
Begin
update STB set IPAddress = null, IPUpdated = getdate() where IPAddress = @IPAddress
End

update STB set IPAddress = @IPAddress, IPUpdated = getdate(), RestartTime=getdate() where STBId = @STBId
select RoomNo from STB with(NOLOCK) where STBId = @STBId and IPAddress = @IPAddress
End
Else --if not exists(select RoomNo from STB where STBId = @STBId)--STB-0
Begin
if exists(select STBId from STB with(NOLOCK) where IPAddress = @IPAddress)--STB-0 IP-1
Begin
update STB set IPAddress = null, IPUpdated = getdate() where IPAddress = @IPAddress
End
insert into [Config].[dbo].[STB]([STBId] ,[IpAddress],[IPUpdated],[RoomNo],[RestartTime])VALUES(@STBId,@IPAddress,getdate(),'',getdate())
End

------

CREATE Procedure [dbo].[UpdateImageVersion]
@STBId char(12),
@ImageVersion varchar(10)
as
UPDATE [Config].[dbo].[STB]
SET [ImageVersion] = @ImageVersion
,[DeployImageVersion] = null
WHERE STBId = @STBId

----

CREATE PROCEDURE [dbo].[HMSQueryGuestMessagesFromSTB]
@STBId char (12) = null
AS
Declare @RoomNo varchar(10)

If @STBId is null
return -1

select @RoomNo = RoomNo from STB with(NOLOCK) where STBId = @STBId

SELECT HMSGuestMessage.iTVMessageNo as MessageNo,
HMSGuestMessage.MessageText as Message_Text,
HMSGuestMessage.CurrentStatus as Current_Status
FROM HMSGuestMessage
INNER JOIN HMSRoomGuest ON HMSGuestMessage.GuestNo = HMSRoomGuest.GuestNo
INNER JOIN STB with(NOLOCK) ON HMSRoomGuest.RoomNo = STB.RoomNo
Where STB.STBId = @STBId and HMSGuestMessage.CurrentStatus <> -1 and HMSRoomGuest.Checkoutdate is null
order by HMSGuestMessage.CurrentStatus
-- Update Guest New Message Flag
if @RoomNo is not null
Begin
if exists ( select roomno from [Message].[dbo].[GuestMessage] where RoomNo = @RoomNo)
Update [Message].[dbo].[GuestMessage] set [GuestMsgFlag] = 0 where RoomNo = @RoomNo
else
Insert into [Message].[dbo].[GuestMessage] ([RoomNo] ,[Start] ,[End] ,[GuestMsgFlag])
Values ( @RoomNo, Null, Null, 0)
End


----


04/29/2009 10:05:59,spid27s,Unknown,waiter id=processa7ee38 mode=U requestType=wait
04/29/2009 10:05:59,spid27s,Unknown,waiter-list
04/29/2009 10:05:59,spid27s,Unknown,owner id=processa79b58 mode=X
04/29/2009 10:05:59,spid27s,Unknown,owner-list
04/29/2009 10:05:59,spid27s,Unknown,ridlock fileid=1 pageid=183 dbid=5 objectname=Config.dbo.STB id=lockb6bce80 mode=X associatedObjectId=72057594041663488
04/29/2009 10:05:59,spid27s,Unknown,waiter id=processa79b58 mode=U requestType=wait
04/29/2009 10:05:59,spid27s,Unknown,waiter-list
04/29/2009 10:05:59,spid27s,Unknown,owner id=processd25978 mode=X
04/29/2009 10:05:59,spid27s,Unknown,owner-list
04/29/2009 10:05:59,spid27s,Unknown,ridlock fileid=1 pageid=252 dbid=5 objectname=Config.dbo.STB id=lock9277e40 mode=X associatedObjectId=72057594041663488
04/29/2009 10:05:59,spid27s,Unknown,waiter id=processd25978 mode=U requestType=wait
04/29/2009 10:05:59,spid27s,Unknown,waiter-list
04/29/2009 10:05:59,spid27s,Unknown,owner id=processa79b58 mode=X
04/29/2009 10:05:59,spid27s,Unknown,owner-list
04/29/2009 10:05:59,spid27s,Unknown,ridlock fileid=1 pageid=608 dbid=5 objectname=Config.dbo.STB id=lock3f71dc0 mode=X associatedObjectId=72057594041663488
04/29/2009 10:05:59,spid27s,Unknown,resource-list
04/29/2009 10:05:59,spid27s,Unknown,Proc [Database Id = 5 Object Id = 2030630277]
04/29/2009 10:05:59,spid27s,Unknown,inputbuf
04/29/2009 10:05:59,spid27s,Unknown,--commit transaction
04/29/2009 10:05:59,spid27s,Unknown,WHERE STBId = @STBId
04/29/2009 10:05:59,spid27s,Unknown,<c/>[DeployImageVersion] = null
04/29/2009 10:05:59,spid27s,Unknown,SET [ImageVersion] = @ImageVersion
04/29/2009 10:05:59,spid27s,Unknown,UPDATE [Config].[dbo].[STB]
04/29/2009 10:05:59,spid27s,Unknown,frame procname=Config.dbo.UpdateImageVersion line=7 stmtstart=328 sqlhandle=0x0300050085f50879c7c3db00f39b0000010000 0000000000
04/29/2009 10:05:59,spid27s,Unknown,executionStack
04/29/2009 10:05:59,spid27s,Unknown,process id=processd25978 taskpriority=0 logused=84 waitresource=RID: 5:1:608:1 waittime=4609 ownerId=15507515 transactionname=UPDATE lasttranstarted=2009-04-29T10:05:54.903 XDES=0x46cd0250 lockMode=U schedulerid=4 kpid=5180 status=suspended spid=61 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2009-04-29T10:05:54.903 lastbatchcompleted=2009-04-29T10:05:54.903 hostname=ITVSERVER-1 hostpid=2368 loginname=AppLogin isolationlevel=read committed (2) xactid=15507515 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
04/29/2009 10:05:59,spid27s,Unknown,Proc [Database Id = 5 Object Id = 2030630277]