Can an INSERT operation result in a deadlock?

Question

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?

Answer 1

Generally all modifications can cause a deadlock and selects will not (get to that later). So

1. No you cannot ignore these.
2. You can somewhat ignore select depending on your database and settings but the others will give you deadlocks.

You don't even need multiple tables.

The best way to create a deadlock is to do the same thing in a different order.

SQL Server examples:

create table A
(
    PK int primary key
)

Session 1:

begin transaction
insert into A values(1)

Session 2:

begin transaction    
insert into A values(7)

Session 1:

delete from A where PK=7

Session 2:

delete from A where PK=1

You will get a deadlock. So that proved inserts & deletes can deadlock.

Updates are similar:

Session 1:

begin transaction    
insert into A values(1)
insert into A values(2)
commit

begin transaction
update A set PK=7 where PK=1

Session 2:

begin transaction
update A set pk=9 where pk=2    
update A set pk=8 where pk=1

Session 1:

update A set pk=9 where pk=2

Deadlock!

SELECT should never deadlock but on some databases it will because the locks it uses interfere with consistent reads. That's just crappy database engine design though.

SQL Server will not lock on a SELECT if you use SNAPSHOT ISOLATION. Oracle & I think Postgres will never lock on SELECT (unless you have FOR UPDATE which is clearly reserving for an update anyway).

So basically I think you have a few incorrect assumptions. I think I've proved:

1. Updates can cause deadlocks
2. Deletes can cause deadlocks
3. Inserts can cause deadlocks
4. You do not need more than one table
5. You do need more than one session

You'll just have to take my word on SELECT ;) but it will depend on your DB and settings.