CODEWITHSUNDEEP
mysqlconcurrency
Pavel Patrin
Pavel Patrin

Reputation: 1736

Insert from select queries in MySQL 5.5 InnoDB

I have a query.

INSERT INTO users_resources_locks (resource_id, user_id)
SELECT resources.id, 123 FROM resources
LEFT JOIN users_resources_locks
    ON resources.id = users_resources_locks.resource_id
WHERE users_resources_locks.id IS NULL;

Table users_resources_locks has unique index on column resource_id.

Could concurrent query write into users_resources_locks between INSERT and SELECT part of my query?

For example is timeline below possible in repeatable read isolation level?

1) My query selects ((1, 123), (2, 123), (3, 123)).
2) Concurent query writes into users_resources_locks values ((1, 54321), (2, 54321)) and commits trasaction.
3) My query writes into users_resources_locks values ((1, 123), (2, 123), (3, 123)) and commits transaction fails because of unique constraint.

Upvotes: 0

Views: 76

Answers (1)

Rahul
Rahul

Reputation: 77876

NO, per my knowledge an DML statement like INSERT takes an exclusive record level implicit lock and so while one insert is in progress another INSERT/UPDATE can't go through. It depends on Reader/Writer locks and DB engine checks on lock compatibility.

Read Lock - R
Write Lock - W

With this a RW / WR/ RR are all compatible locks since read needs a shared lock (again depends on TRANSACTION ISOLATION LEVEL though) but a WW is a non-compatible lock always.

Also, if your ID column is a primary key or any key column like unique key then there is no way it can get duplicated.

Upvotes: 1

Related Questions