matheusdba Staff asked 3 years ago
The original question is “when using truncate in place of delete, it locks the table and cause locking issue”?
matheusdba Staff answered 3 years ago
Lock-wise, both operations generate locks as they are changing the table data. The lock is a natural and positive mechanism in the database.
The Truncate does generate a quicker lock as this is a physical and self commitable procedure (Truncate is considered a DDL). The truncate clears all the segments underlying the table.
The Delete, however, as being a DML, opens a transaction (to be committed or rolled back) and delete the data row by row (or logically) instead of physically. As being, we can say it’s “slower” than the truncate, plus the fact of needing a commit to close the transaction makes it “slower”.
Why DELETE then? To not delete all rows (use where, for filtering the lines to be deleted) or to have the option of a rollback in a routine, for example. It follows the principle of atomicity and consistency of data.