Mysql> insert into record (salesforce_id, sobject_type, start_date, deleted) `salesforce_id`='ABC' AND `start_date` insert into record (salesforce_id, sobject_type, start_date, deleted) TRANSACTION 1418022806, ACTIVE 0 sec starting index read Here is an example with most of the irrelevant data removed from the output: 1 The output can be rather large, but there is a section specifically titled LATEST DETECTED DEADLOCK which is where you should focus your efforts. I recommend using '\G' rather than ' ' as a query terminator as the output is much more friendly to read. You can run the following to access the stored information about the most recent deadlock: If your user isn’t privileged simply ask your administrator to execute the query for you and forward the output. If you don’t have this privilege you will simply get an error when you attempt to run the query below. Locating deadlocked transactionsĪt a minimum, to locate the transactions (and more specifically, the deadlocked SQL statements), you will need the PROCESS privilege for your MySQL user. To be more precise, we had experienced deadlocks due to the same root cause several months prior, but some recently improved, self-imposed monitoring of production was robust enough to bring the error to our immediate attention.įortunately, MySQL (using the InnoDB engine) offers an extremely simple way to diagnose deadlocks, assuming you know where to look.
#Database deadlock resolution upgrade#
While the upgrade accomplished this goal, it also brought along a small handful of deadlocks. The goal was to provide a performance improvement to our application without having to change any code. Namely, we upgraded our MySQL instances in Amazon RDS to use SSDs as opposed to HDDs (or magnetic disks, as they are called in the AWS console).
#Database deadlock resolution code#
Ultimately, we realized that while we had not deployed any code changes, we had modified our infrastructure. While this wasn’t the first time we had encountered deadlocks (more on that later), this was extremely surprising to us as we hadn’t deployed any code changes over the holidays. Over the 2015 holiday season we noticed that some of our backups were reporting MySQL deadlock errors. Spanning uses MySQL for several of our products, and our Backup for Salesforce product is no exception.
0 kommentar(er)
