Re: SET TRANSACTION must be first statement of transaction -- the real cause
Hi Raphael, can you please open a scarab Bug report on this ? http://issues.apache.org/scarab/issues Just to make sure that it is not forgotten. (I'd like to look into this, but I do not have the time at the moment) Thanks, Thomas [EMAIL PROTECTED] schrieb am 24.01.2005 17:40:43: Thre real cause of the problem turned out to be something silly that I had done. I had done a save() on a newly created object and then also done a doInsert(), afterthe PK had already been filled in. The doInsert() did NOT throw an exception or return any sort of an error. The Oracle error manifested itself on a subsequent operation, I'm not sure whether the very next one but certainly one very shortly after. So the answer turns out to be sloppy programming on my part exacerbated by poor diagnostics from the library code. |-+ | | Thomas Fischer | | | [EMAIL PROTECTED]| | | u.net | | || | || | | 21-Jan-2005 13:45| | | Please respond to| | | Apache Torque | | | Users List | | | [EMAIL PROTECTED]| | | pache.org | | || |-+ - | | | | | | | | To: Apache Torque Users List [EMAIL PROTECTED] apache.org | | | | cc: | | Subject: Re: SET TRANSACTION must be first statement of transaction | - | Hi, if you find out anything about the error please post it here. I remember this problem was reported before to the users list but I do not remember that the problem was solved. You can try to log the SQL statements via P6Spy: http://wiki.apache.org/db- torque/FrequentlyAskedQuestions#head-4658ec85746ef90e0d810fb85fd135d9dd30e264 I have never done this myself, though, so no idea whether this can help you. Thomas [EMAIL PROTECTED] schrieb am 21.01.2005 10:49:48: I have just recently started getting the error 'SET TRANSACTION must be first statement of transaction' from my application. The error occurs on Torque calls that are not using transactions. Can anyone suggest possible causes? The application was working fine, so I presume that some change that I made caused this. I am running Torque 3.1 with Oracle 9 on Solaris and Linux. The base installation has not changed for some months. I have checked that all transactions are properly committed/rolled back; tried changing 'testOnBorrow' from 'true' to 'false'. Are there any more usual suspects that I ought to investigate? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: SET TRANSACTION must be first statement of transaction
We are on Oracle 9.2.0/Linux. I am using oracle.jdbc.dirver.OracleDriver from ojdbc14.jar as the driver library wiith the 'thin' connection, and have been running without problems, at least none that were not of my own making, for close on a year. classes12.jar does not work at all for me. The pool is SharedPoolDataSource. I quite agree that this is almost certainly an Oracle rather than a Torque problem. However the references I dug out suggest that there are work-arounds one can put into the Torque code that would avoid at least some instances of this problem. It is worth looking at the Torque code to see whether those work-arounds are actually in place. In view of the length of time that my project has been running, my first thought was, of course, that *I* had done something silly, but I really cannot see what I might have done to cause this to happen. |-+ | | Thomas Fischer | | | [EMAIL PROTECTED]| | | u.net | | || | || | | 21-Jan-2005 14:42| | | Please respond to| | | Apache Torque | | | Users List | | | [EMAIL PROTECTED]| | | pache.org | | || |-+ -| | | | | | | | To: Apache Torque Users List torque-user@db.apache.org | | | | cc: | | Subject: Re: SET TRANSACTION must be first statement of transaction | -| raphael, which oracle version / driver / connection pool are you using ? My first guess would be that this is a problem of the connection pool rather than of torque. Thomas [EMAIL PROTECTED] schrieb am 21.01.2005 15:20:09: Searching the web, I have found the following *possible* causes. Which one may be the actual cause of my problem I don't yet know. The problem is actually manifested deep in the Torque library classes, which I cannot see. Maybe some of the maintainers can check wether any of these possible causes are actually present in the Torque base code. ORA-01453 SET TRANSACTION must be first in transaction 1.After diagnosing the code I found in one place after getting the connection we change the transaction type and before closing the connection we are setting it back to original transaction type. After commenting that code I never got that error. 2.I was able to fix the database problem by changing the datasource configuration from serializable to read committed. 3.In the connection pool setting in Admin server under the section Transaction Isolation deselect the option Guarantee Isolation Level .This should solve the problem. 4.Oracle works fine (except for a certain quirk when checking existing = tables) as long as no tx isolation is set. [snip] Oracle seems to do best with only one or two of = the possible isolation levels anyway. From: Gavin King Subject: Re: [Hibernate] HELP: Hibernate and Oracle Date: Fri, 22 Nov 2002 15:41:22 +0100 (CET) I've never seen this problem before, but it looks like Oracle doesn't like the idea of calling Connection.setTransactionIsolation() after callong Connection.setAutocommit(false) This is not a problem for other platforms, but I will swap the order anyway.. The entire message can be viewed here: http://www.mail-archive.com/[EMAIL PROTECTED] net/msg00827.html 5. I saw -- http://edocs.bea.com/wls/docs70/jta/thirdpartytx.html WHICH STATES --- Description Oracle Bug Comments/Workarounds for WebLogic Server ORA-01453 - SET TRANSACTION must be first statement of transaction When using the Oracle Thin/XA 8.1.7 driver, you cannot change the transaction isolation level
Re: SET TRANSACTION must be first statement of transaction
Hi, [EMAIL PROTECTED] schrieb am 24.01.2005 10:40:01: We are on Oracle 9.2.0/Linux. I am using oracle.jdbc.dirver.OracleDriver from ojdbc14.jar as the driver library wiith the 'thin' connection, and have been running without problems, at least none that were not of my own making, for close on a year. classes12.jar does not work at all for me. The pool is SharedPoolDataSource. Thats exactly what I am using myself, and I did not run in any errors so far. However, this does not mean this will not happen in the future, so I am very interested in getting this fixed. Have you already tried to use P6Spy to log your database statements ? I have never done it myself, no idea how well it is working and whether it also logs the changes in transaction isolation, but if it does, it would help very much to pinpoint the error. I did not find any references to Connection.setTransactionIsolation() in Torque code. This means that with a probability of 99.9%, the error does not lie within Torque. Perhaps someone at db-commons can help you (it is their pool, after all). If you need a really quick solution, you can a) use another pool (perhaps you use an application server which is providing one) b) use read committed instead of serializable as transaction isolation, if this is acceptable for you. More comments below. Please tell us your advances in solving this problem, Thomas I quite agree that this is almost certainly an Oracle rather than a Torque problem. However the references I dug out suggest that there are work-arounds one can put into the Torque code that would avoid at least some instances of this problem. It is worth looking at the Torque code to see whether those work-arounds are actually in place. ... [EMAIL PROTECTED] schrieb am 21.01.2005 15:20:09: Searching the web, I have found the following *possible* causes. Which one may be the actual cause of my problem I don't yet know. The problem is actually manifested deep in the Torque library classes, which I cannot see. Maybe some of the maintainers can check wether any of these possible causes are actually present in the Torque base code. ORA-01453 SET TRANSACTION must be first in transaction 1.After diagnosing the code I found in one place after getting the connection we change the transaction type and before closing the connection we are setting it back to original transaction type. After commenting that code I never got that error. Well, this one is certainly in the pool code, not in the Torque code. I have searched for references to Connection.setTransactionIsolation in Torque source code and found none. 2.I was able to fix the database problem by changing the datasource configuration from serializable to read committed. This can solve a lot of problems, but is sometimes not what one wants. The reason why this might solve problems is that oracle returns the connection with transaction isolation READ_COMMITTED as default, so there is no need to change the transaction isolation. 3.In the connection pool setting in Admin server under the section Transaction Isolation deselect the option Guarantee Isolation Level .This should solve the problem. This is only a solution for a certain connection pool whis is provided by an application server. It will not work with SharedPoolDatasource 4.Oracle works fine (except for a certain quirk when checking existing = tables) as long as no tx isolation is set. [snip] Oracle seems to do best with only one or two of = the possible isolation levels anyway. From: Gavin King Subject: Re: [Hibernate] HELP: Hibernate and Oracle Date: Fri, 22 Nov 2002 15:41:22 +0100 (CET) I've never seen this problem before, but it looks like Oracle doesn't like the idea of calling Connection.setTransactionIsolation() after callong Connection.setAutocommit(false) This is not a problem for other platforms, but I will swap the order anyway.. The entire message can be viewed here: http://www.mail-archive.com/[EMAIL PROTECTED] net/msg00827.html This might be a reason, though I do this in my code myself. Again, this is done in the pool and not in Torque 5. I saw -- http://edocs.bea.com/wls/docs70/jta/thirdpartytx.html WHICH STATES --- Description Oracle Bug Comments/Workarounds for WebLogic Server ORA-01453 - SET TRANSACTION must be first statement of transaction When using the Oracle Thin/XA 8.1.7 driver, you cannot change the transaction isolation level for a transaction. Transactions use the default transaction isolation as set for the database. You use 9.2, not 8.1.7, so this does not apply. - To
Re: SET TRANSACTION must be first statement of transaction -- the real cause
Thre real cause of the problem turned out to be something silly that I had done. I had done a save() on a newly created object and then also done a doInsert(), afterthe PK had already been filled in. The doInsert() did NOT throw an exception or return any sort of an error. The Oracle error manifested itself on a subsequent operation, I'm not sure whether the very next one but certainly one very shortly after. So the answer turns out to be sloppy programming on my part exacerbated by poor diagnostics from the library code. |-+ | | Thomas Fischer | | | [EMAIL PROTECTED]| | | u.net | | || | || | | 21-Jan-2005 13:45| | | Please respond to| | | Apache Torque | | | Users List | | | [EMAIL PROTECTED]| | | pache.org | | || |-+ -| | | | | | | | To: Apache Torque Users List torque-user@db.apache.org | | | | cc: | | Subject: Re: SET TRANSACTION must be first statement of transaction | -| Hi, if you find out anything about the error please post it here. I remember this problem was reported before to the users list but I do not remember that the problem was solved. You can try to log the SQL statements via P6Spy: http://wiki.apache.org/db-torque/FrequentlyAskedQuestions#head-4658ec85746ef90e0d810fb85fd135d9dd30e264 I have never done this myself, though, so no idea whether this can help you. Thomas [EMAIL PROTECTED] schrieb am 21.01.2005 10:49:48: I have just recently started getting the error 'SET TRANSACTION must be first statement of transaction' from my application. The error occurs on Torque calls that are not using transactions. Can anyone suggest possible causes? The application was working fine, so I presume that some change that I made caused this. I am running Torque 3.1 with Oracle 9 on Solaris and Linux. The base installation has not changed for some months. I have checked that all transactions are properly committed/rolled back; tried changing 'testOnBorrow' from 'true' to 'false'. Are there any more usual suspects that I ought to investigate? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: SET TRANSACTION must be first statement of transaction
Hi, if you find out anything about the error please post it here. I remember this problem was reported before to the users list but I do not remember that the problem was solved. You can try to log the SQL statements via P6Spy: http://wiki.apache.org/db-torque/FrequentlyAskedQuestions#head-4658ec85746ef90e0d810fb85fd135d9dd30e264 I have never done this myself, though, so no idea whether this can help you. Thomas [EMAIL PROTECTED] schrieb am 21.01.2005 10:49:48: I have just recently started getting the error 'SET TRANSACTION must be first statement of transaction' from my application. The error occurs on Torque calls that are not using transactions. Can anyone suggest possible causes? The application was working fine, so I presume that some change that I made caused this. I am running Torque 3.1 with Oracle 9 on Solaris and Linux. The base installation has not changed for some months. I have checked that all transactions are properly committed/rolled back; tried changing 'testOnBorrow' from 'true' to 'false'. Are there any more usual suspects that I ought to investigate? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: SET TRANSACTION must be first statement of transaction
raphael, which oracle version / driver / connection pool are you using ? My first guess would be that this is a problem of the connection pool rather than of torque. Thomas [EMAIL PROTECTED] schrieb am 21.01.2005 15:20:09: Searching the web, I have found the following *possible* causes. Which one may be the actual cause of my problem I don't yet know. The problem is actually manifested deep in the Torque library classes, which I cannot see. Maybe some of the maintainers can check wether any of these possible causes are actually present in the Torque base code. ORA-01453 SET TRANSACTION must be first in transaction 1.After diagnosing the code I found in one place after getting the connection we change the transaction type and before closing the connection we are setting it back to original transaction type. After commenting that code I never got that error. 2.I was able to fix the database problem by changing the datasource configuration from serializable to read committed. 3.In the connection pool setting in Admin server under the section Transaction Isolation deselect the option Guarantee Isolation Level .This should solve the problem. 4.Oracle works fine (except for a certain quirk when checking existing = tables) as long as no tx isolation is set. [snip] Oracle seems to do best with only one or two of = the possible isolation levels anyway. From: Gavin King Subject: Re: [Hibernate] HELP: Hibernate and Oracle Date: Fri, 22 Nov 2002 15:41:22 +0100 (CET) I've never seen this problem before, but it looks like Oracle doesn't like the idea of calling Connection.setTransactionIsolation() after callong Connection.setAutocommit(false) This is not a problem for other platforms, but I will swap the order anyway.. The entire message can be viewed here: http://www.mail-archive.com/[EMAIL PROTECTED] net/msg00827.html 5. I saw -- http://edocs.bea.com/wls/docs70/jta/thirdpartytx.html WHICH STATES --- Description Oracle Bug Comments/Workarounds for WebLogic Server ORA-01453 - SET TRANSACTION must be first statement of transaction When using the Oracle Thin/XA 8.1.7 driver, you cannot change the transaction isolation level for a transaction. Transactions use the default transaction isolation as set for the database. |-+ | | Thomas Fischer | | | [EMAIL PROTECTED]| | | u.net | | || | || | | 21-Jan-2005 13:45| | | Please respond to| | | Apache Torque | | | Users List | | | [EMAIL PROTECTED]| | | pache.org | | || |-+ - | | | | | | | | To: Apache Torque Users List [EMAIL PROTECTED] apache.org | | | | cc: | | Subject: Re: SET TRANSACTION must be first statement of transaction | - | Hi, if you find out anything about the error please post it here. I remember this problem was reported before to the users list but I do not remember that the problem was solved. You can try to log the SQL statements via P6Spy: http://wiki.apache.org/db- torque/FrequentlyAskedQuestions#head-4658ec85746ef90e0d810fb85fd135d9dd30e264 I have never done this myself, though, so no idea whether this can help you. Thomas [EMAIL PROTECTED] schrieb am 21.01.2005 10:49:48: I have just recently started getting the error 'SET TRANSACTION must be first statement of transaction' from my application. The error occurs on Torque calls that are not using transactions. Can anyone suggest possible causes? The application was working fine, so I presume that some change that I made caused this. I am running Torque 3.1 with Oracle 9 on Solaris and Linux. The base installation has not changed for some months. I have checked that all transactions are properly committed/rolled back; tried changing 'testOnBorrow' from 'true' to 'false'. Are there any more usual suspects that I ought to investigate? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED
