Without more information, there is no way to see if there is any problem in here. The picture in itself looks perfectly normal: any of your "sleeping" connections can hold a lock on one of the tables which the view is trying to use, in this case you would get exactly that, the connection trying to alter the view would wait for a metadata lock.
One simple way to check it is to use metadata_lock_info plugin. While your ALTER is hanging like that, from another connection run install soname 'metadata_lock_info (if it has not been installed yet), and then run select * from information_schema.metadata_lock_info. It should show if any tables or the view itself are locked.
MariaDB [(none)]> select * from information_schema.metadata_lock_info;
|
+-----------+-------------------------+-----------------+-------------------------------+--------------+----------------------+
|
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
|
+-----------+-------------------------+-----------------+-------------------------------+--------------+----------------------+
|
| 1096 | MDL_INTENTION_EXCLUSIVE | MDL_STATEMENT | Global read lock | | |
|
| 1052 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profiles |
|
| 1048 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profiles |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profiles |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profiles |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profiles |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | aigk_appl |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | aigk_appl |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | aigk_appl |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profile_doc |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profile_doc |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profile_doc |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profiles_groups |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profiles_groups |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profiles_groups |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_army_status |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_army_status |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_army_status |
|
| 1052 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_product_opt |
|
| 1048 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_product_opt |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_product_opt |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_product_opt |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_product_opt |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profile_types |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profile_types |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profile_types |
|
| 1048 | MDL_SHARED | MDL_TRANSACTION | Stored function metadata lock | dev | num_to_text |
|
| 1049 | MDL_SHARED | MDL_TRANSACTION | Stored function metadata lock | dev | num_to_text |
|
| 1050 | MDL_SHARED | MDL_TRANSACTION | Stored function metadata lock | dev | num_to_text |
|
| 1051 | MDL_SHARED | MDL_TRANSACTION | Stored function metadata lock | dev | num_to_text |
|
| 1096 | MDL_INTENTION_EXCLUSIVE | MDL_TRANSACTION | Schema metadata lock | dev | |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profile_groups |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profile_groups |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profile_groups |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_family_type |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_family_type |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_family_type |
|
| 1048 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | aigk_profile |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | aigk_profile |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | aigk_profile |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | aigk_profile |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_doc_type |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_doc_type |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_doc_type |
|
| 1048 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | dictionaries |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | dictionaries |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | dictionaries |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | dictionaries |
|
| 1048 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #departments |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #departments |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #departments |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #departments |
|
| 1048 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #jobs |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #jobs |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #jobs |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #jobs |
|
| 1052 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_products |
|
| 1048 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_products |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_products |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_products |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_products |
|
| 1048 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #users |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #users |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #users |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #users |
|
| 1048 | MDL_SHARED | MDL_TRANSACTION | Stored function metadata lock | dev | num_to_text_3r |
|
| 1049 | MDL_SHARED | MDL_TRANSACTION | Stored function metadata lock | dev | num_to_text_3r |
|
| 1050 | MDL_SHARED | MDL_TRANSACTION | Stored function metadata lock | dev | num_to_text_3r |
|
| 1051 | MDL_SHARED | MDL_TRANSACTION | Stored function metadata lock | dev | num_to_text_3r |
|
| 1048 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #user_profiles |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #user_profiles |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #user_profiles |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #user_profiles |
|
| 1049 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_education |
|
| 1050 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_education |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_education |
|
+-----------+-------------------------+-----------------+-------------------------------+--------------+----------------------+
|
76 rows in set (0.00 sec)
|
So, as you can see, there are plenty of locks held by other connections. Shared read locks on underlying tables should be fine, but three connections have a lock on the view itself, this will definitely prevent ALTER from being executed.
MariaDB [(none)]> select * from information_schema.metadata_lock_info;
|
+-----------+-----------------+-----------------+-------------------------------+--------------+----------------------+
|
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
|
+-----------+-----------------+-----------------+-------------------------------+--------------+----------------------+
|
| 1052 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profiles |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profiles |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | aigk_appl |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profile_doc |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profiles_groups |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_army_status |
|
| 1052 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_product_opt |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_product_opt |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profile_types |
|
| 1051 | MDL_SHARED | MDL_TRANSACTION | Stored function metadata lock | dev | num_to_text |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_family_type |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_profile_groups |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | aigk_profile |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_doc_type |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | dictionaries |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #departments |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #jobs |
|
| 1052 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_products |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | appl_products |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #users |
|
| 1051 | MDL_SHARED | MDL_TRANSACTION | Stored function metadata lock | dev | num_to_text_3r |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | #user_profiles |
|
| 1051 | MDL_SHARED_READ | MDL_TRANSACTION | Table metadata lock | dev | crm_education |
|
+-----------+-----------------+-----------------+-------------------------------+--------------+----------------------+
|
23 rows in set (0.00 sec)
|
Yes I see it, but I am also see that all another connection being in sleep state.
MariaDB [(none)]> show processlist;
|
+------+--------+-----------------+--------+---------+-------+-------+------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+------+--------+-----------------+--------+---------+-------+-------+------------------+----------+
|
| 1047 | dev | localhost | dev | Sleep | 14192 | | NULL | 0.000 |
|
| 1048 | dev | localhost | dev | Sleep | 890 | | NULL | 0.000 |
|
| 1049 | dev | localhost | dev | Sleep | 887 | | NULL | 0.000 |
|
| 1050 | dev | localhost | dev | Sleep | 883 | | NULL | 0.000 |
|
| 1051 | dev | localhost | dev | Sleep | 14340 | | NULL | 0.000 |
|
| 1052 | dev | localhost | dev | Sleep | 16812 | | NULL | 0.000 |
|
| 1053 | dev | localhost | dev | Sleep | 14198 | | NULL | 0.000 |
|
| 1054 | dev | localhost | dev | Sleep | 14194 | | NULL | 0.000 |
|
| 1058 | itpark | localhost | itpark | Sleep | 23333 | | NULL | 0.000 |
|
| 1059 | itpark | localhost | itpark | Sleep | 23326 | | NULL | 0.000 |
|
| 1060 | itpark | localhost | itpark | Sleep | 23306 | | NULL | 0.000 |
|
| 1061 | itpark | localhost | itpark | Sleep | 23295 | | NULL | 0.000 |
|
| 1062 | itpark | localhost | itpark | Sleep | 23361 | | NULL | 0.000 |
|
| 1063 | itpark | localhost | itpark | Sleep | 23349 | | NULL | 0.000 |
|
| 1064 | itpark | localhost | itpark | Sleep | 23345 | | NULL | 0.000 |
|
| 1065 | itpark | localhost | itpark | Sleep | 23339 | | NULL | 0.000 |
|
| 1098 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
|
| 1117 | root | localhost:50704 | dev | Sleep | 125 | | NULL | 0.000 |
|
+------+--------+-----------------+--------+---------+-------+-------+------------------+----------+
|
18 rows in set (0.00 sec)
|
|
|
I think that is a bad idea to do every time restart the server to remove the lock.
So I have two questions:
1. How to find and kill the process which created lock?
2. I understand this is associated with the transaction DML. How to find a transaction pending for a long time?
I am also see that all another connection being in sleep state.
It does not matter. These are transactional locks, they are kept for the duration of transaction, not a statement. That is, once your connection started transaction and lets say did SELECT * FROM <view>, it created a lock, which will be there until it does COMMIT or ROLLBACK.
A very common reason why it can happen is using a connector (IIRC it's php connector, although I'm not 100% sure) which sets autocommit=0 for all connections. Users' applications, especially the old ones which were written at pre-5.5 times, don't take it into account and don't do commits, thus keeping open transactions and transactional locks.
How to find and kill the process which created lock?
Just like you did before. metadata_lock_info plugin shows you the connection ID that holds a lock, the type of the lock, and the object which is locked.
How to find a transaction pending for a long time?
If you are using InnoDB, you can either watch SHOW ENGINE INNODB STATUS or INFORMATION_SCHEMA.INNODB_TRX. If you are using MyISAM, I don't have a good answer for that, I think the best shot is the abovementioned INFORMATION_SCHEMA.METADATA_LOCK_INFO, although it does not show for how long a lock has been held.
> autocommit=0
Yeah, we needed to control transactions this is CRM + ERP system and mistakes in code must doesn't leave data in inconsistent state.
Main problem of php that if script end with fatal error php-fpm not terminate connection which is used by php script. And new script will start new transaction even if previous transaction not committed or rollbacked.
Question: what MariaDB server will do if through one connection not ended previous transaction, and new transaction will start again? locks will be not freed?
Seem we see here this case.
> Just like you did before. metadata_lock_info plugin shows you the connection ID that holds a lock, the type of the lock, and the object which is locked.
It means that THREAD_ID into information_schema.metadata_lock_info is Id in show processlist;?
> If you are using InnoDB
Yes, I am using InnoDB
MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
|
Empty set (0.00 sec)
|
but output is empty.... 
MariaDB [(none)]> SHOW ENGINE INNODB STATUS;
|
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Type | Name | Status |
|
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| InnoDB | |
|
=====================================
|
2015-10-27 18:36:21 7ff290567700 INNODB MONITOR OUTPUT
|
=====================================
|
Per second averages calculated from the last 23 seconds
|
-----------------
|
BACKGROUND THREAD
|
-----------------
|
srv_master_thread loops: 1885 srv_active, 0 srv_shutdown, 584534 srv_idle
|
srv_master_thread log flush and writes: 586414
|
----------
|
SEMAPHORES
|
----------
|
OS WAIT ARRAY INFO: reservation count 4325
|
OS WAIT ARRAY INFO: signal count 4766
|
Mutex spin waits 4213, rounds 84976, OS waits 2651
|
RW-shared spins 1717, rounds 39167, OS waits 1151
|
RW-excl spins 383, rounds 17577, OS waits 494
|
Spin rounds per wait: 20.17 mutex, 22.81 RW-shared, 45.89 RW-excl
|
------------------------
|
LATEST FOREIGN KEY ERROR
|
------------------------
|
2015-10-26 22:38:42 7ff2907af700 Transaction:
|
TRANSACTION 1617605, ACTIVE 0 sec updating or deleting
|
mysql tables in use 1, locked 1
|
70 lock struct(s), heap size 13864, 272 row lock(s), undo log entries 74
|
MySQL thread id 890, OS thread handle 0x7ff2907af700, query id 146254 localhost dev updating
|
delete from `workflow_documents` where `id_document`= '1257'
|
Foreign key constraint fails for table `dev`.`bki_loans`:
|
,
|
CONSTRAINT `bki_loans_ibfk_1` FOREIGN KEY (`id_profile`) REFERENCES `appl_profiles` (`id_profile`)
|
Trying to delete or update in parent table, in index `PRIMARY` tuple:
|
DATA TUPLE: 26 fields;
|
0: len 36; hex 39393635396366632d366266352d313165352d626363352d303035303536336333613664; asc 99659cfc-6bf5-11e5-bcc5-0050563c3a6d;;
|
1: len 6; hex 00000018aec5; asc ;;
|
2: len 7; hex 550000158719cc; asc U ;;
|
3: len 4; hex 00000007; asc ;;
|
4: len 4; hex 000004e9; asc ;;
|
5: SQL NULL;
|
6: SQL NULL;
|
7: len 0; hex ; asc ;;
|
8: len 360; hex 041200b900000003000300130009006300100073001700a3001f00d30028008301310073023b0083024500f3024f00230459007305650093057200a3058000c3058e00e3059c00f305aa000306494e4e67656e646572646f635f6e756d726573436e7472636974697a656e7362697274686461746566697273746e616d656269727468706c61636562757965725f706172746d6964646c656e616d657365636f6e646e616d6569645f656475636174696f6e6f6c645f66697273746e616d6569645f61726d795f73746174757369645f66616d696c795f747970656f6c645f6d6964646c656e616d656f6c645f7365636f6e646e616d6569645f736f6369616c5f737461746521216d616c652121525521525521313937372d30362d313321d090d0bbd18cd0b1d0b5d180d182212165727465777221d09dd0b0d0b7d0b0d180d0bed0b2d0b8d18721d09dd0b0d181d0b8d0b1d183d0bbd0bbd0b8d0bd2132212131213121212131; asc c s ( 1 s ; E O # Y s e r INNgenderdoc_numresCntrcitizensbirthdatefirstnamebirthplacebuyer_partmiddlenamesecondnameid_educationold_firstnameid_army_statusid_family_typeold_middlenameold_secondnameid_social_state!!male!!RU!RU!1977-06-13! !!ertewr! ! !2!!1!1!!!1;;
|
9: len 4; hex 56136da7; asc V m ;;
|
10: len 4; hex 562e64d5; asc V.d ;;
|
11: SQL NULL;
|
12: len 4; hex 00033e24; asc >$;;
|
13: len 4; hex 00033e24; asc >$;;
|
14: len 4; hex 00033e24; asc >$;;
|
15: len 54; hex d09dd0b0d181d0b8d0b1d183d0bbd0bbd0b8d0bd20d090d0bbd18cd0b1d0b5d180d18220d09dd0b0d0b7d0b0d180d0bed0b2d0b8d187; asc ;;
|
16: SQL NULL;
|
17: len 36; hex 32653064646639342d366266352d313165352d626363352d303035303536336333613664; asc 2e0ddf94-6bf5-11e5-bcc5-0050563c3a6d;;
|
18: len 36; hex 62633738376138632d366239332d313165352d626363352d303035303536336333613664; asc bc787a8c-6b93-11e5-bcc5-0050563c3a6d;;
|
19: SQL NULL;
|
20: SQL NULL;
|
21: SQL NULL;
|
22: SQL NULL;
|
23: SQL NULL;
|
24: SQL NULL;
|
25: SQL NULL;
|
|
|
But in child table `dev`.`bki_loans`, in index `id_profile`, there is a record:
|
PHYSICAL RECORD: n_fields 2; compact format; info bits 0
|
0: len 30; hex 39393635396366632d366266352d313165352d626363352d303035303536; asc 99659cfc-6bf5-11e5-bcc5-005056; (total 36 bytes);
|
1: len 30; hex 33663436613564622d366266382d313165352d626363352d303035303536; asc 3f46a5db-6bf8-11e5-bcc5-005056; (total 36 bytes);
|
|
|
------------
|
TRANSACTIONS
|
------------
|
Trx id counter 1619528
|
Purge done for trx's n:o < 1619526 undo n:o < 0 state: running but idle
|
History list length 652
|
LIST OF TRANSACTIONS FOR EACH SESSION:
|
---TRANSACTION 0, not started
|
MySQL thread id 1098, OS thread handle 0x7ff290567700, query id 167176 localhost root init
|
SHOW ENGINE INNODB STATUS
|
---TRANSACTION 0, not started
|
MySQL thread id 1117, OS thread handle 0x7ff298052700, query id 167174 localhost ::1 root cleaning up
|
---TRANSACTION 1619217, not started
|
MySQL thread id 1065, OS thread handle 0x7ff2905b0700, query id 157973 localhost itpark cleaning up
|
---TRANSACTION 1619214, not started
|
MySQL thread id 1064, OS thread handle 0x7ff29809b700, query id 157938 localhost itpark cleaning up
|
---TRANSACTION 1619212, not started
|
MySQL thread id 1063, OS thread handle 0x7ff29071d700, query id 157918 localhost itpark cleaning up
|
---TRANSACTION 1619210, not started
|
MySQL thread id 1062, OS thread handle 0x7ff2907af700, query id 157867 localhost itpark cleaning up
|
---TRANSACTION 1619228, not started
|
MySQL thread id 1061, OS thread handle 0x7ff2903fa700, query id 158076 localhost itpark cleaning up
|
---TRANSACTION 1619226, not started
|
MySQL thread id 1060, OS thread handle 0x7ff2980e4700, query id 158048 localhost itpark cleaning up
|
---TRANSACTION 1619221, not started
|
MySQL thread id 1059, OS thread handle 0x7ff290642700, query id 158039 localhost itpark cleaning up
|
---TRANSACTION 1619219, not started
|
MySQL thread id 1058, OS thread handle 0x7ff2905f9700, query id 157982 localhost itpark cleaning up
|
---TRANSACTION 1619505, not started
|
MySQL thread id 1054, OS thread handle 0x7ff2bf485700, query id 166542 localhost dev cleaning up
|
---TRANSACTION 1619519, not started
|
MySQL thread id 1053, OS thread handle 0x7ff29068b700, query id 166732 localhost dev cleaning up
|
---TRANSACTION 1619516, not started
|
MySQL thread id 1052, OS thread handle 0x7ff2bf4ce700, query id 166687 localhost dev cleaning up
|
---TRANSACTION 1619498, not started
|
MySQL thread id 1051, OS thread handle 0x7ff290368700, query id 166504 localhost dev cleaning up
|
---TRANSACTION 1619527, not started
|
MySQL thread id 1050, OS thread handle 0x7ff290443700, query id 167143 localhost dev cleaning up
|
---TRANSACTION 1619524, not started
|
MySQL thread id 1049, OS thread handle 0x7ff2bf517700, query id 166840 localhost dev cleaning up
|
---TRANSACTION 1619509, not started
|
MySQL thread id 1048, OS thread handle 0x7ff2907f8700, query id 166570 localhost dev cleaning up
|
---TRANSACTION 1619521, not started
|
MySQL thread id 1047, OS thread handle 0x7ff290766700, query id 166786 localhost dev cleaning up
|
--------
|
FILE I/O
|
--------
|
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
|
I/O thread 1 state: waiting for completed aio requests (log thread)
|
I/O thread 2 state: waiting for completed aio requests (read thread)
|
I/O thread 3 state: waiting for completed aio requests (read thread)
|
I/O thread 4 state: waiting for completed aio requests (read thread)
|
I/O thread 5 state: waiting for completed aio requests (read thread)
|
I/O thread 6 state: waiting for completed aio requests (write thread)
|
I/O thread 7 state: waiting for completed aio requests (write thread)
|
I/O thread 8 state: waiting for completed aio requests (write thread)
|
I/O thread 9 state: waiting for completed aio requests (write thread)
|
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
|
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
|
Pending flushes (fsync) log: 0; buffer pool: 0
|
19668 OS file reads, 18459 OS file writes, 7335 OS fsyncs
|
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
|
-------------------------------------
|
INSERT BUFFER AND ADAPTIVE HASH INDEX
|
-------------------------------------
|
Ibuf: size 1, free list len 2829, seg size 2831, 50 merges
|
merged operations:
|
insert 152, delete mark 39954, delete 1
|
discarded operations:
|
insert 0, delete mark 0, delete 0
|
0.00 hash searches/s, 0.00 non-hash searches/s
|
---
|
LOG
|
---
|
Log sequence number 71611116595
|
Log flushed up to 71611116595
|
Pages flushed up to 71611116595
|
Last checkpoint at 71611116595
|
Max checkpoint age 80826164
|
Checkpoint age target 78300347
|
Modified age 0
|
Checkpoint age 0
|
0 pending log writes, 0 pending chkp writes
|
3617 log i/o's done, 0.00 log i/o's/second
|
----------------------
|
BUFFER POOL AND MEMORY
|
----------------------
|
Total memory allocated 137363456; in additional pool allocated 0
|
Total memory allocated by read views 2096
|
Internal hash tables (constant factor + variable factor)
|
Adaptive hash index 12899936 (2213368 + 10686568)
|
Page hash 139112 (buffer pool 0 only)
|
Dictionary cache 7449631 (554768 + 6894863)
|
File system 1329376 (812272 + 517104)
|
Lock system 339352 (332872 + 6480)
|
Recovery system 0 (0 + 0)
|
Dictionary memory allocated 6894863
|
Buffer pool size 8191
|
Buffer pool size, bytes 134201344
|
Free buffers 1024
|
Database pages 6515
|
Old database pages 2384
|
Modified db pages 0
|
Percent of dirty pages(LRU & free pages): 0.000
|
Max dirty pages percent: 75.000
|
Pending reads 0
|
Pending writes: LRU 0, flush list 0, single page 0
|
Pages made young 4865, not young 2187303
|
0.00 youngs/s, 0.00 non-youngs/s
|
Pages read 18812, created 3286, written 12831
|
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
|
No buffer pool page gets since the last printout
|
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
|
LRU len: 6515, unzip_LRU len: 0
|
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
|
--------------
|
ROW OPERATIONS
|
--------------
|
0 queries inside InnoDB, 0 queries in queue
|
0 read views open inside InnoDB
|
0 RW transactions active inside InnoDB
|
0 RO transactions active inside InnoDB
|
0 out of 1000 descriptors used
|
Main thread process no. 1171, id 140679825053440, state: sleeping
|
Number of rows inserted 104803, updated 3246, deleted 60177, read 10097454
|
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
|
Number of system rows inserted 0, updated 0, deleted 0, read 0
|
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
|
----------------------------
|
END OF INNODB MONITOR OUTPUT
|
============================
|
|
|
+--------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
Here I am see very large output. What i must to notice?
It means that THREAD_ID into information_schema.metadata_lock_info is Id in show processlist;?
Yes.
Question: what MariaDB server will do if through one connection not ended previous transaction, and new transaction will start again? locks will be not freed?
I am not sure what you mean by "new transaction will start again". If it's the same connection, starting a new transaction automatically commits the previous one. If it's another connection, starting a new transaction in connection Y does not affect a transaction in a connection X (and if the transaction in the connection X holds MDL locks, it will keep holding them).
Yes, I am using InnoDB
Are you using only InnoDB? Are the tables which are selected from by the view in question InnoDB tables?
but output is empty....
Try the simple thing.
create table test (i int) engine=InnoDB;
|
begin;
|
select * from test;
|
select * from information_schema.innodb_trx;
|
If it returns something, then it works, which means when you queried it previously, you did not have innodb transactions (you could still have transactions which held MDL locks on non-innodb tables).
Here I am see very large output. What i must to notice?
|
For the purpose of this discussion, the section to pay attention to is
------------
|
TRANSACTIONS
|
------------
|
If there were any InnoDB transactions, you would have seen something like
---TRANSACTION 3339, ACTIVE 2 sec
|
MySQL thread id 5, OS thread handle 0x7f432a1c2700, query id 47 localhost ::1 root cleaning up
|
But your output suggests that, again, there are no innodb transactions running at the moment.
> I am not sure what you mean by "new transaction will start again". If it's the same connection, starting a new transaction automatically commits the previous one.
This is that I had in mind. But I am not expected what a new transaction automatically commits the previous one. For me will be more correct if a new transaction automatically rollback the previous one uncommitted transaction. Is it possible change behavior here?
> Are you using only InnoDB? Are the tables which are selected from by the view in question InnoDB tables?
Yes, I am sure. Looks like connection in which hung incomplete transaction was someone re-use and this has led to COMMIT transaction.
I repeat you example and see how it works.
I think be better if in table information_schema.metadata_lock_info would be added column with lock time and would helps automate for DBA killing long locks. It is possible ?
But I am not expected what a new transaction automatically commits the previous one. For me will be more correct if a new transaction automatically rollback the previous one uncommitted transaction.
There is a big set of statements in MySQL which cause a so-called 'implicit commit'. It means that if there was an active transaction, and such a statement was issued, the transaction is automatically committed before executing the statement. E.g. DDL statements behave like that, and so does START TRANSACTION / BEGIN. To my knowledge, there is no concept of "implicit rollback", apart from when a session disconnects without committing the current transaction.
Is it possible change behavior here?
There are no server variables that could change that, if that's what you mean. Of course, in your application you can always precede START TRANSACTION or BEGIN with ROLLBACK. If the previous transaction was committed properly, it will have no effect, if it was not, it will be rolled back.
I think be better if in table information_schema.metadata_lock_info would be added column with lock time and would helps automate for DBA killing long locks. It is possible ?
I was also thinking about it, and was even going to file a feature request for this. If you want to do it, please go ahead. I cannot tell you right away if it's possible, but I hope it is (although most likely it won't go to a GA release).
There is a big set of statements in MySQL which cause a so-called 'implicit commit'. It means that if there was an active transaction, and such a statement was issued, the transaction is automatically committed before executing the statement. E.g. DDL statements behave like that, and so does START TRANSACTION / BEGIN. To my knowledge, there is no concept of "implicit rollback", apart from when a session disconnects without committing the current transaction.
https://mariadb.atlassian.net/browse/MDEV-9030
I was also thinking about it, and was even going to file a feature request for this. If you want to do it, please go ahead. I cannot tell you right away if it's possible, but I hope it is (although most likely it won't go to a GA release).
https://mariadb.atlassian.net/browse/MDEV-9031
What is next GA release? 10.2 ?
And last question what is best way for locking database schema for updating purpose?
I found in documentation: LOCK TABLES tablename1, tablename2, tablename3 ... WRITE
And after update I can do UNLOCK TABLES
It is necessary that no one could prevent the updater to make changes to the database scheme.
I forgot to ask: How much load adds metadata lock_info plugin on database. I think this plugin really very necessary on all production servers. And it would be nice if it would be enabled by default.
What is next GA release? 10.2 ?
Yes.
And last question what is best way for locking database schema for updating purpose?
I found in documentation: LOCK TABLES tablename1, tablename2, tablename3 ... WRITE
And after update I can do UNLOCK TABLES
It depends on what you mean by "update". If it's operations on existing tables, then the above should work for you – of course, as you understand, it will block any activity on any table until you finish all updates on all tables. But if the planned changes include, for example, new table creation, it won't work.
You can also consider the read_only option.
How much load adds metadata lock_info plugin on database
We don't have any statistics in this regard, and even if we did, it will vary too much depending on schema and load.
I ran some very quick basic tests and got ~5-10% throughput loss with MyISAM tables, no visible difference with InnoDB tables; but I want to emphasize that these values are by no means accurate or reliable. The best way to know how it will affect you is to experiment on your schema and server.