Today, suddenly found that when mysql for cold standby, just the DDL operations, this led to a deadlock, DDL table is small, do you have a great god know? Isn't it cold standby DML operation causes a lot of this table when the metadata of the lock?

CodePudding user response:

Another kind of table level lock is MDL that meta data lock, do not need explicit use of MDL, on a visit to a table is automatically add,
MDL's role is to ensure the correctness of the read and write, for example, if a query is traversed a the data in the table, and perform another thread to do change the table structure, delete a column, with the result of the query threads to get the table structure in, this is definitely wrong,
When on a table to add and delete, add MDL read lock, when the table structure changes and MDL write locks,
Between 1 and read lock is not mutually exclusive, so you can have multiple threads at the same time for a table to add and delete,
Between 2, between the read-write lock, write locks are mutually exclusive, used to keep the change table structure operation safety, so if there are two threads to add fields to a table at the same time, one have to wait another completes can begin to carry out,
Transaction of MDL locks, apply at the beginning of the statement execution, but after the statement does not immediately release, and the whole transaction commit until after the release,
A query a row or does not return to a small amount of data for a long time, may be with the thread on the table or hold MDL write lock, the select statement is blocked,
On the table in mysql do flush operation has two situations:
Flush tables with read lock t; Flush tables with read lock; Specify the table, then the only close the table t, if you don't specify the close all open tables, so there waiting for the table may flush state is: have a flush tables command was blocked by other statements, then blocked the select statement,

CodePudding user response:

cold standby, just the DDL operations

How do you perform DDL when cold standby?

CodePudding user response:

It should be hot backup? Cold standby is not disconnected data?