MySQL Bugs: #61956: Undo Slots not available after being maxed out from UPDATE statements

Bug #61956	Undo Slots not available after being maxed out from UPDATE statements
Submitted:	22 Jul 2011 16:05	Modified:	16 May 2013 4:48
Reporter:	Chris Calender	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.0, 5.1, 5.1.57	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	too many active concurrent transactions, undo slot, UPDATE
Triage:	Needs Triage: D3 (Medium)

Description:
If you max out the undo slots using UPDATE statements, then afterward, you will only have capacity for 525 transactions instead of 1024.

..

Below, step #1 works fine, and is expected.  That is what I'm using as a baseline, so-to-speak.

Step #1:

1. On fresh instance, issue 1024 concurrent INSERTs.

On the 1024 INSERT, you will receive the "Too many active concurrent transactions" error.

This is expected.

2. If you repeat this again, 1024 concurrent INSERTs, the same thing happens .. 
you receive the "Too many active concurrent transactions" error, which, again, is expected.

Step #2:

Now, and here is where the problem comes is, try to do the same test but at step #1, perform enough UPDATEs (instead of INSERTs) 
to max out the concurrent transactions.  Since it is UPDATEs, you can only have 512, hence 513 will trigger the error (UPDATE requires 2 undo slots).
So perform 513 concurrent UPDATEs.  Also, let's use a new table, just to verify that it can be any tables involved.

1. On fresh instance, issue 513 concurrent UPDATEs.

On the 513 UPDATE, you will receive the "Too many active concurrent transactions" error.

This is expected.

2. Issue 1024 concurrent INSERTs.

Now -->  Notice, the "Too many active concurrent transactions" error arises on the 526 transaction!

This shows that not all of the undo slots were returned!!!

Where did they go???

Note #1: You can substitute DELETEs for INSERTs in these tests and you will see the same outputs.

Note #2: You can stop/start MySQL, and those undo slots are still consumed.  There is no way to get them back now, and so your max concurrent transactions is limited .. effectively cut in half!

Note #3: This is not reproducible (as-is) in 5.5 due to the 128 Rollback segments (so 128 * 1024 undo slots), though I imagine the same thing could be occurring, you'd just need to expand the tests to use 128K concurrent ops instead of 1K.

How to repeat:
Step #1:

1. 

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

SET @@GLOBAL.max_connections=1025;

<? run php script .. 1024 concurrent INSERTs to table `t` ?>
i = 1024
Error: Too many active concurrent transactions
<? end php script/output ?>

2. 

<? run php script .. 1024 concurrent INSERTs to table `t` ?>
i = 1024
Error: Too many active concurrent transactions
<? end php script/output ?>

Step #2:

1. 

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Populate t1 with 1024 records .. (i.e., 1, 2, 3, 4, ...., 1023, 1024)

<? run php script .. 513 concurrent UPDATEs to table `t1` ?>
i = 513
Error: Too many active concurrent transactions
<? end php script/output ?>

2. 

<? run php script .. 1024 concurrent INSERTs to table `t` ?>
i = 1024
$ dbs[i] = $ dbs[526]
Error: Too many active concurrent transactions
<? end php script/output ?>

<--- Note that this is where it can only go up to 525 transactions once every undo slot has been filled by incompleted UPDATEs.

Here are the basic scripts I used ..

For INSERT into `t`:

<?php
  $dbs=array();
  for ($i = 1; $i <= 1024; $i++) {
    $db=$dbs[$i]=mysql_connect("localhost:3307","root","xxxx",1);
    echo "$ dbs[i] = $ dbs["  . $i . "]<br>";
    mysql_select_db("test",$db);
    mysql_query("BEGIN",$db) or die(mysql_error());
    mysql_query("INSERT INTO t VALUES ($i)",$db) or die(mysql_error());
  }
?>

For UPDATE of `t1`:

<?php
  $dbs=array();
  for ($i = 1; $i <= 513; $i++) {
    $db=$dbs[$i]=mysql_connect("localhost:3307","root","xxxx",1);
    echo "$ dbs[i] = $ dbs["  . $i . "]<br>";
    mysql_select_db("test",$db);
    mysql_query("BEGIN",$db) or die(mysql_error());
    mysql_query("UPDATE t1 SET id=$i+1024 WHERE id=$i",$db) or die(mysql_error());
  }
?>

Thank you for the report.

Verified as described. This is fixed in trunk. Latest version I could repeat it is 5.1.59

I know this is quite an old thread, but can somebody please confirm, in what release this is fixed?
If there's a hotfix for this, please point as well.

Many thanks

Once caused, the problem persists on disk and it takes a mysqldump and reload to get rid of it. Do that, then upgrade to at least 5.1.62 to get a fix for a related bug:

"With 1024 concurrent InnoDB transactions running concurrently and the innodb_file_per_table setting enabled, a CREATE TABLE operation for an InnoDB table could fail. The .ibd file from the failed CREATE TABLE was left behind, preventing the table from being created later, after the load had dropped."

Or mysqldump and reload, then upgrade to any production release of 5.5 and get the much bigger concurrent transaction limit and other benefits of 5.5.

James Day, MySQL Senior Principal Support Engineer, Oracle

[13 May 2013 2:14] Sunny Bains

This issue is fixed in 5.5+. There is now support for 128 * 1024 RW concurrent transactions.