There are actually index created :

CREATE NONCLUSTERED INDEX [IDX_TXN_DESP_OUT_STATUS] ON [dbo].[TXN_DESP_OUT]

(

[DESP_STATUS] ASC

)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]

GO

ALTER TABLE [dbo].[TXN_DESP_OUT] ADD CONSTRAINT [PK_TXN_DESP_OUT] PRIMARY KEY CLUSTERED

(

[RUN_NO] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

Any idea Why the update statement will require a page lock after a row lock on PK_TXN_DESP_OUT (RUN_NO) cluster index ?

UPDATE TXN_DESP_OUT

set DESP_STATUS = 'C',DESP_STATUS_DT=getdate()

WHERE RUN_NO=@RUN_NO and DESP_STATUS <> 'C'

The deadlock trace showing the waitresource="PAGE: 9:1:1100400". i am trying to prevent this page lock so that the deadlock will not occurred.