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
ROLLBACKif 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
interactive_timeoutmid-transaction, is the transaction rolled back? Is there a way to test this from the console?
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.