Skip to Main Content
  • Questions
  • Oracle: Which lock is acquired by Serializable isolation level

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Himanshu.

Asked: October 09, 2022 - 3:04 pm UTC

Last updated: November 04, 2022 - 4:43 am UTC

Version: v12

Viewed 100+ times

You Asked

What I am trying to establish is whether there is any direct relation between Isolation Levels and Locks. So, let's say I started a transaction with a Serializable isolation level then will Oracle by default acquire some type of Table lock on all the tables listed in that transaction, if so then what it is?

I couldn't find any direct answer to this question, but my own understanding from reading many online docs is that there is no direct relation between Isolation Levels and Locks. Locks will be acquired based on specific SQL statement present in the transaction - and not based on whether the transaction isolation level is Read Committed or Serializable, so if there is a DML then Read Exclusive table lock (RX) will be acquired and if there is a statement like 'LOCK TABLE table IN EXCLUSIVE MODE;' then Exclusive table lock (X) will be acquired.

Please note that this question is very specific to Oracle and then very specific on which lock is acquired by Serializable isolation level. In no way I am looking for answers related to Isolation Levels and Locks available in Oracle, I have read the online docs and understand them.

and Connor said...

there is no direct relation between Isolation Levels and Locks

correct.

The locking is unchanged. We simply extend our implementation of read-consistency from the statement level back to the transaction (commencement) level.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database