Will Oracle lock the whole table while performing a DML statement or just the row

Question

When i try to insert/update something in a db table, will Oracle lock the whole table or only the row being inserted/updated?

Is this something that can be controlled through external configuration?

Answer 1

We can issue locks explicitly with the LOCK TABLE command. Find out more

Otherwise, an insert does not lock any other rows. Because of Oracle's read isolation model that row only exists in our session until we commit it, so nobody else can do anything with it. Find out more.

An update statement only locks the affected rows. Unless we have implemented a pessimistic locking strategy with SELECT ... FOR UPDATE. Find out more.

Finally, in Oracle writers do not block readers. So even locked rows can be read by other sessions, they just can't be changed. Find out more.

This behaviour is baked into the Oracle kernel, and is not configurable.

---

Justin makes a good point about the table-level DDL lock. That lock will cause a session executing DDL on the table to wait until the DML session commits, unless the DDL is something like CREATE INDEX in which case it will fail immediately with ORA-00054.

Answer 2

It depends what you mean by "lock".

For 99.9% of what people are likely to care about, Oracle will acquire a row-level lock when a row is modified. The row-level lock still allows readers to read the row (because of multi-version read consistency, writers never block readers and readers never do dirty reads).

If you poke around v$lock, you'll see that updating a row also takes out a lock on the table. But that lock only prevents another session from doing DDL on the table. Since you'd virtually never want to do DDL on an active table in the first place, that generally isn't something that would actually cause another session to wait for the lock.