Will Oracle lock the whole table while performing a DML statement or just the row
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
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.