MySQL – Setting a Time Limit for a Transaction in MySQL/InnoDB

innodbMySQL

This sprang from this related question, where I wanted to know how to force two transactions to occur sequentially in a trivial case (where both are operating on only a single row). I got an answer—use SELECT ... FOR UPDATE as the first line of both transactions—but this leads to a problem: If the first transaction is never committed or rolled back, then the second transaction will be blocked indefinitely. The innodb_lock_wait_timeout variable sets the number of seconds after which the client trying to make the second transaction would be told "Sorry, try again"… but as far as I can tell, they'd be trying again until the next server reboot. So:

Surely there must be a way to force a ROLLBACK if a transaction is taking forever? Must I resort to using a daemon to kill such transactions, and if so, what would such a daemon look like?
If a connection is killed by wait_timeout or interactive_timeout mid-transaction, is the transaction rolled back? Is there a way to test this from the console?

Clarification: innodb_lock_wait_timeout sets the number of seconds that a transaction will wait for a lock to be released before giving up; what I want is a way of forcing a lock to be released.

Update 1: Here's a simple example that demonstrates why innodb_lock_wait_timeout is not sufficient to ensure that the second transaction is not blocked by the first:

START TRANSACTION;
SELECT SLEEP(55);
COMMIT;

With the default setting of innodb_lock_wait_timeout = 50, this transaction completes without errors after 55 seconds. And if you add an UPDATE before the SLEEP line, then initiate a second transaction from another client that tries to SELECT ... FOR UPDATE the same row, it's the second transaction that times out, not the one that fell asleep.

What I'm looking for is a way to force an end to this transaction's restful slumber.

Update 2: In response to to hobodave's concerns about how realistic the example above is, here's an alternate scenario: A DBA connects to a live server and runs

START TRANSACTION
SELECT ... FOR UPDATE

where the second line locks up a row that the application frequently writes to. Then the DBA is interrupted and walks away, forgetting to end the transaction. The application grinds to a halt until the row is unlocked. I'd like to minimize the time that the application is stuck as a result of this mistake.

Best Answer

More than half this thread seems to be about how to ask questions on ServerFault. I think the question makes sense and is pretty simple: How do you automatically roll back a stalled transaction?

One solution, if you're willing to kill the whole connection, is to set wait_timeout/interactive_timeout. See https://stackoverflow.com/questions/9936699/mysql-rollback-on-transaction-with-lost-disconnected-connection.

Related Solutions

MySQL InnoDB – Cons of innodb_file_per_table

I have the complete answer for this one.

Once innodb_file_per_table is put in place, and new InnoDB tables can be shrunk using ALTER TABLE <innodb-table-name> ENGINE=InnoDB'; This will shrink new .ibd files GUARANTEED.

If you run ALTER TABLE <innodb-table-name> ENGINE=InnoDB'; on an InnoDB table created before you used innodb_file_per_table, it will yank the data and indexes for that table out of the ibdata1 file and store it in a .ibd file, This will leave a permanent pigeon whole in the ibdata1 that can never be reused.

The ibdata1 file normally houses four types of information

Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
- Rollback Segments
- Undo Space
Table Metadata (Data Dictionary)
Double Write Buffer (background writing to prevent reliance on OS caching)
Insert Buffer (managing changes to non-unique secondary indexes)
See the Pictorial Representation of ibdata1

Here is the guaranteed way to shrink the ibdata1 file pretty much forever...

STEP 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

STEP 02) Drop all databases (except mysql, information_schema and performance_schema schemas)

STEP 03) Shutdown mysql

STEP 04) Add the following lines to /etc/my.cnf

[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

STEP 05) Delete ibdata1, ib_logfile0 and ib_logfile1 (see update below before deleting!)

At this point, there should only be the mysql schema in /var/lib/mysql

STEP 06) Restart mysql

This will recreate ibdata1 at 10MB (do not configure the option) , ib_logfile0 and ib_logfile1 at 1G each

STEP 07) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata and intermittent MVCC data.

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable OR ALTER TABLE mydb.mytable ENGINE=InnoDB; and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this numerous times in my career as a MySQL DBA without so much as a single problem thereafter. In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 50MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul.

UPDATE 2013-07-02 15:08 EDT

There is a caveat I have in this regard that I updated in other posts of mine but I missed this: I am updating my answer a little more with innodb_fast_shutdown because I used to restart mysql and stop mysql to do this. Now, this one-step is vital because every transaction uncommitted may have other moving parts within and outside of the InnoDB Transaction Logs (See InnoDB Infrastructure).

Please note that setting innodb_fast_shutdown to 2 would clean the logs out as well but more moving parts still exist and gets picked on Crash Recovery during mysqld's startup. Setting of 0 is best.

Mysql InnoDB optimisation

Are your InnoDB Log Files (ib_logfile0 and ib_logfile1) sized correctly ?

They should be 25% of the InnoDB Buffer Pool
In your case, that would be 224M

You can it them as follows:

Add this setting to /etc/my.cnf : innodb_log_file_size=224M
service mysql stop
rm -f /var/lib/mysql/ib_logfile[01]
service mysql start (ib_logfile0 and ib_logfile1 are recreated)

You should keep the default for innodb_flush_method to insure ACID compliance. Otherwise, you could lose up to one second's worth of transactions.

CAVEAT: If your DB server is dedicated to only to the database, you should reserve 75-80% or RAM for the innodb_buffer_pool.

UPDATE

You will definitely want to change your InnoDB layout !!!!

You need to raise innodb_open_files to 6000
You need to turn on innodb_file_per_table
You need to restructure all InnoDB Data to reside in separate tablespaces

Best Answer

Related Solutions

MySQL InnoDB – Cons of innodb_file_per_table

UPDATE 2013-07-02 15:08 EDT

Mysql InnoDB optimisation

Related Topic