LP #1698825: Deadlock on COMMIT after ALTER TABLE on separate connection

**Reported in Launchpad by Romuald Brunet last update 19-06-2017 15:39:42

Server version: 5.6.20-68.0-56-log Percona XtraDB Cluster (GPL), Release 25.7, wsrep_25.7.r4126

Using ALTER TABLE to update the default of a column will provoke a deadlock on other connections that did read that table.

Example:

CREATE TABLE foobar (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
foo_id INT NOT NULL,
bar_id INT DEFAULT 1);

INSERT INTO foobar(foo_id) VALUES (1), (1), (1), (2), (2);

/* On connection 1 */
BEGIN;
SELECT * FROM foobar WHERE foo_id = 1;

/* On connection 2 */
ALTER TABLE foobar ALTER COLUMN bar_id SET DEFAULT 2; – instantaneous

/* On connection 1 */
COMMIT;-- -> ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Tested with MySQL 5.5.54 and MariaDB 10.1.23, their behavior is that the ALTER TABLE locks until the first transaction is commited.

Our current workaround is to use LOCK TABLE foobar WRITE in the ALTER TABLE so it will wait until the first transaction is commited