It is not clear what you are trying to accomplish. But as far as I understand what you are asking, there is no built-in MySQL locking mechanism that will do what you need. (You want the session to be able to “lock” the string to prevent it from “reading” (or changing) by another session.
To accomplish what you are trying to do, it sounds like an application problem, not a database integrity problem.
The approach I would like to use for the solution would be to add two columns to the table:
locked_by - uniquely identify the session holding the row lock locked_at - the date/time the row lock was placed
For a session trying to get a lock on a row, I would check if that row was already locked by another session, and if not, mark the row as locked by this session:
UPDATE mytable SET locked_by = 'me' , locked_at = NOW() WHERE unique_row_identifer = someval AND locked_by IS NULL;
If the return from the update is “updated with zero rows”, you know that you did not receive the lock. If the return is not zero, you know that you have received a lock (at least one row).
To check if my session has already been locked in a row:
SELECT 1 FROM mytable t WHERE t.unique_row_identifier = someval AND locked_by = 'me';
As soon as I realized that I have a “lock” in the string, I could get it with a simple SELECT
SELECT ... WHERE unique_row_identifier = someval`
To release the lock, the session will return the locked_by and locked_at to NULL.
A read-only session can avoid reading a locked row by checking the values ​​in the locked_by column:
SELECT t.* FROM mytable t WHERE t.unique_row_identifier = someval AND t.locked_by IS NULL
A row will only be returned if it is not locked.
Please note that I would do a lock and check in one statement to avoid a race condition with simultaneous situations. If I ran SELECT to do a check and then UPDATE, there is a chance that another session will slip between these two separate statements ... it would be difficult to really make this happen without adding a significant delay. But if we worry about locking lines, we better do everything right.
Note that the value stored in the locked_at column comes into play when we want to check for locks that have been stored for a long time. The session may have taken some locks, and this session is gone, and these locks will never be released. You can schedule a separate maintenance task to view the table for really old locked_at values.
Alternatively, you can use locked_at to perform a more complex lock search, and consider that old locks have expired.
WHERE ( locked_at IS NULL OR locked_at < (NOW() + INTERVAL 24 HOUR) )
===
Note:
I have never used this approach in a production system before. The problem that my team usually has is the "last-win" scenario where updating can potentially replace new changes that have recently been made by others. But the problem we are solving seems to be very different from what you are trying to accomplish.
In order to solve the “last in gain” problem, we add one column “version” (a simple integer) to the table. When we retrieve a row, we retrieve the current value of the version column. When the session later wants to update the row, it checks that no other update has been added to the row by comparing the previously obtained version value with the current value in the table. If the version numbers match, we allow the line to be updated and increase the version number by one. (We do this all in one UPDATE statement, so the operation is atomic in order to avoid a race condition when two simultaneous sessions do not do both updates. We use this template because we really do not want the row to be locked by the session and the lock held by We just prevent simultaneous updates from overwriting each other, which again differs from this as if you are trying to execute.