First page Back Continue Last page Graphics
Basic Locking Rules
Oracle acquires row-level locks for modifications only
Oracle never escalates locks to block or table level
Readers (SELECT) never lock data
Readers never block Writers or other Readers
Readers are not blocked by Writers
- How?
- See multi-versioning model in Transaction Control chapter
Writers are blocked only when another writer is updating same row
Notes:
Oracle’s locking rules are designed with non-blocking and scalability in mind.
Be default, Oracle never acquires locks on behalf of readers (SELECT statements). Therefore, a SELECT will not block an UPDATE, DELETE or INSERT operation. This behavior can be overridden by adding the FOR UPDATE clause to a SELECT statement, which causes Oracle to lock the selected rows. (Examples of the FOR UPDATE clause can be found later in this module.)
Interestingly, Oracle writers (UPDATE and DELETE) do not block readers. If a row has been updated by session 2 after another session 1’s query has begun, session 1 sees the original version of the row – committed or not. This is part of Oracle’s multi-versioning model and is explained in the next chapter.
So when does blocking occur? Be default, only when one session is trying to update the same row already updated by another session.