CODEWITHSUNDEEP
mysqlsqlsql-delete
Haseeb
Haseeb

Reputation: 2234

Error : Lock wait timeout exceeded; try restarting transaction / Limit Delete Query with join

I have the table having duplicate entries against column "image_url" now i want to remove only duplicates and keep just one record of each?

here is my query i tried :

DELETE t1
FROM
    ad_images_copy t1
JOIN ad_images_copy t2 ON t2.image_url = t1.image_url
AND t2.id < t1.id

reference link for remove duplicate rows but it gave me the error

Error : Lock wait timeout exceeded; try restarting transaction

now i want to Limit this query because i have to much records.

i tried by adding limit in last but it not work for me .

DELETE t1
    FROM
        ad_images_copy t1
    JOIN ad_images_copy t2 ON t2.image_url = t1.image_url
    AND t2.id < t1.id
limit 100 ;

help if any other way to do this.

Upvotes: 2

Views: 1253

Answers (1)

chiastic-security
chiastic-security

Reputation: 20520

One thing to check is that image_url and id are indexed on both tables. (Probably id is the primary key, so it'll be indexed, but you should check that image_url is indexed.) If not, add indexes.

If so, you might try avoiding the locking by doing it in two steps. Are your rows immutable? If they are, then you can use a SELECT query to find all the rows you want to delete, and then use some batched DELETEs to get rid of them. This means that you won't end up locking the whole table.

It's safe to do it this way as long as you know that the rows you're looking at aren't going to change, or get removed from t2, between the SELECT and the DELETE.

Upvotes: 1

Related Questions