0

I am using a MAMP server in my office where I have a table which stores around 7.5 lakh (750 000) records. On uploading a file, if the data already in present, we don't throw an error because of any business logic just we delete those records in that time range of the file and re-insert the data from the file. But while deleting, lock timeout exception is thrown while debugging and the file fails to upload. How can I prevent this timeout? Can lock timeout be used in delete query which we keep in a properties file?

Query: 

 DELETE * FROM TABLE_NAME WHERE DATE >= (STARTDATE) AND DATE <= (EndDate).

For say, at least 10k records have to be deleted which can fall in these date range. Above this query gives lock timeout exception.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Abhi
  • 41
  • 2
  • 10

1 Answers1

1

It can be very time-consuming to perform a bulk operation like yours on a large table. Why?

  1. The server may struggle to find the correct rows.
  2. Single queries (in InnoDB) have transaction semantics. That is, the server must be able to roll back the entire operation if any part of it fails. That means the server must store enough data to restore all deleted rows until the deletion operation completes. Doing that makes the server work hard.
  3. Other parts of your application may be accessing the same table simultaneously. If so, they wait their turn and your system blocks.

How to fix this.

  1. Make sure you have an index on your column called DATE so the server can find the correct rows efficiently.
  2. If other parts of your application query the database, put this statement right before the SELECT statements. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;. That tells the query it can go ahead even if it might get a soon-to-be DELETEd row.
  3. Best alternative : Do the delete in batches. Do this:
DELETE * FROM TABLE_NAME WHERE DATE >= (STARTDATE) AND DATE <= (EndDate) LIMIT 1000;

And repeat the delete operation until it processes zero rows. That uses mutiple transactions while keeping each one at a reasonable size so the server does not get choked by them.

This sort of thing can be done most simply in java with a loop like this pseudocode.

 bool done = false;
 while (!done) {
    int rowcount =  execute.Update("Delete Query with LIMIT clause");
    if (rowcount <= 0) done = true;
 }
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • @OJones Thanks for the help. Can I loop through the size of data to be deleted and then delete the data while in a loop. My code is : first i hit the table and get the size of the data in that range. Then i perform this code. int count = list.size(); for(int i=1;i<((count/1000)+1);i++ { int delete = execute.Update("Delete Query")};. Will it not cause any pitfalls. – Abhi Nov 15 '18 at 16:49
  • You can certainly use a loop like you mention. But you're making it more complex than it needs to be. See my edit. – O. Jones Nov 16 '18 at 13:20
  • Thank you so much for the help. Need one more help. If I have a list of 5k records, how do I perform batch insertion to increase the insertion speed and load on sql? I am using Entity manager in hibernate for inserting in sql DB. – Abhi Nov 16 '18 at 14:12
  • Currently i insert normally, which takes a while to insert that many records. i have written the code like this : for(Employee object : EmployeeList) { em.persist(object); }. Can u tell me a faster approach than this. – Abhi Nov 16 '18 at 14:14
  • There's a lot of material on how to insert many rows efficiently using Java, JDBC and MySQL. https://stackoverflow.com/questions/4355046/java-insert-multiple-rows-into-mysql-with-preparedstatement is one example. Or you can write multirow insert statements. https://stackoverflow.com/questions/34447305/innodb-bulk-insert-using-transaction-or-combine-multiple-queries Or you can use `LOAD DATA INFILE`. – O. Jones Nov 16 '18 at 18:07