April 21, 2014

SELECT LOCK IN SHARE MODE and FOR UPDATE

Baron wrote nice article comparing locking hints in MySQL and SQL Server. In MySQL/Innodb LOCK IN SHARE MODE and SELECT FOR UPDATE are more than hints. Behavior will be different from normal SELECT statements. Here is simple example:

Introducing backup locks in Percona Server

TL;DR version: The backup locks feature introduced in Percona Server 5.6.16-64.0 is a lightweight alternative to FLUSH TABLES WITH READ LOCK and can be used to take both physical and logical backups with less downtime on busy servers. To employ the feature with mysqldump, use mysqldump –lock-for-backup –single-transaction. The next release of Percona XtraBackup will […]

One more InnoDB gap lock to avoid

While troubleshooting deadlocks for a customer, I came around an interesting situation involving InnoDB gap locks. For a non-INSERT write operation where the WHERE clause does not match any row, I expected there should’ve been no locks to be held by the transaction, but I was wrong. Let’s take a look at this table and […]

Logging Deadlock errors

The principal source of information for InnoDB diagnostics is the output of SHOW ENGINE INNODB STATUS but there are some sections that are not very useful. For example, LATEST DETECTED DEADLOCK only shows, as the name implies, the latest error detected. If you have 100 deadlocks per minute you will be able to see only […]

Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks

Percona XtraDB Cluster (PXC) and the technology it uses (Galera) is an exciting alternative to traditional MySQL replication.  For those who don’t know, it gives you: Fully Synchronous replication with a write latency increase equivalent to a ping RTT to the furthest node Automatic cluster synchronization, both incremental and full restores The ability to read […]

Innodb Table Locks

Innodb uses row level locks right ? So if you see locked tables reported in SHOW ENGINE INNODB STATUS you might be confused and rightfully so as Innodb table locking is a bit more complicated than traditional MyISAM table locks. Let me start with some examples. First lets run SELECT Query:

As you can […]

InnoDB’s gap locks

One of the most important features of InnoDB is the row level locking. This feature provides better concurrency under heavy write load but needs additional precautions to avoid phantom reads and to get a consistent Statement based replication. To accomplish that, row level locking databases also acquire gap locks. What is a Phantom Read A […]

MySQL Configuration Wizard Updated

We’ve released an updated version of the MySQL Configuration Wizard we announced at the end of last year. If you don’t remember that announcement, here’s the short version: this is a tool to help you generate my.cnf files based on your server’s hardware and other characteristics. We’ve gotten really good feedback on this tool, including […]

When Does InnoDB Update Table Statistics? (And When It Can Bite)

An InnoDB table statistics is used for JOIN optimizations and helping the MySQL optimizer choose the appropriate index for a query. If a table’s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow query log until InnoDB again updates the statistics. But when does InnoDB […]