| Bug #12080 | InnoDB asserts if INSERTs exhaust the lock table | ||
|---|---|---|---|
| Submitted: | 21 Jul 2005 12:30 | Modified: | 5 Sep 2005 13:38 |
| Reporter: | Disha | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 5.0.11 BK | OS: | Any (All) |
| Assigned to: | Osku Salerma | CPU Architecture: | Any |
[22 Jul 2005 0:10]
Heikki Tuuri
Hi!
The reason probably is that the INSERT causes gap locks to be inherited, and increases the size of the lock table.
The UPDATE probably does return the error DB_LOCK_TABLE_FULL.
Hmm... we could block new inserts if:
if (UT_LIST_GET_LEN(trx->trx_locks) > 10000) {
if (buf_LRU_buf_pool_running_out()) {
return(DB_LOCK_TABLE_FULL);
}
}
is true BEFORE the insert. It does not help to check it after the insert.
This still leaves several cases where InnoDB does assert when it runs out of lock table. It is hard to fix them all. In general, InnoDB does assert if it runs out of memory. At least, InnoDB does print very detailed diagnostics to the .err file. Also, in a properly tuned InnoDB installation, running out of lock table is extremely rare. An 8 MB buffer pool is not proper tuning.
But we could fix that INSERT case. I am assigning this bug to Marko Mäkelä. I guess it is enough to fix this in 5.0.
Regards,
Heikki
[5 Sep 2005 13:38]
Osku Salerma
I did what Heikki suggests above:
===== innobase/row/row0ins.c 1.69 vs edited =====
--- 1.69/innobase/row/row0ins.c 2005-08-12 11:29:56 +03:00
+++ edited/innobase/row/row0ins.c 2005-09-05 16:06:42 +03:00
@@ -2412,6 +2412,19 @@
/* DO THE CHECKS OF THE CONSISTENCY CONSTRAINTS HERE */
+ if ((UT_LIST_GET_LEN(trx->trx_locks) > 10000)
+ && buf_LRU_buf_pool_running_out()) {
+ err = DB_LOCK_TABLE_FULL;
+
+ goto error_handling;
+ }
+
err = row_ins(node, thr);
but it doesn't work, the server still crashes with buffer pool running out. First I thought something was wrong with the patch, but further investigation revealed that after the above code had triggered, SHOW INNODB STATUS was showing this:
------------
TRANSACTIONS
------------
Trx id counter 0 7431
Purge done for trx's n:o < 0 7429 undo n:o < 0 0
History list length 4
Total number of lock structs in row lock hash table 52920
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 30005, OS thread id 1170914224
MySQL thread id 2, query id 6320505 localhost root
show innodb status
---TRANSACTION 0 7430, ACTIVE 398 sec, process no 30005, OS thread id 1170717616 inserting
mysql tables in use 1, locked 1
ROLLING BACK 52921 lock struct(s), heap size 3616064, undo log entries 3152292
MySQL thread id 1, query id 6320504 localhost root update
insert into t1 values (1,'satyen')
This went on for a good while before the server crashed with out-of-memory. My interpretation of this is that rolling back the aborted transaction also needs to allocate memory from the buffer pool, so detecting if the buffer pool is running low in INSERT operations doesn't help since if we have lots of statements to roll back we'll still fail.
You could make the "are we low on memory" check more conservative, but it doesn't make much sense to fail to insert stuff if we have 30% of the buffer pool still available...
Also, triggering this error even with a 4MB buffer pool takes over 2.5 million inserts in one transaction. I doubt anyone is going to have a problem with this in practise, so I'm closing this bug since it doesn't seem fixable.
Description: The MySQL daemon crashes unexpectedly when the memory gets filled up by a transaction. For this repro, 3 procedures are used: 1) sp0: This is the calling procedure. It starts a transaction and calls sp1() and sp2(). 2) sp1: sp1 updates a record in a transactional table appropriately. 3) sp2: sp2 fills up the memory by executing dml statement in a endless loop. If run on its own, it does not crash the server, but terminates with proper error message. How to repeat: 1. Create a table tb1 as follows:- create table tb1(f1 int, f2 varchar(10)) engine=innodb// insert into tb1 values (1,'vinay')// 2. Create 3 procedures sp0,sp1 and sp2 as follows:- create procedure sp1 () Begin update tb1 set f2 = 'anonymous' where f2 = 'vinay'; End// create procedure sp2 () Begin while 1 do insert into tb1 values (1,'satyen'); end while; End// create procedure sp0 () Begin Start Transaction; Call sp1(); Call sp2(); Commit; End// 3. Before calling the procedure sp0, set the AUTOCOMMIT to false by executing:- set AUTOCOMMIT = 0// 4. Call the procedure sp0 as:- Call sp0() // Expected Results: The procedure should execute and throw an error indicating low memory / out of memory. Actual Results: The procedure executes and when it gets out of free memory space, it crashes the MySQL daemon. Attachments: error.jpeg, error1.jpeg Note: The Rollback operation takes place successfully. However, when the daemon is restarted, and the rolled back table is retrieved, it takes a long time to display a single record.