Thread: ERROR: transaction is read-only
Hello all,
I am facing a serious issue in my app that uses jdbc and I am not
getting satisfactory answers from the posts either. I have set the conn
auto-commit to false but even after that I am getting the error : ERROR
: transaction is read-only - while executing a batch of 2 statements.
Statement1:
Smt.addBatch("DELETE from table1 where coln1 < 20");
Statement2:
Smt.addBatch("INSERT into table2 (SELECT coln1, coln2 from table3)");
Pls let me know how to resolve this.
Thanks in advance,
-Satish
Your transaction is probably being set to read-only somewhere. I'm going out on a limb and venture that that does not have anything to do with the code you've showed us. I don't think the driver sets a connection to read-only on its own anywhere (only through explicit user caslls to setReadOnly--or, of course, manually issuing a query such as "SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY" or just "SET TRANSACTION READ ONLY"). Are you using RO connections anywhere else? Could this be improper reuse of a read-only connection from a pool? Can you turn this into an isolated test case or at least provide more details on what works and what doesn't. --- Maciek Sakrejda | System Architect | Truviso 1065 E. Hillsdale Blvd., Suite 215 Foster City, CA 94404 (650) 242-3500 Main msakrejda@truviso.com www.truviso.com
On Mon, Jan 17, 2011 at 2:12 PM, Maciek Sakrejda <msakrejda@truviso.com> wrote:
Your transaction is probably being set to read-only somewhere. I'm
going out on a limb and venture that that does not have anything to do
with the code you've showed us. I don't think the driver sets a
connection to read-only on its own anywhere (only through explicit
user caslls to setReadOnly--or, of course, manually issuing a query
such as "SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY" or just
"SET TRANSACTION READ ONLY"). Are you using RO connections anywhere
else? Could this be improper reuse of a read-only connection from a
pool? Can you turn this into an isolated test case or at least provide
more details on what works and what doesn't.
For example, if you are using the spring framework and have <tx:annotation-driven/> in your context and @Transactional annotations on classes, I believe the default is to create a read-only transaction. You must use @Transactional(readOnly=false) to get a read/write transaction. Or maybe that's just how my metadata is set up. It is possible that you can define the default transaction type in the transaction manager declaration. It's been a while since I dug into that stuff. You can put an @Transactional annotation on the class declaration in order to provide defaults and then override those defaults with annotations on individual methods. If you declare your transaction aspects in an application context file, you can use a naming convention like read-only on methods like get* and read-write on methods like set* or assign*. I imagine that most frameworks that abstract transaction handling away from the coder provide similar mechanisms for defining transaction semantics declaratively.
--sam