Subject Re: [firebird-support] Deleting records and deadlocks
Author setysvar
Den 02.12.2015 13:24, skrev Ann Harrison aharrison@...
>> On Dec 2, 2015, at 6:35 AM, Tim Ward tdw@... [firebird-support] <> wrote:
>> What about if two concurrent transactions are both trying to *delete*
>> the *same* record at once? - from the point of the view of the user's
>> objectives there's no reason why this shouldn't work, as either way the
>> record is going to end up deleted, which is what the user wants, but
>> would I be right in guessing that Firebird isn't that clever, and that
>> deadlocks are possible in this scenario?
> It's not nearly that simple. What would happen if one of those transactions rolled back?
> Good luck,
> Ann
Logically, I don't see any difference between an update and a delete. If
two transactions that had done nothing possibly excepting reading the
record simultaneously tried to delete the same record, I would expect
one of them to succeed and the other to get a lock conflict (not the
same as a deadlock, but many persons, probably including you, use the
term deadlock when they mean lock conflict).

One way to avoid this particuar kind of lock conflict, would be to not
delete the record directly from the clients, but rather insert it into a
table that you e.g. could call DELETED. Never let any user directly
delete from the DELETED table, but have a housekeeping routine that at
regular intervals delete records (from both your main table and the
DELETED table). I.e. similar to what is sometimes done with extra tables
having COUNTs and inserting new records with +1 or -1 when adding or
removing items. Though I must admit I've never heard of anyone using
this technique for DELETE.