Bug #26590 MySQL does not allow more than 1023 open transactions
Submitted: 23 Feb 2007 11:30 Modified: 15 Jun 2010 15:35
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0, 5.1, etc. OS:Any
Assigned to: Sunny Bains CPU Architecture:Any
Tags: v6

[23 Feb 2007 11:30] Domas Mituzas
Description:
at 1024 transactions, that have undo records (as in, edited any data), InnoDB will fail to work:

mysql> insert into yt values (66666);
ERROR 1114 (HY000): The table 'yt' is full
mysql> insert into t2 values (66666);
ERROR 1114 (HY000): The table 't2' is full
mysql> create table xxxxxxxx (a int) engine=InnoDB;
ERROR 1005 (HY000): Can't create table 'test.xxxxxxxx' (errno: 135)

Error log will have:

070223 13:26:01InnoDB: Warning: cannot find a free slot for an undo log. Do you have too
InnoDB: many active transactions running concurrently?
070223 13:26:22InnoDB: Warning: cannot find a free slot for an undo log. Do you have too
InnoDB: many active transactions running concurrently?
070223 13:26:22  InnoDB: Warning: cannot create table `test`.`xxxxxxx` because tablespace full

How to repeat:
<?
        $dbs=array();
        $i=1024;
        while($i--) {
                $db=$dbs[$i]=mysql_connect("localhost","root","",1);
                mysql_select_db("test",$db);
                mysql_query("BEGIN",$db) or die(mysql_error());
                mysql_query("INSERT INTO yt VALUES ($i)",$db) or die(mysql_error());
        }
        sleep(600);
?>

Suggested fix:
allow more. unlimited, or run-time settable value.
[23 Feb 2007 11:44] Domas Mituzas
Raising severity to S1, in many cases this may mean indefinite hang without workaround.
[23 Feb 2007 15:02] Heikki Tuuri
I am marking this as a feature request. An application should try to keep the number of concurrently open transactions less than 1000. Having lots of open transactions can cause bottlenecks in locking, for example.
[23 Feb 2007 22:16] Domas Mituzas
This either has to be portrayed as InnoDB limitation in documentation, or bumped up. 

It is no longer 1996, where "oh noes, 1000 transactions gonna crash my 486, rewrite everything in ASM".
Databases nowadays are served by  many-gigabytes memory / many cores boxes, that can handle far more than 1000 concurrent connections. 

Additionally, in scaled out environments single database isn't the only point-of-waiting. Blocking on other resources may cause idle transactions, so even if they are short-lived (100ms, for example, what is quite good metric for web application execution), the throughput of such a system is limited to 10000 TPS in theory, and actual number is much lower.

Opinions stating that 1000 concurrent transactions is bad application design need serious reality check. 
Even if such load is exceptional, database should be last element to crash or refuse service. 
Especially, if fix for such issue is few constants away. 

So, it is a bug, either InnoDB's, or in documentation.
[24 Feb 2007 15:41] James Day
Heikki, feature request is sort of OK, but it's happened in normal service to high load customers even on fairly ordinary hardware (couple of cores, 16GB of RAM) so it seems like a problem that will grow.

It seems that bug #18828 should be a duplicate of this or this a duplicate of it.
[26 Feb 2007 13:44] Heikki Tuuri
http://bugs.mysql.com/bug.php?id=18828 requests us to add a descriptive error code if we cannot allocate enough active transactions.
[26 Feb 2007 13:44] Heikki Tuuri
http://bugs.mysql.com/bug.php?id=26608 is associated with this.
[26 Feb 2007 15:25] Heikki Tuuri
Assigning this to Sunny.
[16 Apr 2007 12:10] Heikki Tuuri
Sunny has a patch for this. We may apply it to 5.2. It is a bit risky to put it to 5.1 or 5.0, since it involves creation of 128 rollback segment pages at the database creation, and thus is quite a big change to InnoDB.
[23 Apr 2007 19:08] Mark Leith
Hi Heikki,

What would this effectively raise the limit on number of transactions to? Any ideas?

Cheers,

Mark
[24 Apr 2007 1:32] Sunny Bains
128K open transactions.
[9 Nov 2007 17:21] Heikki Tuuri
Maybe along the zip code. No fix in 5.0 or 5.1.
[19 Feb 2008 16:15] Heikki Tuuri
A complex patch. Delayed until 6.0 or later.
[16 Mar 2008 20:51] Peter Zaitsev
Heikki,

Can you clarify one thing - does it corresponds to concurrently actively writing transactions only does this limit applies to unpurged transactions as well.

Ie if transaction was committed but not  yet purged will it still use the slot ?
[18 Mar 2008 4:15] Sunny Bains
If you are affected by this bug and the problem persists on restart then see Bug#35352 for a possible solution.
[1 Jul 2009 22:57] Mark Callaghan
It would be really nice to have this in the official InnoDB release.
[2 Jul 2009 1:29] Ken Jacobs
Because this would change on disk structures, it has to be done 
under the control of innodb_file_format management.   That really
can't be done during the life of a release.   Will consider for a
future release.
[30 Nov 2009 23:07] James Day
Bug #43655, running out of space in tablespace and bug #49238, DDL crashes server when it's at the open transaction limit, are possibly related to this one. The two might be the same bug or might have different resolution possibilities.
[30 Nov 2009 23:15] James Day
Correction: bug #43665 [corrected], running out of space in tablespace and bug #49238, DDL crashes server when it's at the open transaction limit, are possibly related to this one. The two might be the same bug or might have different resolution possibilities.
[14 Apr 2010 3:22] James Day
See http://blogs.innodb.com/wp/2010/04/innodb-multiple-rollback-segments/ for the planned improvement for this that is expected to raise the limit to 128,000 concurrent transactions that have modified data. Release date is not yet known.
[14 Apr 2010 7:27] Calvin Sun
The fix is in the latest 5.5.4 release.
[14 May 2010 5:18] Sunny Bains
Fix is in the 5.5.4 release.

Regards,
-sunny
[19 May 2010 14:08] Paul DuBois
Noted in 5.5.4 changelog:

The limit of 1023 concurrent data-modifying transactions has been
raised. The limit is now 128 * 1023 concurrent transactions that
generate undo records. You can remove any workarounds that require
changing the proper structure of your transactions, such as
committing more frequently or delaying DML operations to the end of a
transaction.

See http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-rollback-segments.html
[15 Jun 2010 8:18] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (merge vers: 5.1.47) (pib:16)
[15 Jun 2010 8:35] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:mmakela@bk-internal.mysql.com-20100415070122-1nxji8ym4mao13ao) (pib:16)
[15 Jun 2010 15:35] Paul DuBois
Already fixed in 5.5.