mysql row locking and realize lock when my code want

Question

I am using mysql in my java base web app. I want to lock particular row so that user can read row but can not update and delete. Row is locked still my code want. that means my code itself unlock the row.

Is it possible in mysql to lock particular row in such manner.

http://dev.mysql.com/doc/refman/5.0/en/internal-locking.html — Mithun Sasidharan, Dec 26 '11 at 12:57
http://stackoverflow.com/questions/1726129/mysql-table-locking-vs-row-locking — Mithun Sasidharan, Dec 26 '11 at 12:57
Isn't this what happens precisely when you use a transaction ? — Santosh, Dec 26 '11 at 13:31

score 1 · Accepted Answer · edited May 23 '17 at 12:27

I am not sure what is your use case and why do you want to EXPLICITLY lock a row programmatically. Generally this happens under the hood when you use JDBC/Transaction APIs.

Quoting @Keith Randall from the link shared above by @ Mithun Sasidharan,

"If you have an index on tblAreas.AreaID, then any transaction that includes WHERE tblAreas.AreaID in (...) will lock the index for those entries"

This will generally use and Optimistic Locking. What you are interested seems is Pessimistic locking.

In MySQL, one way to do is to use SELECT FOR UPDATE statement which will lock the selected rows for you. Please refer to the respective documentation for the usage.

mysql row locking and realize lock when my code want

1 Answers1