MySQL Forums
Forum List  »  InnoDB

Avoiding table locks
Posted by: Michael Robinson
Date: October 14, 2022 03:50AM

Simple description: I'm doing a big delete and want to avoid locking the table for other users reading the table.

More complex description: I have a large(ish) table with 200K/day recs going back years. I'm trying to clean out the old data without causing locks on people reading the more recent data. The table is InnoDB and is indexed on date etc. The table is used 24/7 and I can't just use one large delete as A) I don't think the server would cope, B) the locks would kill the application and C) the delete is slightly more complex as I intend to keep one representative record per day.

Locking: I'm currently deleting a day at a time (about 0.3% of data) which takes 20 secs. I understood with InnoDB that it would be record locking but the command "show open tables where In_use > 0" shows the table with in_use sometimes at 1 and sometimes at 2. Does this mean it's tale locking (bad) or just using record locking (good)? When does mysql InnoDB switch to a table lock? I'm only concerned about locking the table from other users reading it.

Other approach: I could copy the table and do all my operations on the new table before renaming it to the old table name. I'm not sure of what the risks are here - we don't use stored procs or triggers on the table though we do rely on auto increment ids. Any opinions on this approach?

Is there any way to format these posts?

Options: ReplyQuote


Subject
Views
Written By
Posted
Avoiding table locks
89
October 14, 2022 03:50AM
42
October 20, 2022 03:01AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.