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.
I asked same question on Ask Tom as well and graciously Ask Tom has confirmed my understanding - in short, 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.
Here is the link of AT answer: https://asktom.oracle.com/pls/apex/asktom.search?tag=oracle-which-lock-is-acquired-by-serializable-isolation-level