0

I am about to delete large amount of data from a database. I want to know if deletion of large number of records from table in MySQL or PostgreSQL, lock a table ?

Let's say my table has been adding 2 GB data per day since 2016, and I want to delete all records older than 6 months from now.

Would my table get locked and all my insert or update or delete queries fail during the deletion time ?

2

I don't know about MySQL but Postgres never locks the whole table when running DML statements - regardless of the number of affected rows.

Additionally writers never block readers, so even if you update all rows in a table, other transactions will still be able to SELECT from that table - obviously seeing the old values until your DML statement is committed.

Deleting rows from the table will also not block concurrent inserts (as long as there are no primary key conflicts). And inserting rows won't block deleting existing rows.

Again: the above is true for Postgres. I don't use MySQL, so I can't comment on that.

1

As for MySQL - it depends on the engine used ...

https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html

For locking reads (SELECT with FOR UPDATE or FOR SHARE), UPDATE, and DELETE statements, the locks that are taken depend on whether the statement uses a unique index with a unique search condition, or a range-type search condition.

For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.

For other search conditions, and for non-unique indexes, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range. For information about gap locks and next-key locks, see Section 15.7.1, “InnoDB Locking”.

DELETE FROM ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

for myISAM engline -> https://stackoverflow.com/questions/1951161/mysql-myisam-table-locking

.. delete sets a write lock to the whole table ...

So InnoDB has big advantages for your scenario

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.