Row and table level locking in MySQL

Row level locks and table level locks in MySQL

Row level locking the database automatically use the row level when every one transaction is hold lock on row then other transaction need to wait for acquired row level lock until first transaction commit.

Row locking example:

First session:

mysql> START TRANSACTION;
mysql> select capital,code2 from country where code2 = 'ZW' for update;
+---------+-------+
| capital | code2 |
+---------+-------+
|    4068 | ZW    |
+---------+-------+
1 row in set (0.00 sec)

Second Session:

mysql>  update country set capital=5000 where code2 = 'ZW';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

First Session:

mysql> commit;
Query OK, 0 rows affected (0.03 sec)

Second Session: 

mysql>  update country set capital=5000 where code2 = 'ZW';
Query OK, 0 rows affected (3.44 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Table level lock: We can acquire table level lock explicitly to avoid other to modified that table. Until we release the lock the other session cannot do acquire lock on table.

Syntax:
LOCK TABLES table_name READ|WRITE;

Example of Table level lock:

-- Example of read lock
LOCK TABLES table_name READ;
Eg:
LOCK TABLES COUNTRY READ;

--Example of Write lock
LOCK TABLES table_name WRITE;

--Check the lock tables
SHOW OPEN TABLES [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]
Eg:
mysql> SHOW OPEN TABLES where in_use >= 1;
+----------+---------+--------+-------------+
| Database | Table   | In_use | Name_locked |
+----------+---------+--------+-------------+
| test_bkp | country |      1 |           0 |
+----------+---------+--------+-------------+
1 row in set (0.00 sec)

--Unlock the tables lock
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

--Verify to release the locks
mysql> SHOW OPEN TABLES where in_use >= 1;
Empty set (0.00 sec)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.