Log Lock

5

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?

    
asked by anonymous 09.07.2015 / 15:23

2 answers

3

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

  • The storage engine of your table must be configured to InnoDB.
  • Your registry lock query should be performed after starting a transaction.
  • 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

        
    09.07.2015 / 16:41
    0

    This process is known as Deadlock .

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

    link

    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.

        
    09.07.2015 / 16:05