8/31/2023 0 Comments Mysql deadlock found![]() We run this update function with a small amount of concurrency using the Bluebird.I am having the exceptions below during the execution of a process and the process terminates after these exceptions. We have a function in our NodeJS code to update the end_date column for the previous version of a record, and if necessary, mark it as deleted. Now imagine this running in our production code, which is having to insert, query, and potentially update millions of records on a daily basis. The table might now look something like this: 1 ![]() Update the end_date of the previous versions to be less than the newest version.Locate the version of each record prior to the most recent one.Suppose a new version of ABC and XYZ both come in 2 days later. We might receive 100 rows in a page, some of which are records we have never seen before, while others might be an update to a record that we have backed up in the past. If things still aren’t clicking I would suggest you refer to the following blog post.ĭuring the course of our backup, we will retrieve a page of records from the Salesforce API, and then insert the information for each record into our table. If you aren’t familiar with gap locks, you can read an introduction to them in the MySQL docs and their explanation why it is used. This plays an important role in the deadlock, which (SPOILER ALERT) is caused by a technique known as a gap lock. Note that we have the key sobject_start_end_deleted which is a non-unique index. | id | salesforce_id | sobject_type | start_date | end_date | deleted | ![]() 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. ![]() 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). 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 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |