Обсуждение: Another exception (Transaction level)
Hi, sorry if I flood you with lots of messages, but I am quite new to Postgresql and I find a lot of questions not answered in the manual. I get randomly (probably when I have concurrent r/w access) the error message java.sql.SQLException: ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query when I do a commit(). But I had set this level at the very beginning with dbConn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); BTW, I tried to use TRANSACTION_READ_UNCOMMITTED (which is described in the manual) but in this case I get "Transaction isolation level 1 is not supported." What is wrong here? Why do I get an error message when I try to use the "read uncommitted" level, and why I get the exception when I use commit() after setting the transaction level to "read committed"? Ciao Ole
On 25/07/2003 13:09 Ole Streicher wrote: > Hi, > > sorry if I flood you with lots of messages, but I am quite new to > Postgresql and I find a lot of questions not answered in the manual. > > I get randomly (probably when I have concurrent r/w access) the error > message > > java.sql.SQLException: ERROR: SET TRANSACTION ISOLATION LEVEL must be > called before any query > > when I do a commit(). But I had set this level at the very beginning with > > dbConn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); Do you do this before or after dbConn.setAutoCommit(false). I think you need to do it before (that's what I deo any I've had a problem) as calling setAutoCommit(false) actually causes the driver to issue a BEGIN; to the back-end to start a transaction. > > BTW, I tried to use TRANSACTION_READ_UNCOMMITTED (which is described > in the manual) but in this case I get "Transaction isolation level 1 > is not supported." > > What is wrong here? Why do I get an error message when I try to use > the "read uncommitted" level, and why I get the exception when I use > commit() after setting the transaction level to "read committed"? > I don't believe that read uncommitted is supported by PostgreSQL. HTH -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
Hi Paul, Paul Thomas writes: > > sorry if I flood you with lots of messages, but I am quite new to > > Postgresql and I find a lot of questions not answered in the manual. > > I get randomly (probably when I have concurrent r/w access) the error > > message > > java.sql.SQLException: ERROR: SET TRANSACTION ISOLATION LEVEL must be > > called before any query > > when I do a commit(). But I had set this level at the very beginning with > > dbConn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); > Do you do this before or after dbConn.setAutoCommit(false). Befor. The steps I do are Connection dbConn; dbConn = java.sql.DriverManager.getConnection(...); dbConn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); dbConn.setAutoCommit(false); and then I use this instance of dbConn for querys and updates (which may occur concurrently at the same time). > > BTW, I tried to use TRANSACTION_READ_UNCOMMITTED (which is described > > in the manual) > I don't believe that read uncommitted is supported by PostgreSQL. Finally, I found out the same when searching the net. But, why the user's manual (9.2. "Transaction Isolation") states something different? I guess it should be corrected in the manual. Ciao Ole
On 25/07/2003 14:16 Ole Streicher wrote: > Hi Paul, > > Paul Thomas writes: > > > sorry if I flood you with lots of messages, but I am quite new to > > > Postgresql and I find a lot of questions not answered in the manual. > > > I get randomly (probably when I have concurrent r/w access) the > error > > > message > > > java.sql.SQLException: ERROR: SET TRANSACTION ISOLATION LEVEL must > be > > > called before any query > > > when I do a commit(). But I had set this level at the very beginning > with > > > > dbConn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); > > > Do you do this before or after dbConn.setAutoCommit(false). > > Befor. The steps I do are > > Connection dbConn; > dbConn = java.sql.DriverManager.getConnection(...); > dbConn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); > dbConn.setAutoCommit(false); > > and then I use this instance of dbConn for querys and updates (which > may occur concurrently at the same time). Concurrently? Do you mean that you a sharing one connection between several threads? If so then that may be the cause of your problems. > > > > BTW, I tried to use TRANSACTION_READ_UNCOMMITTED (which is described > > > in the manual) > > I don't believe that read uncommitted is supported by PostgreSQL. > > Finally, I found out the same when searching the net. But, why the > user's manual (9.2. "Transaction Isolation") states something > different? I guess it should be corrected in the manual. FWIW, the 7.3.3 docs only mention supporting read committed and serialized. Which version are you using? -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
Ole Streicher wrote: > > > > BTW, I tried to use TRANSACTION_READ_UNCOMMITTED (which is described > > > in the manual) > > I don't believe that read uncommitted is supported by PostgreSQL. > > Finally, I found out the same when searching the net. But, why the > user's manual (9.2. "Transaction Isolation") states something > different? I guess it should be corrected in the manual. > If I am not mistaken the SQL standard allows the implementations to use an isolations level higher than the one specified. So although the possible speed-up provided by this weaker level is not explored, the guarantees are provided by the stronger one. In other words, you must not rely on visibility provided by weaker isolation levels, they only exist to allow faster processing. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Hello Paul! Paul Thomas writes: > > Connection dbConn; > > dbConn = java.sql.DriverManager.getConnection(...); > > dbConn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); > > dbConn.setAutoCommit(false); > > and then I use this instance of dbConn for querys and updates (which > > may occur concurrently at the same time). > Concurrently? Do you mean that you a sharing one connection between > several threads? Yes. I didn't see a hint why this should be problematic. > If so then that may be the cause of your problems. How can I avoid this? Shall I establish a new connection for every asynchronious request/update I have? Aren't they expensive? > > Finally, I found out the same when searching the net. But, why the > > user's manual (9.2. "Transaction Isolation") states something > > different? I guess it should be corrected in the manual. > FWIW, the 7.3.3 docs only mention supporting read committed and > serialized. Which version are you using? 7.2.2 from SuSE, but my pdf documentation is of 7.3.2 (downloaded wednesday or so). Page 141 (a4 version): "The four transaction levels and the corresponding behavior are shown in Table 9-1". Ciao Ole
> > Concurrently? Do you mean that you a sharing one connection between > > several threads? > > Yes. I didn't see a hint why this should be problematic. You should use a connection pool: open a few connections beforehand, and each thread can then request one for it's exclusive use, and then give it back after finishing. There are lots of possible strategies/variations you could apply to the nr. of initial connections, how to grow/shrink that depending on load, what happens if there are too many connections open and there's a new connection (you can wait or throw exception), etc. Google around or search this list for "connection pool" and I'm sure you'll find a lot of hints and code examples. Cheers, Csaba.
On 25/07/2003 15:15 Ole Streicher wrote: > Hello Paul! > > Paul Thomas writes: > > > Connection dbConn; > > > dbConn = java.sql.DriverManager.getConnection(...); > > > > dbConn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); > > > dbConn.setAutoCommit(false); > > > and then I use this instance of dbConn for querys and updates (which > > > may occur concurrently at the same time). > > Concurrently? Do you mean that you a sharing one connection between > > several threads? > > Yes. I didn't see a hint why this should be problematic. You _might_ be ok if you were just doing selects (I've never tried it so I'm just guessing here) but for inserts/updates/deletes with transactions, you are going to run into problems because you commit/rollback on the connection and I suspect that the back-end will only allow one active transaction at a time. > > > If so then that may be the cause of your problems. > > How can I avoid this? Shall I establish a new connection for every > asynchronious request/update I have? Aren't they expensive? As Csaba has already suggested, use a connection pool. > > > > Finally, I found out the same when searching the net. But, why the > > > user's manual (9.2. "Transaction Isolation") states something > > > different? I guess it should be corrected in the manual. > > FWIW, the 7.3.3 docs only mention supporting read committed and > > serialized. Which version are you using? > > 7.2.2 from SuSE, but my pdf documentation is of 7.3.2 (downloaded > wednesday or so). Page 141 (a4 version): "The four transaction levels > and the corresponding behavior are shown in Table 9-1". Those are defined behaviours of the SQL spec. The sentance just below table 9-1 is the important one ;-) -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+
Hi Paul! Paul Thomas writes: > You _might_ be ok if you were just doing selects (I've never tried it so > I'm just guessing here) but for inserts/updates/deletes with transactions, > you are going to run into problems because you commit/rollback on the > connection and I suspect that the back-end will only allow one active > transaction at a time. Yes, this was the cause of the problems. When I use different connections, everything works. Thanks. > > 7.2.2 from SuSE, but my pdf documentation is of 7.3.2 (downloaded > > wednesday or so). Page 141 (a4 version): "The four transaction levels > > and the corresponding behavior are shown in Table 9-1". > Those are defined behaviours of the SQL spec. The sentance just below > table 9-1 is the important one ;-) Hmm, I find this a bit misleading. Looks like the smallprint on usual contracts: a big table what you would get, and a small text below "this is not what we offer" ;-) I'd propose to put this information directly into the table - either by removing the two not supported levels, or by flagging them somehow. Ciao Ole
Ole Streicher wrote: > > I'd propose to put this information directly into the table - either > by removing the two not supported levels, or by flagging them somehow. > As I've mention before they _are_ supported and as _per standard_. The standard allows implementations to provide a higher isolation level in place of any lower level that is not implemented. These isolation levels are only intended for you to rely on whatever isolation they guarantee, not on what you could be able to see if the isolation protections are not in place. There is no guarantees on what you will be able to see. The comment you are asking for belongs to a book that teaches people SQL. The PostgreSQL manual assumes SQL knowledge (it is stated right at the beginning). Regards, Fernando -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
> > As I've mention before they _are_ supported and as _per standard_. > The standard allows implementations to provide a higher > isolation level > in place of any lower level that is not implemented. > With 7.3.3, if you try to set the isolation level to TRANSACTION_READ_UNCOMMITTED or TRANSACTION_REPEATABLE_READ an exception is raised. For these levels to be supported shouldn't the driver accept them and set the actual level to the higher one? Darin
Dmitry Tkach wrote: > Fernando Nasser wrote: > >> Ole Streicher wrote: >> >>> >>> I'd propose to put this information directly into the table - either >>> by removing the two not supported levels, or by flagging them somehow. >>> >> >> As I've mention before they _are_ supported and as _per standard_. >> The standard allows implementations to provide a higher isolation >> level in place of any lower level that is not implemented. > > > The statement: > > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; > > results in an error. > This means, that this particular isolation level is *not* supported. > > The "compatibility' section of the 'SET TRANSACTION' charpter in > postgres SQL reference seems to disagree with you too: > If it disagrees it is not with me, but with the committee who wrote the SQL standard. But I don't think it disagrees that much (see below). > "PostgreSQL does not provide the isolation levels READ UNCOMMITTED and > REPEATABLE READ. " > It can't provide REPEATABLE READ. It could/should make it a synonym for SERIALIZABLE. And SERIALIZABLE, which is a misnomer, in PostgreSQL does not actually "serialize" anything, although it does provide the required level of isolation required in the standard, I believe. READ UNCOMMITTED should be accepted and be a synonym for READ COMMITTED as allowed (and suggested) by the SQL standard. > Also, the Appendix "C.2" to the User's Guide - "Unsupported features" > has both REPEATABLE READ (F111-01) and READ UNCOMMITTED (F111-03) listed > as 'unsupported'. > It is unsupported because they give you syntax errors. Perhaps people preferred not to use the allowances in the standard because it could confuse people as not all users know enough SQL to understand that. > Also, I don't know what standard really says about this ... but I have > never seen a database, that would claim that it supports any isolation > level just because it supports a higher one... To me, such definition of > 'support' doesn't, make any sense at all... > If you don't want to look at the standard then look at Date's book. And, again, I only _read_ the standard, I did not wrote it. Please send your complains to the SQL committee. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Fernando Nasser wrote: > Ole Streicher wrote: > >> >> I'd propose to put this information directly into the table - either >> by removing the two not supported levels, or by flagging them somehow. >> > > As I've mention before they _are_ supported and as _per standard_. > The standard allows implementations to provide a higher isolation > level in place of any lower level that is not implemented. The statement: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; results in an error. This means, that this particular isolation level is *not* supported. The "compatibility' section of the 'SET TRANSACTION' charpter in postgres SQL reference seems to disagree with you too: "PostgreSQL does not provide the isolation levels READ UNCOMMITTED and REPEATABLE READ. " Also, the Appendix "C.2" to the User's Guide - "Unsupported features" has both REPEATABLE READ (F111-01) and READ UNCOMMITTED (F111-03) listed as 'unsupported'. Also, I don't know what standard really says about this ... but I have never seen a database, that would claim that it supports any isolation level just because it supports a higher one... To me, such definition of 'support' doesn't, make any sense at all... Even if you are right, and the standard, indeed, suggests that you only have to implement 'serializable' to claim support of all four levels... I don't see much point in agruing that, other than just to say 'postgres is cool' ;-) For all practical purposes, the support isn't there, and the documentation seems to properly (and repeatedly) state that. Dima
Darin Ohashi wrote: >>As I've mention before they _are_ supported and as _per standard_. >>The standard allows implementations to provide a higher >>isolation level >>in place of any lower level that is not implemented. >> > > > With 7.3.3, if you try to set the isolation level to > TRANSACTION_READ_UNCOMMITTED or TRANSACTION_REPEATABLE_READ an exception is > raised. For these levels to be supported shouldn't the driver accept them and > set the actual level to the higher one? > Yes, the JDBC standard assumes the SQL standard, so to be JDBC compliant one is assumed to be SQL compliant as well. We could do this in the driver to make it for the older backends (we are going to support 7.3 and 7.2 in the 74 driver still). On the other hand, people using 7.3 backends have other non-SQL compliances that we cannot mask with the driver, so I wonder if this is worthy of the extra if statement. But that is for Barry and Dave to decide, if someone submits a patch. Regards, Fernando -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
Dmitry Tkach wrote: > > ... or with your reading of the standard perhaps? :-) > Then I am not wasting my time discussing this with you. The SQL standard is a public document, you can go and read it yourself. -- Fernando Nasser Red Hat Canada Ltd. E-Mail: fnasser@redhat.com 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9
> > If it disagrees it is not with me, but with the committee who wrote > the SQL standard. ... or with your reading of the standard perhaps? :-) > But I don't think it disagrees that much (see below). > > >> "PostgreSQL does not provide the isolation levels READ UNCOMMITTED >> and REPEATABLE READ. " >> > > It can't provide REPEATABLE READ. It could/should make it a synonym > for SERIALIZABLE. Maybe. But it doesn't, does it? At least, at the moment, there is no such thing as 'REPEATABLE READ' in postgres at all, so any argument that it is "supported" doesn't seem to make much sense... Perhaps, you meant to say "it could/should be supported"?... :-) > > And SERIALIZABLE, which is a misnomer, in PostgreSQL does not actually > "serialize" anything, although it does provide the required level of > isolation required in the standard, I believe. It makes concurrent transactions behave as if they were serialized. That's exactly what serializable means.... > > It is unsupported because they give you syntax errors. Perhaps people > preferred not to use the allowances in the standard because it could > confuse people as not all users know enough SQL to understand that. Yeah... I have never met a user who would understand that actually :-) > If you don't want to look at the standard then look at Date's book. To the contrary, I'd *love to* look at it. I just don't know *where*. If you could give me a link, that would be great. or, at least, if you just qoute a relevant section you are referring to... that would at least give me the context ... > > And, again, I only _read_ the standard, I did not wrote it. Please > send your complains to the SQL committee. I don't have any complaints. I just find it hard to believe that it is really meant to be the way you read it. I mean, maybe it is. But, unless I can see it with my own eyes, I still find it a lot more likely that you've just misread what it is saying... Dima
On Mon, 28 Jul 2003, Fernando Nasser wrote: > Darin Ohashi wrote: > >>As I've mention before they _are_ supported and as _per standard_. > >>The standard allows implementations to provide a higher > >>isolation level > >>in place of any lower level that is not implemented. > >> > > > > > > With 7.3.3, if you try to set the isolation level to > > TRANSACTION_READ_UNCOMMITTED or TRANSACTION_REPEATABLE_READ an exception is > > raised. For these levels to be supported shouldn't the driver accept them and > > set the actual level to the higher one? > > > > Yes, the JDBC standard assumes the SQL standard, so to be JDBC compliant > one is assumed to be SQL compliant as well. > > We could do this in the driver to make it for the older backends (we are > going to support 7.3 and 7.2 in the 74 driver still). On the other > hand, people using 7.3 backends have other non-SQL compliances that we > cannot mask with the driver, so I wonder if this is worthy of the extra > if statement. But that is for Barry and Dave to decide, if someone > submits a patch. > Well, considering that DatabaseMetaData has a supportsTransactionIsolationLevel method I don't believe setTransationIsolation should ever be called with a value the driver does not claim to support. This seems to solve the poster's original problem and allows the client to make the isolation level escaltion decision instead of the server. Kris Jurka
Hello Fernando! Fernando Nasser writes: > The comment you are asking for belongs to a book that teaches people > SQL. OK, so this is (as well as the Connection problem) solved for me ;-). Thank you. But in any case I have still the problem when using the getInputStream() method that I get an EOF when the 8th bit is set in a byte. Unfortunately, nobody answered on this mail. Could you (or anybody else) help me there? I wrote about it in the mail with the subject "Problem with LargeObject/jdbc when writing short (Repost)". Ole