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