Can I manually lock a MySQL table?

Question

I have multiple independent processes, Scripts A and B, that each access the same table. I would like to Script A to read a record from the table then maybe modify that record (or maybe not).

The thing is that I need to keep Script B from accessing that particular record In the midst of that. Is there a manual lock perhaps? Something that will keep Script B out for just those few milliseconds?

Thanks

Refer: https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html — Madhur Bhaiya, Nov 20 '18 at 19:55
Don't forget about transaction isolation level and storage engine involved too — rkosegi, Nov 20 '18 at 19:58

score 1 · Answer 1 · answered Nov 20 '18 at 20:14

You don't want to lock the entire table right?

For InnoDB table a select...for update should do, it will lock the record (just add for update at the end of the query).

On your script A:

Create a transaction
Do a select for update
If you want to update the record, do so.
Commit the transaction when you finish.

On your script B do a select for update as well, it will wait until script A release the lock.

Can I manually lock a MySQL table?

1 Answers1