Updating an ID with DELETE - OUTPUT - INSERT
I need to update the identity column in a very specific scenario (most of the time, the identity will be left alone). When I need to update it, I just need to give it a new value and so I try to use a combo DELETE + INSERT
.
I currently have a working request that looks something like this:
DELETE Test_Id
OUTPUT DELETED.Data,
DELETED.Moredata
INTO Test_id
WHERE Id = 13
(This is just an example, the actual query is a little more complicated.)
One of them raised an important point. She asked if this would lead to an impasse as we write and read from the same table. While it works fine in this example (half a dozen lines), in a real world scenario with tens of thousands of lines it might not work.
Is this a real problem? If so, is there a way to prevent this?
I installed .
Thank! SQL Fiddle
source to share
My first thought was yes it is possible. And it may still be possible, but it would be very difficult to get stuck in this simplified version of the statement. You select one row for which row-level locks are likely, and the fact that the locks needed to delete and insert are quickly acquired one after the other.
I did some testing against a table with a million rows, executing the statement 5 million times on 6 different connections in parallel. Didn't hit one dead end.
But add a relational query, a table with indexes, and foreign keys and you can only have a winner. I had a similar expression that was causing deadlocks.
I ran into deadlock errors with a similar statement.
UPDATE A
SET x=0
OUTPUT INSERTED.ID, 'a' INTO B
So in order to execute this statement to complete mssql it is necessary to take locks for updates in table A, lock inserts in table B and shared (read) locks in table A to check foreign key table B, must contain table A.
Last but not least, mssql decided that it would be wise to use parallelism for this particular query, causing the operator to loop on itself. To solve this problem, I just set the query "MAXDOP 1" on the statement to prevent parallelism.
However, there is no clear answer to preventing deadlocks. As mssql is said to have ever so much, it depends. You can use the exclusive TABLOCKX table hint. This will prevent deadlock, but it is probably not desirable for other reasons.
source to share