WL#2333: SHOW ENGINE ... LOCK STATUS
Affects: Server-7.1 — Status: Un-Assigned — Priority: Medium
This description arises from a discussion on dev-public, thread "Re: TODO: Jan, please implement SHOW LOCKS" in January 2005. [mysql internal address]/secure/mailarchive/mail.php?folder=5&mail=31862 At the time we expected that InnoDB would act on lock display soon, and we would have liked to agree on a common general idea for all storage engines. Now (December 2008) we seem to have failed in the original objective, but we still can propose what the server should have. So here's an editable document. PERFORMANCE_SCHEMA . EVENTS_WAITS_CURRENT ----------------------------------------- Locks are just another type of event, so they'll be in EVENTS_WAITS_CURRENT and all histories or summaries. Phylum: Instrument Class: Wait Order Synchronization Object /* abbreviated 'Synch' */ Family Lock Example: 'Wait/Synch/Lock/Row-Exclusive/InnoDB'. That would be the end of this task description, if it weren't for the fact that people also want to know what's blocking what. PERFORMANCE_SCHEMA . LOCKS -------------------------- LOCKS is a single (noticeably un-normalized) table with one row for each lock that is "current" on a database, tablespace, table, or row. In theory, a new lock is inserted when a request is made, updated when a request changes (e.g. a shared lock becomes exclusive or status changes to granted), deleted when lock is released (e.g. at end of transaction). Values come from the storage engine or the performance schema instruments. WL#2360 says: "Values in PERFORMANCE_SCHEMA tables are non-deterministic, non-consistent, non-repeatable. PERFORMANCE_SCHEMA tables cannot be locked, indexed, or backed up. Selections on PERFORMANCE_SCHEMA tables should not be cached." That is true for PERFORMANCE_SCHEMA.LOCKS too. WL#2360 also says: "Every user has SELECT privilege for all PERFORMANCE_SCHEMA tables, and no other privilege." That is true for PERFORMANCE_SCHEMA.LOCKS too. There is no requirement for a SUPER privilege or a PROCESS privilege. We'll try to use the same table for all storage engines. That means that we can't guarantee that it's up to date or consistent, and it means that some columns will be always NULL since they're not applicable. Other DBMSs might include locks of internal spaces (e.g. the sequence number instance) in tables like this. But we'd expect to control such things with a mutex. Mutexes aren't part of this task. See later section "Mutexes". Alternative names: PERFORMANCE_LOCKS, CURRENT_LOCKS. Columns in PERFORMANCE_SCHEMA . LOCKS: Name Comments ---- -------- /* The object_xxxx fields are whatever is necessary to identify an object. "Resource" is a better word but events_waits_current already has object_schema, object_name, object_type, object_instance_begin. Often some of the object_xxxx fields will be blank (NULL). The data type is always VARCHAR(64) CHARACTER SET UTF8, but if a storage engine has a numeric identifier for an object, that's fine, we will show it as decimal with no zerofill. */ object_schema Ordinarily this will have a database name. object_name A table name, a routine name, a file name, or some other resource identifier. If one can lock all objects of a given type, this will be a wildcard. INNODB_LOCKS has: lock_table, lock_index. object_type INNODB.LOCKS_TYPE has: 'RECORD', 'TABLE'. We prefer the term 'row', and we expect there will be more possibilities with more storage engines and future considerations. So our list is: DATABASE, TABLESPACE, TABLE, ROW, INDEX, INDEX KEY, FILE, SEQUENCE, ROUTINE, BLOCK. object_instance_begin The identifier of the lock. It's not enough to identify a lock by object_name. We might have a sequence number from a storage engine here, or we might have a memory address. INNODB_LOCKS has: lock_id. location This is NULL except for rows, index keys, or blocks. This has a location within a table / tablespace. It might be a partial row primary key value, a ROWID, a block number plus a record number within the block (INNODB_LOCKS has lock_space, lock_page, lock_rec). Usually it's numeric. This might have the beginning of a range, but we do not have a way to show the end of a range. /* The transaction_xxxx_id columns have whatever is necessary to identify a transaction. Again, depending on circumstances, some of these may be blank (NULL). In a more normalized structure, we'd probably want to have a single ID and use it to look up the other information. These are always INTEGER fields. */ transaction_process_id Corresponds to "SHOW PROCESSLIST" number transaction_thread_id Corresponds to events_waits_current.thread_id transaction_serial_id Corresponds to InnoDB "trx id" /* The transaction_xxxx_id columns show "who requested the lock". The blocking_transaction_xxxx_id columns show "who currently has the lock (and is therefore blocking)". But the "who is blocking" question is tough. See later discussion. */ blocking_transaction_process_id Who is blocking. blocking_transaction_thread_id Who is blocking. blocking_transaction_serial_id Who is blocking. INNODB_LOCKS has: lock_trx_id. blocking_object_instance_id The lock that we're waiting for. This should point to another row in performance_schema.locks, with a status of "Granted", and with the same resource_xxxx_id columns. Sometimes we're trying to get a row lock and we're waiting for a table lock, so the resource_xxxx_id columns will differ. Sometimes we're trying to get an exclusive lock and we're blocked by several transactions that have a shared lock, so this column might only be showing one of many 'blocking' rows. request_mode CHAR. Use the same abbreviations as in INNODB_LOCKS.LOCK_MODE but allow other common abbreviations for other possible storage engines. 'X' exclusive 'X.GAP' exclusive on gap between keys 'AUTO_INC' exclusive on sequence 'IX' intent exclusive 'IS' intent shared 'IS.GAP' intent shared on gap between keys 'IU' intent update 'S' shared 'S.GAP' shared on gap between keys 'U' update The "mode that you request the lock" may differ from the "mode that you hold the lock", but we show only request_mode. request_status CHAR or ENUM 'GRANTED' request was successful 'UPGRADE' e.g. converting from shared to exclusive 'WAIT' request is entered, probably blocked 'ESCALATE' maybe use this for granularity change 'FAIL' deadlock, timeout, table full request_count If request_status='granted', the value here is the number of other transactions which are waiting for this lock to be released. Might be just a binary value ("is blocking (yes/no)?"). In WL#321 "SHOW LOCK QUEUE" the column name is number_of_threads_waiting_for_this_lock_to_be_released We do not list the waiting transactions, and we do not list the waiting lock requests, that would be hard. Maybe 'waiting_count' would be a better name. storage_engine CHAR. Typically this is 'INNODB'. This is NULL when no storage engine is involved. The storage engine might also be in EVENT_NAME. /* These further columns exist in events_waits_current. */ event_id event_name E.g. 'Wait/Synch/Lock/Row-Exclusive/InnoDB' timer_start When lock was requested. Doesn't appear in our model DBMSs. Data type might be TIMESTAMP. timer_end When lock was granted, or when it failed. timer_wait This is either "timer_end - timer_start" or "current_time - timer_start" in pseudo-picoseconds. nesting_event_id /* These further columns exist in Robin Schumacher's 'syslocks' request. Technically user name and user host are unnecessary, because we can look up them up via thread_id. But we don't want to force users to do subqueries and joins every time, so if they always need these columns, they should get them. */ user_name user_host Perhaps a way to think of a LOCKS table is as a join of EVENTS_WAITS_HISTORY wait/synch/lock rows from different threads, where thread-1.object_name,object-location = thread-2.object-name,object-location and event_name like 'wait/synch/lock%'. Additional columns in Event Summaries ------------------------------------- Since locks are events, we have all the summary information that we have for all events. For example: select * from EVENTS_WAITS_SUMMARY_BY_EVENT_NAME\G ... EVENT_NAME: wait/synch/lock/sql/table COUNT_STAR: 8 SUM_TIMER_WAIT: 31412802980 MIN_TIMER_WAIT: 758914 AVG_TIMER_WAIT: 3926600372 MAX_TIMER_WAIT: 24824640490 But we want a breakdown by result -- what happened to those 8 requests? Therefore we'd have more columns. For example: COUNT_STAR_GRANTED 2 COUNT_STAR_ESCALATED 2 /* DB2 name = lock_escals */ COUNT_STAR_DEADLOCKED 2 COUNT_STAR_TIMEOUT 2 COUNT_STAR_OTHER_ERROR 0 COUNT_STAR_IN_PROGRESS 0 COUNT_STAR_KILLED 0 We must add these columns to EVENTS_WAITS_SUMMARY tables, so a description is necessary in WL#4816 PERFORMANCE_SCHEMA Summaries. They will always be NULL for non-lock events. There is no need for separate LOCKS_SUMMARY views of these tables. Two more possible columns are "high water marks", that is, counters for the maximum events that were ever happening simultaneously. Specifically: HIGH_WATER_MARK_REQUESTED /* How many were ever active at the same time */ HIGH_WATER_MARK_WAITED /* How many of the above were blocked */ You can get the figures by counting rows in PERFORMANCE_SCHEMA.LOCKS. Although this is good for calculating resource usage, and perhaps for deciding how much memory to allocate for some lock tables, it is not really monitoring. We won't have high water marks. Deadlock counting suggestions should go to WL#4689 Deadlock Monitor. Source of Information --------------------- Often we can expect that the storage engine has an internal lock list so the easiest way to get information is by querying the storage engine directly. That would mean changing storage-engine API. Alternatively there might be tables specific to the storage engine that we can read in order to populate PERFORMANCE_SCHEMA.LOCKS. For example, the InnoDB plugin supplies, in information_schema: INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS http://www.innodb.com/doc/innodb_plugin-1.0/innodb-information-schema.html#innodb-information-schema-transactions If the plugin is unavailable one can struggle with SHOW ENGINE INNODB STATUS. Alternatively the performance schema instruments will give us * "who is blocked" and "what the last lock request was" PERFORMANCE_SCHEMA.EVENTS_WAITS_CURRENT * "how many times a table was locked, and for how long, and current status" See WL#4816 HLS text after the words "[ OBJECT SUMMARIES ]" * "locking events in the history" PERFORMANCE_SCHEMA.EVENTS_WAITS_HISTORY * "summary of locks by thread or globally" EVENTS_WAITS_SUMMARY_BY_EVENT_NAME.COUNT_STAR EVENTS_WAITS_SUMMARY_BY_EVENT_NAME.SUM_TIMER_WAIT EVENTS_WAITS_SUMMARY_BY_EVENT_NAME.MIN_TIMER_WAIT EVENTS_WAITS_SUMMARY_BY_EVENT_NAME.AVG_TIMER_WAIT But PERFORMANCE_SCHEMA "history" tables are fixed size, so even a long history might not have every row lock. Perhaps we should have a count of row locks in the "object summaries" table, that is, you could find out that thread x has y row locks on table z. Perhaps we should have the last ten granted row locks in the "object summaries" table, that is, you could find out the rows that thread x has on table z, but only ten of them. With a combination of "long history" plus "count of row locks by table" plus "last 10 row locks on table" you probably can answer the important question "who is blocking". How probably? Peter thinks: if it's 90% that's not as good as what the the storage engine's own table has, but it would be universal, and we could test in a real installation to see if it would be adequate. So MySQL populates PERFORMANCE_SCHEMA.LOCKS in different ways for different storage engines, depending whether we instrument the storage engine code, and depending whether the storage engine exposes information from its own internal tables. What the user sees should be consistent, though -- MySQL must try to make the information look the same, and be in one table. SHOW Syntax ----------- The original (January 2005) request was for a new SHOW statement. Perhaps this requirement is now superseded. We only need the tables. But the specification of "SHOW Syntax" remains in case somebody likes it. SHOW ENGINE INNODB LOCK STATUS [LIKE 'tablename'] [LIMIT number_of_locks_to_print] or SHOW LOCK STATUS WHERE engine='InnoDB' /* later, 'engine='MyISAM' etc. */ [AND resource_object_id='something'] [LIMIT number_of_locks_to_print] Some comments (from the discussion thread) Nowadays MySQL warns that SHOW INNODB STATUS is deprecated syntax and users should use SHOW ENGINE INNODB STATUS instead. Peter expects that this is analogous. Since MySQL has LIKE 'pattern' for other SHOW statements, e.g. SHOW STATUS [LIKE 'pattern'], Peter believes users will expect to see "LIKE 'tablename'" rather than "tablename". And Peter believes "LIMIT number_of_locks_to_print", rather than "number_of_locks_to_print", might help avoid a parsing problem later if we add a new clause before number_of_locks_to_print. Specifically, he worries that someone will want to have SHOW ENGINE INNODB LOCK STATUS WHERE ... LIMIT ... SHOW LOCK STATUS will be the same as SELECT * FROM PERFORMANCE_SCHEMA.LOCKS. Mutexes ------- Originally this specification included mutexes. Now it doesn't. There is a similarity. We could be showing mutexes and filling in these columns of PERFORMANCE_SCHEMA.LOCKS: object_name = event_name, or part of event_name object_type = 'MUTEX' object_instance_begin = address request_mode = 'X' (mutex locking is always exclusive) request_status = 'WAIT' or 'GRANTED' request_count = how many others are blocked Currently one can get some information per mutex with MUTEX_INSTANCES, for example: NAME: wait/synch/mutex/sql/LOG::LOCK_log OBJECT_INSTANCE_BEGIN: 34754648 LOCKED_BY_THREAD_ID: NULL Or one could have a separate table which lists only the holders of currently-held mutexes. In that case, it's simply a list of copies of EVENTS_WAITS_CURRENT mutex rows which don't disappear until the mutex is unlocked. Other lock situations to consider are: GET_LOCKS, FLUSH TABLES WITH READ LOCK, metadata locks. Other worklog entries --------------------- In the past, there have been other suggestions for MySQL lock display: "SHOW TABLE LOCKS [FROM (db|table)] [FOR user];" (WL#320, Jeremy Cole) "SHOW LOCK QUEUE [FROM (db|table)]" (WL#321, Jeremy Cole) "SHOW FULL PROCESSLIST" (WL#1275, Mark Matthews) These are raw-idea-bin suggestions, and Peter thinks we can ignore them. (Sergei Golubchik, on the other hand, thinks we should consider them.) And there is a task in the benchmarks queue: WL#2288 Patch Innodb-code to add status variable to show number of row locks Some of the "mysqladmin debug" code might be worth looking at. Falcon has WL#3765 Falcon Diagnostics. References ---------- BUG#9742: Put a SHOW LOCK TABLES BUG#23076 No way of viewing locks from within the server (Mark Leith) http://bugs.mysql.com/bug.php?id=23076 MySQL 5.6 Reference manual re INNODB_LOCKS table http://dev.mysql.com/doc/refman/5.6/en/innodb-locks-table.html "MySQL Internals: SHOW LOCKS or equivalent" http://lists.mysql.com/internals/33907 Harrison Fisk's patch [mysql internal address]/secure/mailarchive/mail.php?folder=4&mail=24248 BUG#33601 adding a view for information_schema to identify lock waiting relations (another patch) http://bugs.mysql.com/bug.php?id=33601 BUG#46391 No statistics are displayed for named locks from GET_LOCK() etc. A table [performance_schema] locks is mentioned in the WL#2360 file attachment [mysql internal address]/worklog/download/performance.txt?fid=400 Some more private references are visible by clicking on the 'Progress' for 2009-04-13, to see what was deleted from 'References' on this date.
Copyright (c) 2000, 2018, Oracle Corporation and/or its affiliates. All rights reserved.