0

I have a long section of code that I am executing inside a Spring transaction. I now want just a small subset of that code to execute with a different isolation level. If I call:

transactionTemplate.setIsolationLevel(Isolation.SERIALIZABLE.value());

Right before this small subset will this achieve my goal or must I set the isolation level and then create a new transaction?

Mark
  • 75
  • 10

1 Answers1

2

Well, I know that in SQL*Plus, setting an isolation level implies starting a transaction:

-bash-4.1$ sqlplus mbobak/mbobak

SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 19 20:26:18 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select taddr from v$session where sid in(select sid from v$mystat where rownum=1);

TADDR
----------------


SQL> set transaction isolation level serializable;

Transaction set.

SQL> select taddr from v$session where sid in(select sid from v$mystat where rownum=1);

TADDR
----------------
0000007F510EFD60

SQL>

I assume the same would apply for your Spring API.

Just thought of something else. Also, note that, in fact, the 'set transaction' must be the first statement in a transaction. You cannot change the isolation level of a transaction, once it has begun. Any attempt to do so, will result in:

ORA-01453: SET TRANSACTION must be first statement of transaction
Mark J. Bobak
  • 13,720
  • 6
  • 39
  • 67
  • I think the fact that you can only set the isolation level as the first statement means setIsolationLevel will not work on the fly as it would, otherwise, have to create a new transaction the boundaries of which would be unclear. – Mark Dec 28 '13 at 11:14