How can I lock a record in MySQL?
For example, I have a record that is being accessed by user 000, and I want to lock this record so users other than 000 can not access it. Is it possible?
How can I lock a record in MySQL?
For example, I have a record that is being accessed by user 000, and I want to lock this record so users other than 000 can not access it. Is it possible?
First you need to ensure the following two prerequisites for registry lock to work:
You can run registry locking in MySQL in two ways:
1) SELECT ... FOR UPDATE
Any lock ending with the FOR UPDATE
command will not allow other transactions to read, update, or delete the record. Other transactions may read such records only after the first transaction is completed (commited) or rolled back.
Example:
; Esta consulta não irá permitir que outras transações leiam o registro com id=10;
; Ela também não irá permitir atualizações ou exclusões di registro.
SELECT * FROM NomeDaTabela WHERE id=10 FOR UPDATE
2) LOCK IN SHARED MODE
Any lock with the LOCK IN SHARED MODE
command will allow other transactions to read the locked record but will not allow other transactions to update or delete the record. Other transactions may update or delete the record when the first transaction is completed or canceled.
Example:
; Esta consulta não irá permitir que outras transações atualizem ou excluam o registro de id=10.
; Ela permite que outras transações leiam o registro de id=10.
SELECT * FROM NomeDaTabela WHERE id=10 LOCK IN SHARE MODE;
This response has been adapted from the "Row Locking With MySQL" article, available at link
This process is known as Deadlock .
Check out the MySQL documentation, I hope this link helps you as well as other people:
A shared (S) lock allows the transaction that holds the lock to read a record.
A unique lock (X) allows the transaction that keeps the lock from updating or deleting a line.
Example:
This example involves two clients, client A and client B.
Client A creates the table containing a record and starts the transaction by selecting this line,
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)
mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.10 sec)
Then client B initiates the transaction and performs a delete on this line:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM t WHERE i = 1;
Result:
mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
This example was taken from the MySQL site, I did not detail the whole process, but you can check the link I provided.
I hope it helps you as you helped me learn something new.