EverySELECTfromyourPythonprogrammayacquireametadata_MySQL

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 youSELECTin a Python program, your connection acquires a metadata lock.

These locks are not released until your program ends or explicitly ends the transaction usingCOMMITorROLLBACK.

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 executeALTER TABLEor 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: 3Duplicates: 0Warnings: 0

$ cat simple.py#!/usr/bin/env pythonimport sys, osimport mysql.connectordb = 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:4814 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 theSET @@session.autocommit = OFFstatement that disables autocommit. This behavior can be influenced in a few different ways, when usingMySQL Connector/Python:

1. Set theautocommitconnection argument to "True".
2. Modify theautocommitproperty of the connection after connecting.
3. ExecuteSET AUTOCOMMIT=0orAUTOCOMMIT=OFFafter connecting.

The facilities available may depend on the specific Python driver you are using. TheMySQLdbdriver, for instance, does not support anautocommitconnection 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!