0

If you have a simple select with updlock statement as below:

select top 1 id 
from customer with (updlock, serializable) 
where guid = @guid;

will all the locks be acquired atomically during the select?

Or will it be acquired one by one, i.e. index lock first then row lock etc... thus prone to deadlock if the order of locks are acquired differently in another thread executing the same statement with a different guid or even the same guid?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TFK
  • 275
  • 1
  • 2
  • 11
  • 3
    the fact you are asking this question leads me to believe you have an XY problem. – Mitch Wheat Feb 01 '21 at 04:53
  • 1
    plus you left out the wrapping transaction (like your previous question here: https://stackoverflow.com/questions/65948932/sql-server-updlock-serializable-doesnt-block-100) – Mitch Wheat Feb 01 '21 at 05:07
  • I am trying to understand this better, down to its details so i don't see how its an XY problem. Yes i left it out because I don't see how it affects my question here. Or are you suggesting purely this statement on itself being inside a transaction or not can change whether the locks are acquired atomically? – TFK Feb 01 '21 at 05:42
  • If being in a transaction affects the atomicity of the locks acquired (not talking about whether these locks get released before transaction etc..), then this seems to me an answer you can help answer. – TFK Feb 01 '21 at 05:52

0 Answers0