MySQL Basics-lock mechanism, table level lock, row level lock

One, table-level lock

1. Read lock,lock table t_student Read, add read lock, so that other Sessiona and sessionb can not modify the data, just read the data.

Show processlist; View process, modify the state is waiting for the table level lock, has been waiting for 8s

After unlocking unlock tables, SQL that modifies the data is also executed successfully, as shown in

2. Write the lock , when a process in a table after the write lock, after the process if the update (write, insert, UPDATE, delete), if the write lock is not released, the other process even to view the table permissions are not, only wait for it to release the write lock value, Other processes will be able to complete the corresponding operation. If the process does not update the table, the other processes can only do query operations, but the update operation cannot be implemented.

Second, row-level lock

The MyISAM engine does not support row-level locks, and InnoDB's engines support row-level locks. However, the InnoDB engine is implemented by locking the index entries on the index, which means that the InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise INNODB will use a table lock.

As shown, both Sessiona and SESSIONB have modified the record with ID 1 in the transaction, Sessiona executed first, and the sessionb is blocked. This is Sessiona a row-level lock on a row with ID 1, causing the other session to be blocked when the row is logged.

Looking at the next scenario, Sessiona modifies the record with ID 1, sessionb the record with ID 2, but does not cause blocking. Because row-level locks depend on the index.

It is important to note, however, that if you do not rely on the index to modify the table, the InnoDB engine uses the table-level lock by default. For example, if the ID in the table t_student is not a primary key or index, then table-level locks are enabled.

Third, deadlock

Originally Sessiona and SESSIONB respectively enabled Id=1 and id=2 row-level locks, but then Sessiona and sessionb respectively modified id=2 and id=1 data, the equivalent of each to operate the lock is not their own. It creates a deadlock.

MySQL Basics-lock mechanism, table level lock, row level lock