Serializable transactions and SQLException
Serializable transactions and SQLException
От:
Giampaolo Tomassoni <g.tomassoni@libero.it>
Дата:
Dears, I would like to develop java code using the pgsql-jdbc driver on serializable isolated transactions. The (general) pgsql manual states that this, of course, may cause concurrent updating transaction failures to inform the client to retry the transaction as a whole. This is said to be reported by the error message: ERROR: Can't serialize access due to concurrent update Great. I want to cope with it. But what's the SQLException.getSQLState() value associated to this? Or is it a SQLException.getErrorCode()? Is this value something 'standard', in the sense that, ie., if I need to switch to Oracle it works fine? Is there a better way to identify 'please, retry' suggestions than browsing the SQLException object? It is possible I didn't search enough, but it is a matter of fact that I didn't find any information about it. Please spare a word about it. Regards, Giampaolo Tomassoni
Re: Serializable transactions and SQLException
От:
Giampaolo Tomassoni <g.tomassoni@libero.it>
Дата:
On Friday 29 October 2004 04:19, you wrote: > > ... omissis... > > If you are connected to a >= 7.4 server getSQLState will have a value for > this error. The best way to identify it and make sure you code works on > other database platforms is to actually write a small test case to > generate this error and grab the error code. PG will always return > the same error code, but other databases may not use the same one and the > best way to find out is actually testing. Thank you everybody for the prompt help. I see your replies suggest to switch to a 7.4 or earlier version. I have 7.3.2 in my production environment. What is supposed to solve a >=7.4 server in relation to serializable isolation? Thanks again, Giampaolo Tomassoni
Re: Serializable transactions and SQLException
От:
Giampaolo Tomassoni <g.tomassoni@libero.it>
Дата:
On Friday 29 October 2004 10:44, you wrote: > On Fri, 29 Oct 2004, Giampaolo Tomassoni wrote: > > I see your replies suggest to switch to a 7.4 or earlier version. I have > > 7.3.2 in my production environment. What is supposed to solve a >=7.4 > > server in relation to serializable isolation? > > 7.4 or later. Yes, later. I meant that. > Only newer server versions return error codes with errors. Oh, that's why I didn't see anything of that in PGresult structure... Thank you very much for the help. Regards to everybody, Giampaolo Tomassoni > > Kris Jurka
Re: Serializable transactions and SQLException
От:
Oliver Jowett <oliver@opencloud.com>
Дата:
Giampaolo Tomassoni wrote: > Dears, > > I would like to develop java code using the pgsql-jdbc driver on serializable > isolated transactions. The (general) pgsql manual states that this, of > course, may cause concurrent updating transaction failures to inform the > client to retry the transaction as a whole. > > This is said to be reported by the error message: > > ERROR: Can't serialize access due to concurrent update > > > Great. I want to cope with it. But what's the SQLException.getSQLState() value > associated to this? Or is it a SQLException.getErrorCode()? Is this value > something 'standard', in the sense that, ie., if I need to switch to Oracle > it works fine? Is there a better way to identify 'please, retry' suggestions > than browsing the SQLException object? Your best bet is to use a 7.4 or later server and inspect the SQLState of the exception. To find out what SQLState to expect, either try it and see, or take a look at src/include/utils/errcodes.h in the server source tree and pair it up with the appropriate ereport() call you are interested in. For transaction serialization failures it is ERRCODE_T_R_SERIALIZATION_FAILURE which has a SQLState of 40001. SQLStates are somewhat standardized around SQL99. The comments in errcodes.h say: >> * The SQL99 code set is rather impoverished, especially in the area of >> * syntactical and semantic errors. We have borrowed codes from IBM's DB2 >> * and invented our own codes to develop a useful code set. I have no idea what Oracle does in this area. -O
Re: Serializable transactions and SQLException
От:
Kris Jurka <books@ejurka.com>
Дата:
On Thu, 28 Oct 2004, Giampaolo Tomassoni wrote: > I would like to develop java code using the pgsql-jdbc driver on > serializable isolated transactions. The (general) pgsql manual states > that this, of course, may cause concurrent updating transaction failures > to inform the client to retry the transaction as a whole. > > This is said to be reported by the error message: > > ERROR: Can't serialize access due to concurrent update > > > Great. I want to cope with it. But what's the SQLException.getSQLState() value > associated to this? Or is it a SQLException.getErrorCode()? Is this value > something 'standard', in the sense that, ie., if I need to switch to Oracle > it works fine? Is there a better way to identify 'please, retry' suggestions > than browsing the SQLException object? If you are connected to a >= 7.4 server getSQLState will have a value for this error. The best way to identify it and make sure you code works on other database platforms is to actually write a small test case to generate this error and grab the error code. PG will always return the same error code, but other databases may not use the same one and the best way to find out is actually testing. Kris Jurka
Re: Serializable transactions and SQLException
От:
Kris Jurka <books@ejurka.com>
Дата:
On Fri, 29 Oct 2004, Giampaolo Tomassoni wrote: > I see your replies suggest to switch to a 7.4 or earlier version. I have > 7.3.2 in my production environment. What is supposed to solve a >=7.4 > server in relation to serializable isolation? 7.4 or later. Only newer server versions return error codes with errors. Kris Jurka
