CODEWITHSUNDEEP
mysqllockingsubquery
Jonny White
Jonny White

Reputation: 875

Mysql subquery locking

If I have a query like:

UPDATE table_x SET a = 1 WHERE id = ? AND (
    SELECT SUM(a) < 100 FROM table_x
)

And

Do I need to lock the table or will table_x be locked automatically as it's a subquery?

Upvotes: 2

Views: 3143

Answers (1)

BK435
BK435

Reputation: 3174

Assuming this is innodb table, You will have row level locking . So, even if they are 100 of these happening at a time, only ONE transaction will be able to acquire the lock on those rows and finish processing before the next transaction is to occur. There is no difference between how a transaction is processed for the update and the subquery. To the innodb engine this is all ONE transaction, not two separate transactions.

If you want to see what is going on behind the scenes when you run your query, type 'show engine innodb status' in the command line while the query is running.

Here is a great walkthrough on what all that output means.

If you want to read more about Innodb and row level locking, follow link here.

Upvotes: 1

Related Questions