SQL Server Difference among ROWLOCK ,UPDLock AND xlock
-
I have some confusion about lock (ROWLOCK ,UPDLock AND xlock). i want to know the difference among these locks. where to use UPDLock, when to use RowLock and when Xlock with a example for better clarification. 1)
BEGIN TRANSACTION
SELECT @ID=RowID FROM MyTable WITH (ROWLOCK, XLOCK, HOLDLOCK) WHERE ID=6822
In the above sql rowlock and XLOCK both use as a result from other session records 6822 could not be read or modify. XLOCK alone is capable to lock the rows....so why one should use ROWLOCK & XLOCK together ? if i use only xlock & HOLDLOCK then it will not serve the purpose ? 2) Tell me with example what is the difference between ROWLOCK & UPDLOCK ? ROWLOCK prevent other session to modify data and UPDLOCK does the same thing. so what is the difference between ROWLOCK & UPDLOCK ? Please anyone explain these difference with example as a result at my end i can run the example code and understand. Thanks
-
I have some confusion about lock (ROWLOCK ,UPDLock AND xlock). i want to know the difference among these locks. where to use UPDLock, when to use RowLock and when Xlock with a example for better clarification. 1)
BEGIN TRANSACTION
SELECT @ID=RowID FROM MyTable WITH (ROWLOCK, XLOCK, HOLDLOCK) WHERE ID=6822
In the above sql rowlock and XLOCK both use as a result from other session records 6822 could not be read or modify. XLOCK alone is capable to lock the rows....so why one should use ROWLOCK & XLOCK together ? if i use only xlock & HOLDLOCK then it will not serve the purpose ? 2) Tell me with example what is the difference between ROWLOCK & UPDLOCK ? ROWLOCK prevent other session to modify data and UPDLOCK does the same thing. so what is the difference between ROWLOCK & UPDLOCK ? Please anyone explain these difference with example as a result at my end i can run the example code and understand. Thanks
What is the difference between ROWLOCK, UPDLOCK and HOLDLOCK - Microsoft Q&A[^] There is also information in the Microsoft docs: Table Hints (Transact-SQL) - SQL Server | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
What is the difference between ROWLOCK, UPDLOCK and HOLDLOCK - Microsoft Q&A[^] There is also information in the Microsoft docs: Table Hints (Transact-SQL) - SQL Server | Microsoft Docs[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Sir Thanks for your reply & link. i read about ROWLOCK & UPDLock but still not clear. they wrote very briefly. if possible sir please discuss the difference between ROWLOCK & UPDLock hint with example. Thanks
