Registration lock

Question

How could I lockar a record in Mysql?

For example, I have a record that is being accessed by the user 000, and I want to lock this record so that users other than 000 cannot access it. That is possible?

Answer 1

First you need to ensure the following two prerequisites for the registry lock to work:

1. The storage engine of your table must be configured to Innodb.
2. Your record lock query must be executed after starting a transaction.

You can run registry lock in Mysql in two ways:

1) SELECT ... FOR UPDATE

Any lock concluding with the command FOR UPDATE will not allow other transactions to read, update or delete the record. Other transactions may read these records only after the first transaction is completed (commited) or canceled (roll 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 command LOCK IN SHARED MODE will allow other transactions to read the blocked 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 cancelled.

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 was adapted from the article in English "Row Locking With Mysql", available in http://www.xpertdeveloper.com/2011/11/row-locking-with-mysql/

Answer 2

This process is known as Deadlock.

Check out the Mysql documentation, I hope this link helps you as well as other people:

https://dev.mysql.com/doc/refman/5.5/en/innodb-lock-modes.html

A shared lock (S) 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 row, follows:

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 starts the transaction and executes a delete on this line:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> DELETE FROM t WHERE i = 1;

Upshot:

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 refer to the link I provided.

I hope it helps you as much as it helped me to learn something new.