All posts by Kuba Łopuszański

InnoDB Data Locking – Part 3 “Deadlocks”

February 9, 2021MySQLKuba Łopuszański

In this blog series, I’m describing how InnoDB locks data (tables and rows) in order to provide illusion to clients that their queries are executed one after another, and how this was improved in recent releases.
In InnoDB Data Locking – Part 1 “Introduction” I’ve introduced basic concepts required to understand current post:

databases, tables, rows (like files on a shared drive, spreadsheets inside a file, and rows inside a spreadsheet)
serializability of transactions (ability to explain states observed over time with a convincing story about relative order of parallel operations)
timeouts (for misbehaving lock owners, and to resolve deadlocks)
reader-writer lock (shared/exclusive access rights)
starvation (permanent inflow of readers starving a writer waiting for its turn)
queueing (FIFO, or priority)
read views (read-only snapshots which allow stale reads concurrent to new writes)
granularity of a lock (access right to everything vs.

…

InnoDB Data Locking – Part 2.5 “Locks” (Deeper dive)

November 23, 2020MySQLKuba Łopuszański

All together now

Let’s now put together all that we’ve learned in InnoDB Data Locking – Part 2 “Locks” about table and record locks to understand following situation:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM t FOR SHARE;
+----+
| id |
+----+
|  5 |
| 10 |
| 42 |
+----+
3 rows in set (0.00 sec)

mysql> DELETE FROM t WHERE id=10;
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t VALUES (4);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT INDEX_NAME,LOCK_TYPE,LOCK_DATA,LOCK_MODE
       FROM performance_schema.data_locks

…

InnoDB Data Locking – Part 2 “Locks”

October 14, 2020MySQLKuba Łopuszański

In InnoDB Data Locking – Part 1 “Introduction” we’ve described the difficulties Lock System tries to solve using metaphor of people trying to concurrently edit spreadsheets. While it might be useful metaphor to get some intuitions about the problem, to talk about solutions it helps to know at least a little about the “reality” this metaphor maps to.…

InnoDB Data Locking – Part 1 “Introduction”

September 4, 2020MySQLKuba Łopuszański

In this blog series, I’d like to introduce you gently to the topic on which I was working last 2 years, which is improving how InnoDB locks data (tables and rows) in order to provide illusion to clients that their queries are executed one after another, while in reality there is a lot of concurrency.…

MySQL 8.0: Excluding the Buffer Pool from a Core File

January 28, 2019MySQLKuba Łopuszański

The latest release of MySQL 8.0 introduces a new dynamic system variable @@innodb_buffer_pool_in_core_file which lets you omit the Buffer Pool’s memory content when generating a core file.

This change is an adaptation of a patch contributed by the Facebook team. We would like to thank and acknowledge this important and timely contribution by Facebook.…

MySQL Server Blog

News from the MySQL Server Team