MySQL Bugs: #72362: Users may use an unlimited amount of undo space

Bug #72362	Users may use an unlimited amount of undo space
Submitted:	17 Apr 2014 1:40	Modified:	17 Apr 2014 16:16
Reporter:	Jeremy Cole (Basic Quality Contributor) (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	All	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	innodb, limits, space, undo

Description:
As InnoDB is currently implemented, it is possible for users to use an unlimited amount of space for undo history by either:

  1. Creating a single or many large transactions writing to as few as one row many times, directly accumulating undo history.
  2. Leaving open a transaction (while minimally keeping it alive) with a read view, indirectly accumulating undo history by preventing purge.

(Additionally, the user could combine both of these approaches, leaving open a transaction with a read view while simultaneously generating a large number of small transactions which individually have a small amount of undo history. This may better prevent detection of the culprit.)

Both of these situations allow a regular user, without special privileges, to consume large amounts of disk space in the system tablespace, potentially causing the system tablespace to be expanded to consume all filesystem space and without an easy recourse from the system administrator.

How to repeat:
N/A

Suggested fix:
Add configuration options to:
  * Limit the undo space consumed by a single transaction.
  * Limit the aggregate undo space consumed by a given user.
  * Limit the age of the transaction read view for a given user.

Jeremy,

I fully understand what you are writing about here. However, the problem that I see is what should be the action if any of the constraints is implemented ???

What should InnoDB do to the problematic transaction or transactions ??? 

I do not think that rollback is the correct action, since it would be too harsh. The only thing that I see that could be done is that some warning or alert is written to the error log and / or to some other output. In that case, DBA would have some notice about what is happening and to whom is that happening.

You have not mentioned, but there are also many badly written applications, where COMMIT is waiting for the end user to click on something or press something. And the end user might decide to go for a lunch or similar.

If you would agree with this then it would become a nice feature request.

What do you think about it ??

Sinisa,

I don't see why a transaction shouldn't be rolled back. The default limits could be quite liberal (or even "infinity") to avoid scaring hapless users. However in a real production environment the limits can be conservative and the consequences of rolling back a problematic transaction are much less "harsh" than, for instance, running out of disk space on a critical master server. Transactions may fail for any number of reasons and applications should already be prepared for that to happen -- this would just add one more way they could fail.

Oracle Database and many others certainly have precedent for "snapshot too old" errors, and limits to consumption of undo log space.

Regards,

Jeremy

Having a variable that has a range up to "infinity", with the initial default of infinity, as well, could be a solution.

The action that would be triggered by described situation is something that will be discussed internally. The option exposed and proposed by you will be on the table as well.

Verified !!!!

related: http://bugs.mysql.com/bug.php?id=54455