How to implement row level locking

Question

This is not working for the row level locking I just want to know if I can select the row like

select * from table where folder like %344443%** 

then update the row with

update table set folder = '{"bin":"44456","venv":4366}' where id = 'i-instanceid'

Answer 1

You can't.

The problem is that the UPDATE must scan the entire table to find the row(s) you need to change. In doing so, it locks the entire table.

Don't bury things that you want to search on inside JSON strings. Have them as indexed columns on their own. This should let you lock a single row, and run the Update much faster.

Or look into indexing parts JSON columns. Such is still evolving. What version of MySQL are you using?

Furthermore, why select the id first, then do the update? Can't you simply do the update? If you are actually doing something else in the "transaction", say so. You may need the SELECT. At that point, it would need to be SELECT ... FOR UPDATE.