Can someone explain to me the difference between the two lock hints above?
It appears both hold locks until the end of the transaction so just wondering if there’s a difference between the two.
I’ve read books online but still not sure of the exact difference.
Is the only difference updlock allows shared-lock reads whereas xlock doesn’t allow shared-lock reads? I realize updlock uses update locks on the data, but an update lock sounds similar to
an exclusive lock since it won’t allow data changes to the data before the update occurs. Any clarification here is appreciated.
It is correct that update locks allow SELECT and that both hold locks until end of transactions. Update locks blocks update locks so both will serialize your code. However, watch out for XLOXK
SQL Server will effectively ignore XLOCK hint! There’s an optimization where SQL Server check whether the data has changed since the oldest open transaction. If not, then an xlock is ignored.
This makes xlock hints basically useless and should be avoided. Just to a test and you will see…