Locking is depends upon Isolation level and Storage Engine.
MySQL uses table level locking (instead of page, row, or column locking) for all storage engines except InnoDB, which uses row level locking
locking system in MySQL:
Implicit Locking:
To maintain the ACID property every transaction has to maintain the locking internally so that end user will retrieve the valid data to achieve it engine has to have some implicit locking like write lock readers.
Explicit locking:
For some of the transactions user wanted to make sure that their transaction will be uninterrupted so they make an explicit lock on transaction.
Blocking is locking on session level, one transaction is writing on the object and if other transaction is trying to access same object, it will not until first write transaction completes (first session is blocking second session)
To get the blocking information:
MySQL>Show PROCESSLIST;
MySQL>Show Full PROCESSLIST; ## extra information.
Following command will provide additional detail:
SELECT pl.id ,pl.user ,pl.state ,it.trx_id ,it.trx_mysql_thread_id ,it.trx_query AS query ,it.trx_id AS blocking_trx_id ,it.trx_mysql_thread_id AS blocking_thread ,it.trx_query AS blocking_queryFROM information_schema.processlist AS pl INNER JOIN information_schema.innodb_trx AS it ON pl.id = it.trx_mysql_thread_idINNER JOIN information_schema.innodb_lock_waits AS ilw ON it.trx_id = ilw.requesting_trx_id AND it.trx_id = ilw.blocking_trx_id
- MySQL uses memory accordingly to value assigned to variable “innodb_buffer_pool_size “ standard value should be 50 % – 70% of total OS memory.the value to this variable is dynamic – no need to restart mysql to get effect.
- mysqladmin flush-tables clears or flush table data from memory and clears memory
To know how many times we have locks on table and required wait
SHOW STATUS LIKE ‘Table%’
| Table_locks_immediate | Table_locks_waited
To get the benchmark information about how much time required to retrieve the data for calculation
SELECT BENCHMARK(1000000,1+1)1 row in set (0.32 sec)
To get structure information about table
DESCRIBE
Similarly SHOW CREATE TABLE, SHOW TABLE STATUS, and SHOW INDEX statements to get information about tables.
To get the execution plan for the statement explain is the command.
{EXPLAIN tbl_name [col_name | wild]
or
{EXPLAIN | DESCRIBE | DESC}
[explain_type]
{explainable_stmt | FOR CONNECTION connection_id}
explain_type: {
EXTENDED | PARTITIONS | FORMAT = {TRADITIONAL | JSON}
}
explainable_stmt: {
SELECT statement
| DELETE statement
| INSERT statement
| REPLACE statement
| UPDATE statement
}
Analyze table is used to update the statistics of the table and analyze it. During this operations table will be locked.
Statistics information is are stored in mysql.innodb_table_stats and mysql.innodb_index_stats
and also can be used using mysqlstats plugin
14.7.2.4 OPTIMIZE TABLE Syntax
Optimize table is used to reorganized the indexes and data on the table for better performance.
It check the table if any error exists
14.7.2.3 CHECKSUM TABLE Syntax
To maintain the consistency of the table this command it used, it also used for validation for backup data.
This command is used for repair of any issue on table.
Reference:
https://www.ppgia.pucpr.br/pt/arquivos/techdocs/mysql/optimization.html
Vinay Thakur 