Assuming:

  • I am using REPEATABLE_READ or SERIALIZABLE transaction isolation (locks get retained every time I access a row)
  • We are talking about multiple threads accessing multiple tables simultaneously.

I have the following questions:

  1. Is it possible for an INSERT operation to cause a deadlock? If so, please provide a detailed scenario demonstrating how a deadlock may occur (e.g. Thread 1 does this, Thread 2 does that, ..., deadlock).
  2. For bonus points: answer the same question for all other operations (e.g. SELECT, UPDATE, DELETE).

UPDATE: 3. For super bonus points: how can I avoid a deadlock in the following scenario?

Given tables:

  • permissions[id BIGINT PRIMARY KEY]
  • companies[id BIGINT PRIMARY KEY, name VARCHAR(30), permission_id BIGINT NOT NULL, FOREIGN KEY (permission_id) REFERENCES permissions(id))

I create a new Company as follows:

  • INSERT INTO permissions; -- Inserts permissions.id = 100
  • INSERT INTO companies (name, permission_id) VALUES ('Nintendo', 100); -- Inserts companies.id = 200

I delete a Company as follows:

  • SELECT permission_id FROM companies WHERE id = 200; -- returns permission_id = 100
  • DELETE FROM companies WHERE id = 200;
  • DELETE FROM permissions WHERE id = 100;

In the above example, the INSERT locking order is [permissions, companies] whereas the DELETE locking order is [companies, permissions]. Is there a way to fix this example for REPEATABLE_READ or SERIALIZABLE isolation?

sql insert deadlock
Edit
Report