One of my colleagues recently redesigned a system to schedule work for programs to do, and needed to write a multiple-table UPDATE with ORDER BY and LIMIT, neither of which is supported for multiple-table UPDATE in MySQL. This article explains how to do it anyway, and shows how to rewrite a first attempt for hundreds of times better performance.

Why would you ever want to deliberately cause a deadlock? Sometimes a very large deadlock in MySQL will fill the output of SHOW ENGINE INNODB STATUS until it truncates, so you can't see information about transactions, log and I/O, buffers, and so forth. I know only two solutions to this problem: 1) restart MySQL and 2) cause a small deadlock so the LAST DETECTED DEADLOCK section shrinks to an acceptable size. In this article I'll show you how to cause a small deadlock, and how to use innotop to do it more easily.

This article is part of a series on how to use innotop to make your life easier.

In an earlier article I explored the finer points of multi-table, cross-database deletes in MySQL, but I didn't get it quite right. This article explains the subtleties I've discovered since then. This is more complex than the MySQL manual documents.

I worked with Microsoft SQL Server 2000 at my previous employer, and was accustomed to explicitly defining the locking mode I wanted, by providing lock hints on queries. MySQL gives less control over locking, but there are ways to control the type of locks a query will acquire. This article explores those techniques.

A "little-known way," I claim, and yet it happens all the time -- precisely because it's little-known. Experts will quickly recognize where I'm going to go with this article, but I hope many others in my audience will understand deadlocks more deeply after reading it. I'll use MySQL and InnoDB for illustration purposes, but the scenario this article describes (dramatic music, please!) could happen to you, too! And probably will someday, unless you're one of the elite few (ok, enough drama) who know how to avoid it.

In this article I'll briefly introduce deadlocks, give an example of one that happened at my employer recently, analyze and explain it, and then disclose the secret way to avoid cause such deadlocks. Then I'll show you how to reproduce the deadlock and dive into the gory details of what goes on internally with InnoDB. I'll also demonstrate how …

This is one in a series of articles on how to use the innotop MySQL and InnoDB monitor. In this article I show how innotop can display locks that are causing a transaction to wait.

A couple of people have written in reporting innotop crashes. Unfortunately I don't have access to enough variations of operating systems and MySQL versions to test everything myself, but if you're able to help by sending me a bug report when innotop crashes, I'm willing to work on fixing it! I hope soon I'll have a much larger test suite, and am grateful for your help with that. This article explains what information I need to reproduce and debug crashes.

I've made several improvements to the innotop InnoDB and MySQL monitor, and it's ready to get from the download link on the original article.

MySQL is adding more tools to monitor its internals with every new release, but one thing it still lacks is a way to find out who is locking what, and therefore which transactions block which other ones. This is such a vital feature that I'm considering writing my own patch to the source! Still, it is possible, to a limited extent, to find out who's locking resources. In this article I'll explain how you can do that.

This article is the second in a series on how to use the innotop MySQL and InnoDB monitor.

This is the first in a series of articles I?ll write on how to use innotop, the MySQL and InnoDB monitor I?m developing. This article explains how to install innotop.