Mysql table locking

Locking is important in many scenarios to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. for example altering table definition online or any kind of table definition changes.locking Mysql provides an option to lock table/s with different types of locks, depends on need.

syntax for lock table:

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type:
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE

UNLOCK TABLES

Following are the examples for READ and WRITE LOCK:

READ LOCK:

session1> create table t1( c1 int);
Query OK, 0 rows affected (0.06 sec)

session1> insert into test.t1 values(1001);
Query OK, 1 row affected (0.01 sec)

session1> lock table t1 READ;
Query OK, 0 rows affected (0.00 sec)

session1> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
session1> insert into t1 values(1002);
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated

Session1 acquired READ lock on table t1 explicitly. After applying READ lock on table users can read the table but not write it.

session2> lock table t1 READ;
Query OK, 0 rows affected (0.00 sec)

session2> insert into t1 values(1002);
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated

session3> select * from t1;
+------+
| c1 |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

Multiple sessions can acquire a READ lock on the table at the same time and other sessions can read the table without explicitly acquiring a READ lock.

currently, READ lock s acquired by session1 and session2, both locks need to be unlocked in order to perform a write operation on lock table.

session1> UNLOCK TABLES;
session1> insert into t1 values(1005);

INSERT operation executed from session1  will go in waiting state, since READ lock acquired on table t1 by session2 and not  released yet.

You can see this using:

session3> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
| 2 | session1 | localhost | test | Query | 89 | Waiting for table metadata lock | insert into test.t1 values(1005) |
| 3 | session3 | localhost | test | Query | 0 | starting | show processlist |
| 4 | session2 | localhost | test | Sleep | 77 | | NULL |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
3 rows in set (0.00 sec)

After releasing READ lock by session2 insert operation will execute on t1 table.

session2> UNLOCK TABLES;
session1>  select * from t1;
+------+
| c1 |
+------+
| 1001 |
| 1005 |
+------+
2 rows in set (0.00 sec)

NOTE: FLUSH TABLES Different form UNLOCK TABLES:

FLUSH TABLE:  Closes all open tables, forces all tables in use to be closed, and flushes the query cache. FLUSH TABLES also removes all query results from the query cache, like the RESET QUERY CACHE statement. FLUSH TABLE will not work when table acquires READ LOCK.

UNLOCK TABLES: UNLOCK TABLES explicitly releases any table locks held by the current session. LOCK TABLES implicitly releases any table locks held by the current session before acquiring new locks.

********************************************************************************************************************************************

WRITE LOCK:

-The session that holds the lock can read and write the table.

session1> lock table t1 write;
Query OK, 0 rows affected (0.00 sec)

session1> insert into test.t1 values(1006);
Query OK, 1 row affected (0.01 sec)

session1> select count(*) from t1;
+------+
| c1 |
+------+
| 1001 |
| 1005 |
| 1006 |
+------+
3 rows in set (0.00 sec)

-Only the session that holds the lock can access the table. No other session can access it until the lock is released.

session2> select count(*) from t1;
and 
session3> insert into test.t1 values(1002);

session1> show processlist;
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
| 2 | session1 | localhost | test | Query | 0 | starting | show processlist |
| 3 | session2 | localhost | test | Query | 127 | Waiting for table metadata lock | select count(*) from t1 |
| 4 | session3 | localhost | test | Query | 116 | Waiting for table metadata lock | insert into test.t1 values(1002) |
+----+------+-----------+------+---------+------+---------------------------------+----------------------------------+
3 rows in set (0.00 sec)

Look into performance_schema.metadata_locks table for more status information on table locks. (Thanks for the hint daniel )

– Enable Locking related instruments (if it’s not enabled) :

UPDATE performance_schema.setup_instruments SET ENABLED=’YES’, TIMED=’YES’ WHERE NAME=’wait/lock/metadata/sql/mdl’;

SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA=’test’ AND OBJECT_NAME LIKE ‘t_’;

-Lock requests for the table by other sessions block while the WRITE lock is held.

All set  🙂 ……

Advertisements

8 thoughts on “Mysql table locking

  1. With performance schema you can see the locks:

    mysql 4 > UPDATE performance_schema.setup_instruments SET ENABLED=’YES’, TIMED=’YES’ WHERE NAME=’wait/lock/metadata/sql/mdl’;
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

    After ‘LOCK TABLE t2 WRITE’:
    mysql 4 > SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA=’test’ AND OBJECT_NAME LIKE ‘t_’\G
    *************************** 1. row ***************************
    OBJECT_TYPE: TABLE
    OBJECT_SCHEMA: test
    OBJECT_NAME: t2
    OBJECT_INSTANCE_BEGIN: 139764409971040
    LOCK_TYPE: SHARED_NO_READ_WRITE
    LOCK_DURATION: TRANSACTION
    LOCK_STATUS: GRANTED
    SOURCE: sql_parse.cc:5618
    OWNER_THREAD_ID: 26
    OWNER_EVENT_ID: 13
    1 row in set (0.00 sec)

    After ‘LOCK TABLE t1 READ’:
    mysql 4 > SELECT * FROM performance_schema.metadata_locks WHERE OBJECT_SCHEMA=’test’ AND OBJECT_NAME LIKE ‘t_’\G
    *************************** 1. row ***************************
    OBJECT_TYPE: TABLE
    OBJECT_SCHEMA: test
    OBJECT_NAME: t1
    OBJECT_INSTANCE_BEGIN: 139764409894112
    LOCK_TYPE: SHARED_READ_ONLY
    LOCK_DURATION: TRANSACTION
    LOCK_STATUS: GRANTED
    SOURCE: sql_parse.cc:5618
    OWNER_THREAD_ID: 26
    OWNER_EVENT_ID: 15
    1 row in set (0.00 sec)

  2. Pingback: Bitcoin Core Developer: Rely on Proper Bitcoin Storage Methods - Altcoin Today
  3. Lalit, very nice explanation of table locking but here is one confusion with me. I am using innoDB storage engine and i can easily lock innoDB whole tables but i have read that innoDB only supports row level locking. Does innoDB support both table level locking and row level locking ? Please kindly will you clear my concept ?

    • Hi Sunny,
      Thank you and the Very good question asked by you.

      Innodb Support Both table and row level locking depends on the transaction we are executing and scenarios.

      For example:
      In General case,
      DDL transaction will use table level locking.
      DML transaction will use row-level locking.

      It’s not necessary that locking always work as above, Let see example:
      An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns.
      For above case, we are inserting row (DML transaction) and it uses table level locking.

      Following are the few important points we should consider for InnoDB locking,

      If autocommit = 0, InnoDB honors LOCK TABLES; MySQL does not return from LOCK TABLES … WRITE until all other threads have released all their locks to the table. The default value of innodb_table_locks is 1, which means that LOCK TABLES causes InnoDB to lock a table internally if autocommit = 0.

      In MySQL 5.7, innodb_table_locks = 0 has no effect for tables locked explicitly with LOCK TABLES … WRITE. It does have an effect for tables locked for read or write by LOCK TABLES … WRITE implicitly (for example, through triggers) or by LOCK TABLES … READ.

      Please refer this for more info:
      https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-shared-exclusive-locks

      I hope this explanation make things clear.

      • Lalit your explanation is well, i am beginner to database and learned table locking. Can you please tutor me all about row level locking, i have searched dev.mysql but they provide knowledge in a difficult way and i am unable to learn in that difficukt way. I will be very thankful to you.

  4. It would be really helpful to you if you first go through the concept of ISOLATION Levels and MVCC. This will make thing clear to you for generic RDBMS locking concept. After that, I’m sure thing will very clear to you.
    read this once you complete Isolation and MVCC concepts.
    Ref: https://dev.mysql.com/doc/refman/5.7/en/locking-issues.html

    Soon I will try to write a blog for this if possible.
    Thank you for writing back.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s