partial data loss from deadlock?

Question

I have a situation here where We had a partial data loss due to so e DB Deadlock. The values were being inserted using the same procedures to multiple tables but the data loss happened only for a few. Is this really possible?

Answer 1

By default, transactions need to be explicit in SQL Server. So, if your procedure is structured like this:

create procedure dbo.doStuff
as
begin
   update table1 …;
   update table2 …;
   delete table3 …;
   insert table4 …;
end

And you encounter a deadlock in the table3 delete, the results of the updates to tables 1&2 should be considered durable (that is, they won't also be rolled back by the deadlock's rollback). If you need all of the statements to succeed/fail atomically, you need to wrap the whole thing in a transaction. That is:

create procedure dbo.doStuff
as
begin
   begin transaction;
      update table1 …;
      update table2 …;
      delete table3 …;
      insert table4 …;
   commit transaction;
end

Keep in mind that this does have implications for concurrency (you're holding onto locks for longer) - ain't nothin' for free. And, although you can't rely on it, it can also influence which process is the deadlock victim (since part of the choice is "how much work would it be to roll back")!

You can (and probably should!) get fancier with begin try and xact_state() if you want, but the above is the basics.