Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can an INSERT operation result in a deadlock?

Tags:

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?

like image 203
Gili Avatar asked May 14 '13 02:05

Gili


People also ask

Can insert cause deadlock in Oracle?

(Yes, it may not be obvious that INSERTs, too, can cause deadlocks). Here we have a situation with two tables, each table having a primary key.

What results in deadlock?

A deadlock problem occurs when two (or more than two) operations already want to access resources locked by the other one. In this circumstance, database resources are affected negatively because both processes are constantly waiting for each other. This contention issue is terminated by the SQL Server intervention.

Does insert locks the table?

When inserting a record into this table, does it lock the whole table? Not by default, but if you use the TABLOCK hint or if you're doing certain kinds of bulk load operations, then yes.

Which can cause dead deadlock?

Deadlock occurs mainly when there are multiple dependent locks exist. In a thread and another thread tries to lock the mutex in reverse order occurs. One should pay attention to use a mutex to avoid deadlocks. Be sure to complete the operation after releasing the lock.


1 Answers

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.

like image 193
LoztInSpace Avatar answered Sep 20 '22 05:09

LoztInSpace