Edit Question - KnowledgeHub

Title Body

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: 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). 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?

Tags (comma-separated) Cancel