I am facing deadlock issues which I suspect is from gap lock that is issued when my select for update doesn't find a row
Hi everyone.
Currently I am facing a deadlock issue between two inserts into my Account table. My account table only has 1 index which is the PK AccountID. When the deadlock occurs, the two insert statements are both trying to insert into Account but with different AccountIDs which I believed should have never caused a deadlock since they are not fighting over the same resource.
However upon further investigation, it seems like a Select for update is done to check if a row already exists and to update it if it does. However since this select does not find an existing row, I expected it to not lock anything but I suspect it might have a gap lock.
Is my understanding correct that even though the SELECT for update doesn't find an existing row, it will gap lock an index range? And when my second transaction tries to insert into that index gap, it fails to get the lock?