Every SELECT from your Python program may acquire a metadata lock!

Metadata locking has been an exciting adventure for the last couple years in MySQL and MariaDB. Users and applications using only MyISAM tables are learning the joys of locking conflicts between transactions/connections. Users with InnoDB tables who either rely on global autocommit behavior or have explicitly set that globally in their configuration might be surprised to find Some users are seeing more than their fair share of these crop up in some very surprising places due to a particular, pernicious Python behavior: PEP-249 mandates that autocommit “must be initially off”. Every time your Python programs/scripts connect to MySQL or MariaDB, a new transaction is started, and, crucially: by default, every time you SELECT in a Python program, your connection acquires a metadata lock. These locks are not released until your program ends or explicitly ends the transaction using COMMIT or ROLLBACK. A long-running monitoring tool or something that may connect, execute a couple simple SELECT statements, and keep its connection for some period of time, can cause havoc if you have other processes that try to execute ALTER TABLE or other DDL on those tables. MariaDB 10.0 gives us a great plugin to inspect metadata locks, so we can see this behavior in action.

MariaDB> create table test.t1 (id int unsigned not null auto_increment primary key) engine=myisam;
Query OK, 0 rows affected (0.02 sec)

MariaDB> insert into test.t1 values (),(),();
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
$ cat simple.py
#!/usr/bin/env python

import sys, os
import mysql.connector

db = mysql.connector.Connect(user='root')

cursor = db.cursor()
cursor.execute("SELECT 1 FROM test.t1")
cursor.fetchall()
cursor.close()

cursor = db.cursor()
cursor.execute("SELECT SLEEP(60)")
cursor.fetchall()
cursor.close()

db.close()

$ python simple.py
MariaDB> install soname 'metadata_lock_info';
Query OK, 0 rows affected (0.00 sec)

MariaDB> show processlist;
+----+------+-----------------+------+---------+------+------------+------------------+----------+
| Id | User | Host            | db   | Command | Time | State      | Info             | Progress |
+----+------+-----------------+------+---------+------+------------+------------------+----------+
|  3 | root | localhost       | test | Query   |    0 | init       | show processlist |    0.000 |
| 14 | root | localhost:59875 | NULL | Query   |   40 | User sleep | SELECT SLEEP(60) |    0.000 |
+----+------+-----------------+------+---------+------+------------+------------------+----------+
2 rows in set (0.00 sec)

MariaDB> select * from information_schema.metadata_lock_info;
+-----------+-----------------+-----------------+---------------------+--------------+------------+
| THREAD_ID | LOCK_MODE       | LOCK_DURATION   | LOCK_TYPE           | TABLE_SCHEMA | TABLE_NAME |
+-----------+-----------------+-----------------+---------------------+--------------+------------+
|        14 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | test         | t1         |
+-----------+-----------------+-----------------+---------------------+--------------+------------+
1 row in set (0.00 sec)

Here’s an excerpt from the general query log, to show what statements this connection has executed since it began:

140624 17:24:48    14 Connect   root@localhost as anonymous on
                   14 Query     SET NAMES 'utf8' COLLATE 'utf8_general_ci'
                   14 Query     SET @@session.autocommit = OFF
                   14 Query     SELECT 1 FROM test.t1
                   14 Query     SELECT SLEEP(60)

There we can see the SET @@session.autocommit = OFF statement that disables autocommit. This behavior can be influenced in a few different ways, when using MySQL Connector/Python:

  1. Set the autocommit connection argument to “True”.
  2. Modify the autocommit property of the connection after connecting.
  3. Execute SET AUTOCOMMIT=1 or AUTOCOMMIT=ON after connecting.

The facilities available may depend on the specific Python driver you are using. The MySQLdb driver, for instance, does not support an autocommit connection argument. If you have Python programs that connect to MySQL or MariaDB, make sure you understand their autocommit behavior. It’s a good practice, no matter the language or API you’re using, to explicitly set the autocommit behavior that your program relies on. Good luck avoiding those metadata locks!