sql - Can an INSERT operation result in a deadlock? -

assuming:

• i using repeatable_read or serializable transaction isolation (locks retained every time access row)
• we talking multiple threads accessing multiple tables simultaneously.

i have following questions:

1. is possible insert operation cause deadlock? if so, please provide detailed scenario demonstrating how deadlock may occur (e.g. thread 1 this, thread 2 that, ..., deadlock).
2. for bonus points: answer same question other operations (e.g. select, update, delete).

update: 3. super bonus points: how can avoid deadlock in 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 new company follows:

• insert permissions; -- inserts permissions.id = 100
• insert companies (name, permission_id) values ('nintendo', 100); -- inserts companies.id = 200

i delete company follows:

• select permission_id companies id = 200; -- returns permission_id = 100
• delete companies id = 200;
• delete permissions id = 100;

in above example, insert locking order [permissions, companies] whereas delete locking order [companies, permissions]. there way fix example repeatable_read or serializable isolation?

generally modifications can cause deadlock , selects not (get later). so

1. no cannot ignore these.
2. you can ignore select depending on database , settings others give deadlocks.

you don't need multiple tables.

the best way create deadlock same thing in different order.

sql server examples:

create table (     pk int primary key ) 

session 1:

begin transaction insert values(1) 

session 2:

begin transaction     insert values(7) 

session 1:

delete pk=7 

session 2:

delete pk=1 

you deadlock. proved inserts & deletes can deadlock.

updates similar:

session 1:

begin transaction     insert values(1) insert values(2) commit  begin transaction update set pk=7 pk=1 

session 2:

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

session 1:

update set pk=9 pk=2 

deadlock!

select should never deadlock on databases because locks uses interfere consistent reads. that's crappy database engine design though.

sql server not lock on select if use snapshot isolation. oracle & think postgres never lock on select (unless have update reserving update anyway).

so think have few incorrect assumptions. think i've proved:

1. updates can cause deadlocks
2. deletes can cause deadlocks
3. inserts can cause deadlocks
4. you not need more 1 table
5. you do need more 1 session

you'll have take word on select ;) depend on db , settings.