Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dim
Yak Posting Veteran
57 Posts |
 Posted - 2011-01-17 : 13:11:05
|
| Hi,I am working on a job fail problem where there is a query deadlock. My query is trying to update the table which is already being used by other resource it seems. I have modified my query to include updlock. The query is as below:UPDATE TRP.dbo.Cont with (updlock)SET contact_Updated_ind = 'Y'FROM TRP.dbo.Cont C with (updlock) INNER JOIN Ph_Type WPT ON WPT.Cont_ID = C.Cont_ID INNER JOIN Ph_T_Type WP -- 06.01.0003 modification ON WPT.Ph_id = WP.Ph_id AND WP.IsValid = 1WHERE WPT.Cont_ID IS NOT NULL AND WPT.P_nbr_ID IS NOT NULL AND WPT.Ph_ID IS NULLMy concerns:1. Will updlock help resolve the deadlock and obtain the update lock for the table?2. Do I need to use the readpast along with the updlock?Please adviceThank you,dimDp |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dim
Yak Posting Veteran
57 Posts |
Posted - 2011-01-17 : 15:24:03
|
| Hi tkizer,This is what is mentioned in the log file:Msg 1205, Sev 13, State 52, Line 235 : Transaction (Process ID 139) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction. [SQLSTATE 40001]Is this enough information? The dba had not set the profiler on. Dp |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
Updlock and Readpast Table Hints