Обсуждение: Serializable transactions and SQLException

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

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

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

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

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

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