mysql - InnoDB lock exhaustion with batched transactional DELETEs under DBI -

I am using Pearl and DBI, which works on removal of a large ISSL table in 1000 parts. But I'm getting this error: DBD :: mysql :: db failed: Total number of lock is greater than lock table size .

My $ q = q {Date deleted on table date_format (Date, '% Y-% m') & Gt; '2015-01' Border 1000}; My $ rc = ''; Until ($ rc eq '0E0') {$ rc = $ dbh- & gt; Tax ($ q); $ Dbh- & gt; Committed (); }

This error occurred in my experience only when trying to delete or insert a large number of records at a time with a statement, in fact, I am able to:

1. Extend the indoor buffer pool size by using the innodb_buffer_pool_size global variable.

2. Delete in chunks.

I have not resolved two reasons. The first thing is that in my specific situation this buffer extends time even before filling it in the end, although I am not sure about it, and second because we are not sure what the effect can be on the use of the database. / P>

I would like to know:

* Even though I am deleting in divisions, why is this error occurring?

* Is it a quick high level solution to problem with PRL and / or DBI?

* Any other information that can be a Southern.

Even if I am deleting in divisions, even though this error is happening?

Uses Indobi:

14.5.8 Setting by various SQL statements in LockOn InnoDB

A locking Reads, sets a record lock on a UPDATE , or daylight usually ; Scan in processing SQL statement. It does not matter whether the WHERE condi statement states that the line will be excluded from InnoDB does not miss the exact WHERE condition, but Only knows which index categories were scanned. Lock are normally the next-key locks that are included in "differences" just before the record.

[...]

Remove from ... Where

(emphasis)

This means that your query will be locked every line she scans, even lines that do not match the condition in your WHERE section.

I do not know the exact execution details of your query, but I think with a large table, this is innodb_buffer_pool_size (which I believe is shared by all sessions ; Other sessions may have to lock the rows at the same time as your query) It will not be difficult to eliminate Especially if your query does not use the index and does not trigger a table scan.

Is there a quick high level solution to this problem?

Describes a simple solution for this situation:

If you are removing many rows from a large table, you lock for an InnoDB table The table can be more than the size. To avoid this problem, or to reduce the time that the table is locked, the following strategy (which does not use DELETE ) can be useful:

< Ol>

Select the rows to be deleted in the empty table, with the same structure as the original table:

  select the TCP * From where ...;  

2. Indirectly, to move the original table out of the way and change the name of the copy on the original name of the empty table Use: Li>

  to t_old the table again, t_copy to t; Drop the original table:   
  
  drop table t_old;  
 
 
  

can enter other session sessions, while RENAME TABLE implementation, hence rename operation is not subject to concurrency problems. Section 13.1.20, "See empty table syntax".