2

Q1: Does a delete query run as a whole transaction in mysql/mariadb. So if we have 1 million rows to delete, will mysql/mqriadb server delete it as a whole or if after deleting some rows something goes wrong deleted rows will get restored.

Q2: Should we need to delete rows in a single delete query (irrespective of how many rows need to deleted) or should we need to place a limit in delete query and perform delete in batches. If batching is required at what number (no. of rows to deleted) should we think of it.

pvjhs
  • 549
  • 1
  • 9
  • 24
  • Your first question is a duplicate: https://stackoverflow.com/questions/17856370/transaction-necessary-for-single-update-query – Tim Biegeleisen Apr 01 '18 at 08:50
  • For your second question, it really depends on how many records you plan on deleting. Do you really think you will have a frequent need to delete millions of records? – Tim Biegeleisen Apr 01 '18 at 08:51
  • we need to purge approx 1 lakh to 1 million of records every day. – pvjhs Apr 01 '18 at 09:04
  • About your second question. The MySQL Documentation states: If you are deleting many rows from a large table, you may exceed the lock table size for an InnoDB table. They suggest to use a temporary table in this case. There is a similar Question on stack overflow https://stackoverflow.com/questions/19448868/delete-huge-amounts-of-data-from-huge-table and a [blog article](http://www.rathishkumar.in/2017/12/deleting-huge-number-of-records-in-mysql.html) by Rashid Kumar : Deleting huge number of records in MySQL – andih Apr 01 '18 at 10:04

1 Answers1

5

In InnoDB, a DELETE of any size is transactional. Deleting a million will be slow, mostly because of the need to prepare for a possible ROLLBACK.

I cover the rest of the questions in http://mysql.rjweb.org/doc.php/deletebig , including advice on what to do.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Based on your article's chunk strategy i developed a procedure and deleting 1000 rows inside a loop, i observed `The total number of locks exceeds the lock table size`. My `innodb_buffer_pool_size=128M` and `autocommit=1`. How is this possible? Is there any way that locks are released after each query? Or is it anything related to the loop inside which the delete is being run? – ismathullah Nov 15 '19 at 05:33
  • @ismathullah - Ouch. How much RAM do you have? How old is your MySQL? 128M is an old default. Decrease the 1000 to, say, 100 until you work out the optimal buffer_pool_size. – Rick James Nov 15 '19 at 18:17
  • I am using MariaDB-10.0.35, MySQL 5.6. on an 8C 16GB server. Decreasing may work, but i m delaing with deletion of 50+ million records. Once one dele query is done within an iteration, that lock is to be released right. How does that work? Is there anything special about deletions within a loop? – ismathullah Nov 25 '19 at 04:54
  • @ismathullah - Depends on the setting of `autocommit` and whether you used `BEGIN`. – Rick James Nov 25 '19 at 05:10
  • `autocommit=1` and i have used `BEGIN` to start the procedure definition, i believe that wont put everything into a transaction. Will test with seperate transaction block for each iterations. Will publish result if it helps. – ismathullah Nov 25 '19 at 07:04
  • @ismathullah - `BEGIN` cancels autocommit=1. The transaction will end with an explicit `COMMIT`. – Rick James Nov 25 '19 at 16:11