1

Given two tables:

  • Company [id, name, balance]
  • Employee [id, name, company_id]

I'd like to delete a company and its dependencies but I cannot do so atomically because in my real project a company has multiple levels of dependencies which would be too complex to squeeze into a single SQL statement. Besides, looking at https://stackoverflow.com/a/5598275/14731 I'm not sure a single SQL statement would be atomic.

Instead I do this:

  1. SELECT ... FOR UPDATE all employees associated with the company (lock them to prevent modification in mid-transaction)
  2. Delete each employee one by one
  3. Delete the parent Company.

There is one catch: how do I prevent new records that reference the Company from being inserted between step 1 and 2? According to https://stackoverflow.com/a/3602125/14731 SELECT ... FOR UPDATE does not block inserts. Ideally I want to place a lock on the Company, not one lock per every kind of dependency that could point to it.

UPDATE: I've got an idea but I'm not sure whether it makes sense.

If someone wants to insert a new record that points to the Company, they'd likely need to look it up to make sure it exists. If I SELECT ... FOR UPDATE on the company before step 1, it should block their reads thereby delaying the subsequent insert. I've got two questions:

  1. Does SELECT ... FOR UPDATE block normal SELECT queries? If not, I'd have to ensure that inserting threads always use SELECT ... FOR UPDATE when looking up the Company.
  2. Would this approach work?
Community
  • 1
  • 1
Gili
  • 86,244
  • 97
  • 390
  • 689
  • Can't you just wrap your deletes in a transaction: begin transaction, delete all employees with one statement, delete parent company, rollback on error else commit, end transaction? – Lord Peter Jan 01 '13 at 22:07
  • Which DBMS are you using? Oracle? PostgreSQL? DB2? –  Jan 01 '13 at 23:25
  • @LordPeter, nothing prevents another thread from inserting a new employee between the time you delete all employees and the time you delete the company. – Gili Jan 02 '13 at 19:36
  • @a_horse_with_no_name, I'm looking for database-agnostic solutions. If that's not possible, feel free to provide database-specific solutions. – Gili Jan 02 '13 at 19:37
  • If you are only running two single queries then the chance of a new employee being inserted will be reduced. If it happens then presumably you have FK constraints that will cause the company delete to fail so you will roll back and can try again. Alternatively, just cascade company deletes to take out the "orphaned" employees? Procedural approaches are rarely the right way to go. – Lord Peter Jan 02 '13 at 22:02
  • @LordPeter, CASCADE ON DELETE sounds like a good solution assuming someone could answer http://stackoverflow.com/q/14131108/14731 – Gili Jan 03 '13 at 16:23

1 Answers1

0

Here is one possible solution:

  1. The thread that inserts new associations should establish a read lock on the Company (using REPEATABLE_READ isolation or SELECT ... FOR SHARE)
  2. The thread that deletes the Company should write lock the row using SELECT ... FOR UPDATE.
  3. The write lock will wait for all outstanding read locks to be released and will prevent new read locks from being established until the DELETE operation is complete.
Gili
  • 86,244
  • 97
  • 390
  • 689