Always get error when I delete the same data in mysql

Question

I have a table with about 1,000,000 rows, and I need to delete the same datas, so I use following SQL

delete
from history
where hid not in
(
    select hid from
    (
        select min(hid) as hid
        from history
        group by `invitedkey`, `userkey`, `charge`
    ) as b
);

Here hid is Primary, AUTO_INCREMENT, if the invitedkey, userkey, charge are the same, keep one data with minimum hid. but I run above SQL, I always get error

Lock wait timeout exceeded; try restarting transaction

And I googled, but there's no good answer, is there any ideas except increasing hardware configuration.

Why not? He obviously wants to delete some duplicates and keep the one with lowest primary key. — fancyPants, Aug 24 '18 at 08:03
Your delete query is waiting for a lock which is held by another transaction. Start your query, then post the result of `show engine innodb status\G` (only the part `TRANSACTIONS`) — fancyPants, Aug 24 '18 at 08:04

starko · Answer 1 · 2018-08-24T08:16:10.380

1

In my opinion, it happens when the database is large and based on many transactions. Try to start with the following questions, though in the long run you will need to optimize the database

For example run this:

SET GLOBAL innodb_lock_wait_timeout = 4000; 
SET innodb_lock_wait_timeout = 4000;

Edit:

Very similar problem i found in THIS place

edited Aug 24 '18 at 08:16

answered Aug 24 '18 at 08:09

starko

1,150
11
26

Increasing the time to wait for a transaction which could hold the lock like forever seems like a good solution to you? – fancyPants Aug 24 '18 at 08:36
you do not know whether it will always keep the blockade, this is a temporary solution, I wrote that in the future you will need to optimize the database – starko Aug 24 '18 at 09:53

score 0 · Answer 2 · answered Aug 24 '18 at 08:13

0

I think your fifth line is unnecessary. The query in the brackets is enough to bring all the minimum bids.

answered Aug 24 '18 at 08:13

Lihi

19
6

score 0 · Answer 3 · answered Aug 24 '18 at 08:17

0

I think your fifth line is unnecessary. The query in the brackets is enough to bring all the minimum hids.

Delete from history Where hid not in (select min(hid) from...)

.

answered Aug 24 '18 at 08:17

Lihi

19
6

It is needed. Otherwise you'll get an error that you can't read and update a table at the same time. With the additional subquery a temporary table is created. And please don't post answers twice. There's an edit button, if you want to add things. – fancyPants Aug 24 '18 at 08:36

score 0 · Answer 4 · answered Aug 24 '18 at 11:09

Use join instead:

delete h
    from history h left join
         (select invitedkey, userkey, charge, min(hid) as min_hid
          from history h
          group by invitedkey, userkey, charge
         ) hh
         on hh.min_hid = hh.hid
    where hh.min_hid is null;

MySQL is not very good at optimizing update and delete queries. It is probably running the subquery once for every row, which is why you are getting a time out.

Always get error when I delete the same data in mysql

4 Answers4