CODEWITHSUNDEEP
javaspringoracle-databaseisolation-level
Mark
Mark

Reputation: 85

Changing isolation level within a Spring transaction

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?

Upvotes: 0

Views: 1807

Answers (1)

Mark J. Bobak
Mark J. Bobak

Reputation: 14433

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

Upvotes: 2

Related Questions