首页 » MySQL/TiDB/GoldenDB, ORACLE 9i-23ai, PostgreSQL/GaussDB » Oracle、MySQL、PostgreSQL等数据库比较系列(十四): drop table being selected
Oracle、MySQL、PostgreSQL等数据库比较系列(十四): drop table being selected
对于一个连续7*24小时的业务，如果session 1正在select查询一张大表，而另一个session尝试drop 相同的表，会发生什么？对于最流行的MVCC数据库oracle,mysql,postgreql需要对比，因为drop不只是字典表更新标记，还需要回收物理空间。在这几个数据库中的表现一样吗？
#Oracle
session 1
create table anbob.mytab as select * from dba_objects; select count(*) from anbob.mytab a,anbob.mytab b; -- ing
session 2
SQL> set pause on pages 10 SQL> select * from mytab; OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS T G S NAMESPACE EDITION_NAME SHARING E O A DEFAULT_COLLATION D S CREATED_APPID CREATED_VSNID MODIFIED_APPID MODIFIED_VSNID ------------------------------ ------------------------------ ------------------------------ ---------- -------------- ----------------------- ------------------- ------------------- ------------------- ------- - - - ---------- -------------------------------------------------------------------------------------------------------------------------------- ------------------ - - - ---------------------------------------------------------------------------------------------------- - - ------------- ------------- -------------- -------------- SYS I_FILE#_BLOCK# 9 9 INDEX 2019-04-17 00:56:14 2019-04-17 00:56:14 2019-04-17:00:56:14 VALID N N N 4 NONE Y N N N SYS I_OBJ3 38 38 INDEX 2019-04-17 00:56:14 2019-04-17 00:56:14 2019-04-17:00:56:14 VALID N N N 4 NONE Y N N N SYS I_TS1 45 45 INDEX 2019-04-17 00:56:14 2019-04-17 00:56:14 2019-04-17:00:56:14 VALID N N N 4 NONE Y N N N ...
session 3
SQL> select * from v$lock where sid=106; --session 1 sid no rows selected SQL> drop table anbob.mytab; Table dropped.
note:
此时的session 1和session 2一直在执行之前的查询，session 2回车还可以返回之前的数据。 但注意如果物理block补另一对象reuse，查询可能会提示ORA-08103: object no longer exists。在oracle中查询不会增加任何lock, 所以session 3可以正常删除，并且在查询中还可以再创建同名对象。
即使有DML 事务堵塞了DDL，在oracle也可以非常方便的排查如有DDL_LOCK_TIMEOUT, 默认立即ora-54报错释放，不会影响后面的业务，同样还有ASH的blocker_session，或HANGANALYZE等分析。
MySQL InnoDB
session 1
--set autocommit=0; MYSQL_root@127.0.0.1 [anbob]> start transaction; Query OK, 0 rows affected (0.00 sec) MYSQL_root@127.0.0.1 [anbob]> select avg(a.id) from testa a,testa b ,testa c,testa d;
session 2
MYSQL_root@127.0.0.1 [anbob]> drop table testa; -- wait
session 3
MYSQL_root@127.0.0.1 [anbob]> insert into testa select * from testa; -- wait
Note:
session 1的事务堵塞了session 2 DDL, session 2需要排他MDL, session2 又堵了session 3，这里session 3是DML，即使是select一样被读。
session 4
MYSQL_root@127.0.0.1 [anbob]> show processlist; +----+-----------------+-----------------+-------+---------+------+---------------------------------+---------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------------+-------+---------+------+---------------------------------+---------------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 3170 | Waiting on empty queue | NULL | | 8 | root | localhost:27302 | anbob | Sleep | 6 | | NULL | | 9 | root | localhost:27464 | anbob | Query | 0 | starting | show processlist | | 10 | root | localhost:27470 | anbob | Query | 1822 | Waiting for table metadata lock | drop table testa | | 11 | root | localhost:27496 | anbob | Query | 1709 | Waiting for table metadata lock | insert into testa select * from testa | +----+-----------------+-----------------+-------+---------+------+---------------------------------+---------------------------------------+ 5 rows in set (0.00 sec) MYSQL_root@127.0.0.1 [anbob]> select * from performance_schema.metadata_locks t where OBJECT_NAME='testa'; +-------------+---------------+-------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID | +-------------+---------------+-------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+ | TABLE | anbob | testa | NULL | 140974864 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:6056 | 48 | 79 | | TABLE | anbob | testa | NULL | 140575756950864 | EXCLUSIVE | TRANSACTION | PENDING | sql_parse.cc:6056 | 126 | 32 | | TABLE | anbob | testa | NULL | 140575958492112 | SHARED_WRITE | TRANSACTION | PENDING | sql_parse.cc:6056 | 127 | 34 | +-------------+---------------+-------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+ 3 rows in set (0.00 sec)
session 1 是SHARED_READ， session 2请求EXCLUSIVE， session 3是SHARED_WRITE.
METADATA LOCK超时
MYSQL_root@127.0.0.1 [anbob]> show variables like '%meta%'
-> ;
+---------------------------------------+---------+
| Variable_name | Value |
+---------------------------------------+---------+
| binlog_row_metadata | MINIMAL |
| innodb_stats_on_metadata | OFF |
| performance_schema_max_metadata_locks | -1 |
| resultset_metadata | FULL |
+---------------------------------------+---------+
4 rows in set (0.05 sec)
MYSQL_root@127.0.0.1 [anbob]> show variables like '%lock%'
-> ;
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| block_encryption_mode | aes-128-ecb |
| innodb_api_disable_rowlock | OFF |
| innodb_autoinc_lock_mode | 2 |
| innodb_deadlock_detect | ON |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_print_all_deadlocks | OFF |
| innodb_status_output_locks | OFF |
| innodb_table_locks | ON |
| key_cache_block_size | 1024 |
| lock_wait_timeout | 31536000 |
| locked_in_memory | OFF |
| max_write_lock_count | 18446744073709551615 |
| performance_schema_max_metadata_locks | -1 |
| performance_schema_max_rwlock_classes | 60 |
| performance_schema_max_rwlock_instances | -1 |
| performance_schema_max_table_lock_stat | -1 |
| query_alloc_block_size | 8192 |
| range_alloc_block_size | 4096 |
| skip_external_locking | ON |
| transaction_alloc_block_size | 8192 |
+-----------------------------------------+----------------------+
MYSQL_root@127.0.0.1 [anbob]> show variables like '%timeout%';
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| mysqlx_connect_timeout | 30 |
| mysqlx_idle_worker_thread_timeout | 60 |
| mysqlx_interactive_timeout | 28800 |
| mysqlx_port_open_timeout | 0 |
| mysqlx_read_timeout | 30 |
| mysqlx_wait_timeout | 28800 |
| mysqlx_write_timeout | 60 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
+-----------------------------------+----------+
20 rows in set (0.00 sec)
Note:
mysql的MDL不受innodb_lock_wait_timeout 参数控制, 官方文档metadata lock release记录的是直到事务结束,Vineet Khanna’s archive 可以配置wait_timeout参数，默认28800秒,
Mitigating the MetaData Lock Issues ,There are various solutions to tackling MDL:
1.Appropriate setting of wait_timeout variable which will kill stuck/sleep threads after a certain time.
2.Configure pt-kill to get rid of stuck/sleep threads
3. Fix code where transactions are not committed after performing DB queries
而mariaDB 提到是lock_wait_timeout ，但参数默认是1年，几乎也不会等到那会。
找到METADATA LOCK HOLDER
# kill all the Sleep queries
MYSQL_root@127.0.0.1 [anbob]> SELECT CONCAT('CALL mysql.rds_kil ( ',id,')',';') FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND='Sleep' AND TIME > 10 ;
+--------------------------------------------+
| CONCAT('CALL mysql.rds_kil ( ',id,')',';') |
+--------------------------------------------+
| CALL mysql.rds_kil ( 8); |
+--------------------------------------------+
1 row in set (0.00 sec)
MYSQL_root@127.0.0.1 [anbob]> SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO FROM performance_schema.metadata_locks INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID WHERE PROCESSLIST_ID <> CONNECTION_ID();
+-------------+---------------+-------------+---------------------+-------------+-----------+----------------+---------------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_INFO |
+-------------+---------------+-------------+---------------------+-------------+-----------+----------------+---------------------+
| TABLE | anbob | test2 | SHARED_READ | GRANTED | 48 | 8 | select * from test2 |
| GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE | GRANTED | 49 | 9 | drop table test2 |
| BACKUP LOCK | NULL | NULL | INTENTION_EXCLUSIVE | GRANTED | 49 | 9 | drop table test2 |
| SCHEMA | anbob | NULL | INTENTION_EXCLUSIVE | GRANTED | 49 | 9 | drop table test2 |
| TABLE | anbob | test2 | EXCLUSIVE | PENDING | 49 | 9 | drop table test2 |
| TABLE | anbob | test2 | SHARED_READ | PENDING | 50 | 10 | select * from test2 |
+-------------+---------------+-------------+---------------------+-------------+-----------+----------------+---------------------+
6 rows in set (0.00 sec)
MYSQL_root@127.0.0.1 [anbob]> select CONCAT_WS('|',s.program_name,ifnull(s.db,'NULL'),user) "pgram|db|user",
CONCAT_WS('|',s.pid,s.conn_id,s.thd_id) "pid|conn|thd",
s.command,
s.time,
s.rows_examined "rows",
ifnull(s.current_statement,s.last_statement) last_active_statement,
CONCAT_WS('|',w.locked_table_name,w.locked_index,w.locked_type,w.waiting_lock_mode,w.blocking_lock_mode) "l_table|index|ltype",
CONCAT_WS('|',w.blocking_trx_id,w.blocking_pid) "b_xid|pid"
from sys.session s
left join sys.innodb_lock_waits w
on s.conn_id=w.waiting_pid
WHERE s.conn_id!=connection_id() and s.trx_state='ACTIVE';
+----------------------------+--------------+---------+------+------+-----------------------+---------------------+-----------+
| pgram|db|user | pid|conn|thd | command | time | rows | last_active_statement | l_table|index|ltype | b_xid|pid |
+----------------------------+--------------+---------+------+------+-----------------------+---------------------+-----------+
| mysql|anbob|root@localhost | 2059|8|48 | Sleep | 306 | 1 | select * from test2 | | |
+----------------------------+--------------+---------+------+------+-----------------------+---------------------+-----------+
1 row in set (0.55 sec)
Note:
在mysql 中可以看到select过程中是堵塞drop table的，增加了metadata lock元数据锁，同时DDL又会堵塞DML和select，而且MDL默认timeout时间很长，所以在mysql需要注意查杀MDL， 如果slave上查询，从master同步过去的DDL会怎么样呢？ Franck Pachot 测试在slave上的select等待很短会立即导致slave上的select 被kill掉,并提示表不存在。
# PostgreSQL
session 1
anbob=# select * from test1; id | charset ----+--------- (0 rows) Time: 0.612 ms anbob=# select id,pg_sleep(1) from test1; id | pg_sleep ----+---------- (0 rows) Time: 0.670 ms anbob=# insert into test1 values(1,null); INSERT 0 1 Time: 24.449 ms anbob=# select id,pg_sleep(1) from test1; id | pg_sleep ----+---------- 1 | (1 row) Time: 1001.466 ms anbob=# insert into test1 values(1,null); INSERT 0 1 anbob=# select id,pg_sleep(1) from test1; id | pg_sleep ----+---------- 1 | 1 | (2 rows) Time: 2003.639 ms anbob=# insert into test1 values(1,null); INSERT 0 1 Time: 6.034 ms anbob=# select id,pg_sleep(300) from test1; -- sleeping
session 2
anbob=# drop table test1; -- waiting
session 3
anbob=# select * from test1; -- waiting
session 4
anbob=# select datid,datname,pid,usename,application_name,query_start,substr(query,1,40) as c1 from pg_stat_activity; datid | datname | pid | usename | application_name | query_start | c1 -------+----------+-----------------+---------+------------------------+-------------------------------+------------------------------------------ 16385 | anbob | 140310382233344 | og | gsql | 2023-06-29 15:47:11.809052+08 | select * from test1; 16385 | anbob | 140310399014656 | og | gsql | 2023-06-29 15:44:55.354664+08 | drop table test1; 16385 | anbob | 140310264739584 | og | gsql | 2023-06-29 15:47:34.885612+08 | select datid,datname,pid,usename,applica 16385 | anbob | 140310415795968 | og | gsql | 2023-06-29 15:38:41.970753+08 | select id,pg_sleep(300) from test1; anbob=# select locktype,database,relation,page,virtualxid,mode,granted,pid from pg_locks where pid in(140310415795968,140310399014656,140310382233344) order by pid; locktype | database | relation | page | virtualxid | mode | granted | pid ---------------+----------+----------+------+------------+---------------------+---------+----------------- relation | 16385 | 16386 | | | AccessShareLock | f | 140310382233344 virtualxid | | | | 17/12 | ExclusiveLock | t | 140310382233344 transactionid | | | | | ExclusiveLock | t | 140310399014656 virtualxid | | | | 16/9 | ExclusiveLock | t | 140310399014656 relation | 16385 | 16386 | | | AccessExclusiveLock | f | 140310399014656 relation | 16385 | 16386 | | | AccessShareLock | t | 140310415795968 virtualxid | | | | 14/30 | ExclusiveLock | t | 140310415795968 (7 rows) anbob=# select * from dba_waiters; blocker_pid | blocker_user | blocker_query_start | blocker_query | waiter_pid | waiter_user | waiter_query | waiter_query_start | waited -----------------+--------------+---------------------+----------------------+-----------------+-------------+----------------------+--------------------+-------- 140310415795968 | og | 20230629 16:02:18 | select id,pg_sleep(3 | 140310382233344 | og | select * from test1; | 20230629 16:02:25 | 9 140310415795968 | og | 20230629 16:02:18 | select id,pg_sleep(3 | 140310399014656 | og | drop table test1; | 20230629 16:02:22 | 12 (2 rows)
Note:
在postgresql和opengauss中表现和mysql差不多，select 会堵塞DDL, 持有对象级的访问共享锁AccessShareLock与排他锁ExclusiveLock不兼容
OPENGAUSS TIMEOUT参数
anbob=# \! sh show timeout
archive_timeout | 0 | Forces a switch to the next xlog file if a new file has not been started within N seconds.
authentication_timeout | 1min | Sets the maximum allowed time to complete client authentication.
autoanalyze_timeout | 5min | Sets the timeout for auto-analyze action.
basebackup_timeout | 10min | Sets the timeout in seconds for a reponse from gs_basebackup.
checkpoint_timeout | 15min | Sets the maximum time between automatic WAL checkpoints.
checkpoint_wait_timeout | 1min | Sets the maximum wait timeout for checkpointer to start.
dcf_connect_timeout | 60000 | Sets the connect timeout of local DCF node.
dcf_election_timeout | 3 | Sets the election timeout of local DCF node.
dcf_socket_timeout | 5000 | Sets the socket timeout of local DCF node.
deadlock_timeout | 1s | Sets the time to wait on a lock before checking for deadlock.
fault_mon_timeout | 5min | how many miniutes to monitor lwlock. 0 will disable that
gpc_clean_timeout | 30min | Set the maximum allowed duration of any unused global plancache.
gs_clean_timeout | 1min | Sets the timeout to call gs_clean.
idle_in_transaction_session_timeout | 0 | Sets the maximum allowed idle time between queries, when in a transaction.
incremental_checkpoint_timeout | 1min | Sets the maximum time between automatic WAL checkpoints.
lockwait_timeout | 20min | Sets the max time to wait on a lock acquire.
logical_sender_timeout | 30s | Sets the maximum time to wait for logical replication.
partition_lock_upgrade_timeout | 1800 | Sets the timeout for partition lock upgrade, in seconds
pldebugger_timeout | 15min | Sets the receive timeout (s) of pldebugger.
session_timeout | 10min | Set the maximum allowed duration of any unused session.
statement_timeout | 0 | Sets the maximum allowed duration of any statement.
tcp_user_timeout | 0 | Maximum timeout of TCP retransmits.
update_lockwait_timeout | 2min | Sets the max time to wait on a lock acquire when concurrently update same tuple.
wal_flush_timeout | 2 | set timeout when iterator table entry.
wal_receiver_connect_timeout | 2s | Sets the maximum wait time to connect master.
wal_receiver_timeout | 6s | Sets the maximum wait time to receive data from master.
wal_sender_timeout | 6s | Sets the maximum time to wait for WAL replication.
wdr_snapshot_query_timeout | 100s | Sets the timeout for wdr snapshot query, in seconds
PostgreSQL 13
anbob=# show lock_timeout; lock_timeout -------------- 0
Note:
在postgresql和opengauss中select 同样会堵塞DDL，DDL又会堵塞后面的DML和SELECT ,OPENGAUSS应该是受 lockwait_timeout控制，默认20分钟。而postgreSQL是参数lock_timeout，默认是0永久，和mysql等1年还长久, 而如果配置了statement_timeout 会自动中断。
测试几个分布式数据库表现
# Oceanbase
# session 1 obclient [ ANBOB]> select count(*) from mytab a,mytab b ; -- RUNNING # session 2 obclient [TBCS]> select * from sys gv$lock; Empty set (0.206sec ) obclient [ TBCS]> DROP TABLE mytab; Query OK，
在OB中不会持有对象锁，SELECT和oracle一样没有增加任何锁，所以drop 不会等待select，直接删除.
# 万里开源GreatDB
GreatDB Cluster[(none)]> use web_slow_log;
Database changed
GreatDB Cluster[web_slow_log]> show tables;
Empty set (0.00 sec)
GreatDB Cluster[web_slow_log]> create table web_slow_log.test_drop (id int primary key);
Query OK, 0 rows affected (0.01 sec)
GreatDB Cluster[web_slow_log]> insert into web_slow_log.test_drop values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
---session 1
GreatDB Cluster[web_slow_log]> start transaction;
Query OK, 0 rows affected (0.00 sec)
GreatDB Cluster[web_slow_log]> select * from web_slow_log.test_drop;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
---session 2
GreatDB Cluster[(none)]> drop table web_slow_log.test_drop;
-- waiting
---session 3
GreatDB Cluster[(none)]> insert into web_slow_log.test_drop values (4),(5),(6);
-- waiting
---session 4
GreatDB Cluster[(none)]> show processlist;
+----------+-----------------+---------------------+--------------+---------+---------+---------------------------------+-------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----------+-----------------+---------------------+--------------+---------+---------+---------------------------------+-------------------------------------------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 8144018 | Waiting on empty queue | NULL |
| 15858485 | root | localhost | web_slow_log | Sleep | 109 | | NULL |
| 15859344 | root | localhost | NULL | Query | 76 | Waiting for table metadata lock | drop table web_slow_log.test_drop |
| 15859541 | root | localhost | NULL | Query | 37 | Waiting for table metadata lock | insert into web_slow_log.test_drop values (4),(5),(6) |
| 15859644 | root | localhost | NULL | Query | 0 | init | show processlist |
+----------+-----------------+---------------------+--------------+---------+---------+---------------------------------+-------------------------------------------------------+
65 rows in set (0.00 sec)
GreatDB Cluster[(none)]> select * from performance_schema.metadata_locks t where OBJECT_NAME='test_drop';
+-------------+---------------+-------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+-------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE | web_slow_log | test_drop | NULL | 140613269960160 | SHARED_WRITE | TRANSACTION | PENDING | sql_parse.cc:5736 | 15859581 | 3 |
| TABLE | web_slow_log | test_drop | NULL | 140612934760816 | SHARED_READ | TRANSACTION | GRANTED | sql_parse.cc:5736 | 15858525 | 19 |
| TABLE | web_slow_log | test_drop | NULL | 140612129134432 | EXCLUSIVE | TRANSACTION | PENDING | sql_parse.cc:5736 | 15859384 | 3 |
+-------------+---------------+-------------+-------------+-----------------------+--------------+---------------+-------------+-------------------+-----------------+----------------+
3 rows in set (0.01 sec)
GreatDB Cluster[(none)]> show variables like '%lock%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| block_encryption_mode | aes-128-ecb |
| innodb_api_disable_rowlock | OFF |
| innodb_autoinc_lock_mode | 2 |
| innodb_deadlock_detect | ON |
| innodb_lock_wait_timeout | 50 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_print_all_deadlocks | OFF |
| innodb_status_output_locks | OFF |
| innodb_table_locks | ON |
| key_cache_block_size | 1024 |
| lock_wait_timeout | 31536000 |
| locked_in_memory | OFF |
| max_write_lock_count | 18446744073709551615 |
| performance_schema_max_metadata_locks | -1 |
| performance_schema_max_rwlock_classes | 60 |
| performance_schema_max_rwlock_instances | -1 |
| performance_schema_max_table_lock_stat | -1 |
| query_alloc_block_size | 8192 |
| range_alloc_block_size | 4096 |
| skip_external_locking | ON |
| transaction_alloc_block_size | 8192 |
+-----------------------------------------+----------------------+
22 rows in set (0.00 sec)
Note:
不出意外，greateDB就是MySQL的表现。
# GoldenDB中兴
---session 1
MySQL [test]> insert into test_data values (1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
MySQL [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)
MySQL [test]> select * from test_data;
+----+------+------+
| ID | A | B |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
+----+------+------+
3 rows in set (0.00 sec)
MySQL [(none)]> drop table test.test_data;
-- 等待几秒后报错
ERROR 10952 (HY000): ERR: there is metadata lock on this table, please try later!
MySQL [(none)]>
MySQL [(none)]> show variables like '%lock%';
+-----------------------------------------+------------------------------+
| VARIABLE_NAME | VALUE |
+-----------------------------------------+------------------------------+
| block_encryption_mode | aes-128-ecb |
| innodb_api_disable_rowlock | OFF |
| innodb_autoinc_lock_mode | 2 |
| innodb_deadlock_detect | ON |
| innodb_deadlock_log_expire_seconds | 604800 |
| innodb_deadlock_log_table | ON |
| innodb_lock_wait_collect_time | 500 |
| innodb_lock_wait_log | OFF |
| innodb_lock_wait_log_dir | /appdata/goldendb/zxdb1/log/ |
| innodb_lock_wait_log_size | 512 |
| innodb_lock_wait_max_depth | 0 |
| innodb_lock_wait_slot_count | 1024 |
| innodb_lock_wait_slot_max_depth | 8 |
| innodb_lock_wait_timeout | 8 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_print_all_deadlocks | ON |
| innodb_status_output_locks | OFF |
| innodb_table_locks | ON |
| key_cache_block_size | 1024 |
| lock_wait_timeout | 5 |
| locked_in_memory | OFF |
| log_slow_lock_monitor | |
| log_slow_lock_monitor_time_ratio | 0.200000 |
| max_write_lock_count | 18446744073709551615 |
| performance_schema_max_metadata_locks | -1 |
| performance_schema_max_rwlock_classes | 60 |
| performance_schema_max_rwlock_instances | -1 |
| performance_schema_max_table_lock_stat | -1 |
| query_alloc_block_size | 8192 |
| range_alloc_block_size | 4096 |
| skip_external_locking | ON |
| transaction_alloc_block_size | 8192 |
| trx_log_reserved_block_num | 1024 |
+-----------------------------------------+------------------------------+
34 rows in set (0.01 sec)
---session 3
MySQL [(none)]> insert into test.test_data values (4,4,4),(5,5,5),(6,6,6);
ERROR 11204 (HY000): table 'test.test_data' is disabled!
MySQL [(none)]>
Note：
在goldenDB中基于mysql分布式，但修改了参数lock_wait_timeout默认值， 所以短暂的等待后报错，提示ERR: there is metadata lock on this table。而此时的DML报错提示table ‘test.test_data’ is disabled!
— over —
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。