首先明确一个问题，Alter更改表结构，会加什么锁？
答：MDL（元数据表锁），就如同我们打开一个EXCEL文档时，用户A正在翻阅，此时用户B要增加一列。那么用户B要等待用户A翻阅完，才可以增加，否则用户A查询的数据就会不一致，不符合ACID事务四大特性。
案例
会话一 | 会话二 | 会话三 |
|---|---|---|
select sleep(1800),id from sbtest1 where id=1; | ||
注：休眠1800秒后查询id=1的记录 | ||
alter table sbtest1 add column city varchar(30); | ||
注：由于会话一未执行完，仍旧持有MDL锁，会话二执行DDL时无法获取到MDL元数据表锁，会出现锁等待Waiting for table metadata lock | ||
select * from sbtest1 where id=2; | ||
注：会话三等待事务二释放MDL锁，出现锁等待Waiting for table metadata lock |
在MariaDB 10.3版本里，可以通过Alter table ... NOWAIT来检测MDL锁，一旦被检测到，立即中止。
参考官方文档：https://mariadb.com/kb/en/wait-and-nowait/
在MySQL 8.0.35版本里，并没有提供相关语法，这对于你不熟悉数据库业务的情况下，执行DDL上线是非常危险的，会引发出一系列事故的发生。
可以通过下面的python脚本实现NOWAIT功能。
代码语言：javascript复制import re
import pymysql
from pymysql.constants import CLIENT
class TableAlteration:
def __init__(self, mysql_server, mysql_username, mysql_password, mysql_database, mysql_port, mysql_table):
self.mysql_server = mysql_server
self.mysql_username = mysql_username
self.mysql_password = mysql_password
self.mysql_database = mysql_database
self.mysql_port = mysql_port
self.mysql_table = mysql_table
self.db = None
self.cursor = None
def connect(self):
self.db = pymysql.connect(
host=self.mysql_server,
user=self.mysql_username,
password=self.mysql_password,
database=self.mysql_database,
port=self.mysql_port,
charset='utf8',
client_flag=CLIENT.MULTI_STATEMENTS
)
self.cursor = self.db.cursor()
def disconnect(self):
if self.cursor:
self.cursor.close()
if self.db:
self.db.close()
def alter_table(self, ddl_sql):
self.connect()
table_list = re.split(r"[ ] ", ddl_sql)
fetch_run_sql = """
SELECT
a.trx_id, trx_state, trx_started, b.id AS processlist_id, b.info,
b.user, b.host, b.db, b.command, b.state,
CONCAT('KILL QUERY ', b.id) as sql_kill_blocking_query
FROM
information_schema.INNODB_TRX a,
information_schema.PROCESSLIST b
WHERE
a.trx_mysql_thread_id = b.id
and b.info like '%{0}%'
ORDER BY
a.trx_started
""".format(table_list[2])
row = self.cursor.execute(fetch_run_sql)
if row != 0:
results = self.cursor.fetchall()
print('{0}表持有MDL元数据锁，无法更改表结构。'.format(table_list[2]))
for row in results:
print("trx_id: {0}".format(row[0]))
print("trx_state: {0}".format(row[1]))
print("trx_started: {0}".format(row[2]))
print("processlist_id: {0}".format(row[3]))
print("info: {0}".format(row[4]))
print("user: {0}".format(row[5]))
print("host: {0}".format(row[6]))
print("db: {0}".format(row[7]))
print("command: {0}".format(row[8]))
print("state: {0}".format(row[9]))
print("sql_kill_blocking_query: {0}".format(row[10]))
print("-" * 50)
else:
pass
self.disconnect()
# 使用示例
mysql_server = '192.168.198.239'
mysql_username = 'admin'
mysql_password = 'hechunyang'
mysql_database = 'test'
mysql_port = 3336
mysql_table = 'sbtest1'
ddl_sql = 'alter table sbtest1 add column city varchar(30)'
table_alteration = TableAlteration(mysql_server, mysql_username, mysql_password, mysql_database, mysql_port, mysql_table)
table_alteration.alter_table(ddl_sql)当检测到sbtest1表有未执行完的事务时，打印出正在执行的SQL，并给 出提示：
这对于你刚加入一家公司，不熟悉数据库业务情况下，非常有用，可以避免引发出一系列事故的发生。
