Working around MySQL lock metadata

There are multiple documents about innodb locks on MySQL 5.7:

Granularity of locks

There are 4 types of granularity of locks

  • Shared lock
  • Exclusive lock
  • Intention shard lock
  • Intention exclusive lock

Types of locks

  • Record locks
  • Gap locks
  • Next-Key locks
    • the value in the index for the next-key lock indicates as “supreme” pseudo-record, which is not a real value
  • Insert Intention locks
  • AUTO-INC locks

Lock tables

  • information_schema.innodb_trx
  • information_schema.innodb_locks
  • information_schema.innodb_lock_waits

Use cases

Update queries without an index

Create a test table and insert a few records.

create database test;
use test;

create table test1(id INTEGER AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL);
insert into test1(name, created_at, updated_at) values('test', NOW(), NOW());
insert into test1(name, created_at, updated_at) values('test 2', NOW(), NOW());
insert into test1(name, created_at, updated_at) values('test 3', NOW(), NOW());
insert into test1(name, created_at, updated_at) values('test 4', NOW(), NOW());
select * from test1;

Then the last selct result would be like:

+----+--------+---------------------+---------------------+
| id | name   | created_at          | updated_at          |
+----+--------+---------------------+---------------------+
|  1 | test   | 2023-01-17 02:02:35 | 2023-01-17 02:02:35 |
|  2 | test 2 | 2023-01-17 02:02:36 | 2023-01-17 02:02:36 |
|  3 | test 3 | 2023-01-17 02:02:38 | 2023-01-17 02:02:38 |
|  4 | test 4 | 2023-01-17 02:02:40 | 2023-01-17 02:02:40 |
+----+--------+---------------------+---------------------+
4 rows in set (0.01 sec)

Checking what the lock results will be:

  1. session A
     BEGIN;
     update test1 set name = 'test 2 changed' where name = 'test 2';
    
  2. session B: This will be locked because of the query on session A.
     insert into test1(name, created_at, updated_at) values('test 5', NOW(), NOW());
    

Then we can see lock information like followings:

mysql> select * from information_schema.innodb_locks \G
*************************** 1. row ***************************
    lock_id: 1814:24:3:1
lock_trx_id: 1814
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`test1`
 lock_index: PRIMARY
 lock_space: 24
  lock_page: 3
   lock_rec: 1
  lock_data: supremum pseudo-record
*************************** 2. row ***************************
    lock_id: 1811:24:3:1
lock_trx_id: 1811
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`test1`
 lock_index: PRIMARY
 lock_space: 24
  lock_page: 3
   lock_rec: 1
  lock_data: supremum pseudo-record
2 rows in set, 1 warning (0.00 sec)

You can see lock_data is the supremum pseudo-record, which is not a real record created for next-key lock.

Other tables look like followings:

mysql> select * from information_schema.innodb_trx \G
*************************** 1. row ***************************
                    trx_id: 1814
                 trx_state: LOCK WAIT
               trx_started: 2023-01-17 02:24:59
     trx_requested_lock_id: 1814:24:3:1
          trx_wait_started: 2023-01-17 02:24:59
                trx_weight: 2
       trx_mysql_thread_id: 4
                 trx_query: insert into test1(name, created_at, updated_at) values('test 5', NOW(), NOW())
       trx_operation_state: inserting
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
*************************** 2. row ***************************
                    trx_id: 1811
                 trx_state: RUNNING
               trx_started: 2023-01-17 02:03:26
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 3
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 5
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
mysql> select * from information_schema.innodb_lock_waits \G
*************************** 1. row ***************************
requesting_trx_id: 1815
requested_lock_id: 1815:24:3:1
  blocking_trx_id: 1811
 blocking_lock_id: 1811:24:3:1
1 row in set, 1 warning (0.01 sec)

2024

Back to Top ↑

2023

Follow Kubernetes the Hard way

4 minute read

This article was written by just following Kelsey Hightower’s Kubernetes Hardway document to understand Kubernetes internal architecture.

Gcp Billing Analyze

less than 1 minute read

There are a few documents to manage billing data in BigQuery Attribution of committed use discount fees and credits How to export to BigQuery Structur...

Prometheus Metrics Overview on Grafana

1 minute read

In this post, some variables defined in Grafana are used for Prometheus metrics, including $__rate_interval: This article describes the benefit of this va...

Use Google Secret Manager in a GKE cluster

3 minute read

There are an awesome article about the options to use the Google Secret Manager and their pros and cons. In this article, use Secrets Store CSI Driver by fol...

Working around MySQL lock metadata

2 minute read

There are multiple documents about innodb locks on MySQL 5.7: InnoDB locking Locks Set by Different SQL Statements in InnoDB Using InnoDB Transaction ...

Upgrade Windows 10 to Windows 11

3 minute read

I used to use Windows 11, but for some reasons, the OS stopped working and I needed to clean-install it from Windows 10 from windows recovery environment.

Back to Top ↑

2022

MySQL backup and restore

1 minute read

In this article, explain how to backup MySQL database using Percona Xtrabackup. There are two binaries, innobackupex and xtrabackup. innobackupex is a wrappe...

tmux

1 minute read

Basic configuration

Back to Top ↑

2021

MySQL Replication

1 minute read

This configuration is for the version 5.7 and it’s minimum configuration in the official document.

jq cheetsheet

less than 1 minute read

jq is used to parse JSON result, format and output on the cli.

Compare static site generator

less than 1 minute read

There are many web sites to compare static site generator, but they miss some explanations that require to me. For some people, these features are important ...

Back to Top ↑

2020

Getting Started with Kubernetes Deployment

less than 1 minute read

The deployment is many use cases and in this page, they’re not described. For the details for those use cases or the concept of deployment, see official page.

Overview about MySQL Lock

2 minute read

This document is written for MySQL 5.7, so these contents may be not correct for other versions.

MySQL Performance

2 minute read

This document is written for MySQL 5.7, so these contents may be not correct for other versions. In this page, performance_schema is mainly discussed.

Git hooks

less than 1 minute read

Configurations

gitHub pages

3 minute read

Getting Started See Official tutorial for detail steps.

Gitconfig

1 minute read

Configuration The detail for gitconfig is written in official page.

git cli

less than 1 minute read

Written in March 2020.

MySQL Tuner

less than 1 minute read

MySQL Tuner tool This is a tool to review a configuration for MySQL server.

kubectl cheetsheet

less than 1 minute read

Collect recent error logs If the logs are outputted by zap, error messages are aggregated by checking level = error. This log does not work very well if the ...

Introduction to GCP Cloud endpoints

less than 1 minute read

The Cloud endpoint is actually the NGINX proxy which offers the following features on GCP. Authentication and validation Logging and monitoring in GCP

HTTP/2 for Go

1 minute read

http package in golang supports HTTP/2 protocols. It’s automatically configured.

Back to Top ↑

2019

Terraform overview

1 minute read

Basic concepts There are some basic components for terraform.

Protocol Buffers for Go with Gadgets

less than 1 minute read

gogo/protobuf is the library to store some extensions from golang/protobuf in this repository. There are some useful packages that golang/protobuf does not p...

Introduction to GCP Cloud CDN

less than 1 minute read

Target upstream services Cloud CDN can have only GCP load balancer as the upstream services. And GCP load balancer can configure one of followings for backen...

Getting Started with Google closure library

less than 1 minute read

Some JavaScript library depends on Google Closure. If you need to understand the behavior of such a library, you have to know closure. The official document ...

Back to Top ↑