CODEWITHSUNDEEP
mysql
Dixon Ivey
Dixon Ivey

Reputation: 101

SELECT FOR UPDATE in multi thread environment

I', writing MySQL queries in a multi threaded environment so this query can be executed on any given number of threads. My db is MySQL 8 using InnoDB engine.

Let says I have a DB table with 10 numbers (1,2,3,4,5,6,7,8,9,10)

I have a SELECT ... FOR UPDATE query with a limit of 2 rows from a table in the database. FOR UPDATE will lock the rows to ensure isolation. If I have 5 threads that start at the same time will thread 1 grab entries 1 and 2, thread 2 see that thread 1 got entries 1 and 2 so it will go grab 3 and 4.. and so on.

Would it behavior this way?

Upvotes: 0

Views: 532

Answers (1)

Barmar
Barmar

Reputation: 780698

No, the locks should have no influence on the query plan. The queries will try to select whichever rows fit the WHERE and ORDER BY criteria. If they're locked by another thread, it will block.

Also, the locking will depend on whether the WHERE or ORDER BY clauses use indexed columns or not. If you examine non-indexed columns, it will have to scan the entire table to find or order the rows, which will effectively lock the entire table. If you restrict these clauses to indexed columns, it should just be able to set locks on those indexes.

See use of LIMIT, FOR UPDATE in SELECT statement in the MySQL Forum for some more information.

Upvotes: 2

Related Questions