Mysql subquery locking

Question

If I have a query like:

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

And

hundreds of this query could be made at exactly the same time
I need to be certain that a never gets to more than 100

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

I haven't tried testing it yet. My concern is that the sub query will be satisfied but if there are 100 of these happening concurrently then although it is satisfied initially, by the time it does the update it's not longer true. — Jonny White, Mar 17 '15 at 21:19
Oh no, I'm using MyIsam. Do you know for sure that there is locking for subqueries. It's not the most trivial of tests as I have to emulate lots of traffic so wanted to make sure that I'm going down the right path first. I couldn't find anything conclusive in MySql docs that says locking happens automatically for subqueries. — Jonny White, Mar 18 '15 at 09:07
@BK435 Actually innodb is not a problem. Where did you learn that subqueries cause row-level locking? Thanks! — Jonny White, Mar 18 '15 at 11:02

score 1 · Accepted Answer · answered Mar 18 '15 at 18:34

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.

Mysql subquery locking

1 Answers1