Thread: Portable interpretation of jdbc SQLException for SERIALIZABLE transaction restart?
Portable interpretation of jdbc SQLException for SERIALIZABLE transaction restart?
From
Dave Tenny
Date:
I'm getting the following expected exception:
java.sql.SQLException: ERROR: Can't serialize access due to concurrent update
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:197)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183)
at DbMutex.updateRow(DbMutex.java:70)
at DbMutex.run(DbMutex.java:38)
So I know I need to restart my transaction. What I don't know
is how to portably interpret this particular SQLException instance as one
that justifies a transaction restart, as opposed to the myriad other types of SQLExceptions I might get.
So two questions:
1) How do I know this is an exception indicative of the need for a transaction restart in PostgreSQL?
2) How do I do this portably? (I.e. in a database neutral fashion).
The Sun spec on SQLException talks about XOPEN SQLstate interpretation,
I'm still looking for documentation on that puppy. It also talks about SQL99.
At this point I'm unsure which applies to this exception for postgresql.
I'm using PostgreSQL 7.3.3 and Java 1.4.2 on linux, though hopefully none of that
makes a difference.
Thanks for any tips.
java.sql.SQLException: ERROR: Can't serialize access due to concurrent update
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
at org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:197)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183)
at DbMutex.updateRow(DbMutex.java:70)
at DbMutex.run(DbMutex.java:38)
So I know I need to restart my transaction. What I don't know
is how to portably interpret this particular SQLException instance as one
that justifies a transaction restart, as opposed to the myriad other types of SQLExceptions I might get.
So two questions:
1) How do I know this is an exception indicative of the need for a transaction restart in PostgreSQL?
2) How do I do this portably? (I.e. in a database neutral fashion).
The Sun spec on SQLException talks about XOPEN SQLstate interpretation,
I'm still looking for documentation on that puppy. It also talks about SQL99.
At this point I'm unsure which applies to this exception for postgresql.
I'm using PostgreSQL 7.3.3 and Java 1.4.2 on linux, though hopefully none of that
makes a difference.
Thanks for any tips.
FYI, additional testing with the software revs indicated below reveals that the SQLException SQLState is null, and the vendor specific error code is zero. That seems to leave me stuck with interpreting the exception string content (ugh!) unless someone has additional information. Dave Tenny wrote: > I'm getting the following /expected/ exception: > > java.sql.SQLException: ERROR: Can't serialize access due to > concurrent update > > at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) > at > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505) > > at > org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320) > > at > org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48) > > at > org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:197) > > at > org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183) > > at DbMutex.updateRow(DbMutex.java:70) > at DbMutex.run(DbMutex.java:38) > > So I know I need to restart my transaction. What I don't know > is how to portably interpret this particular SQLException instance as one > that justifies a transaction restart, as opposed to the myriad other > types of SQLExceptions I might get. > > So two questions: > > 1) How do I know this is an exception indicative of the need for a > transaction restart in PostgreSQL? > 2) How do I do this portably? (I.e. in a database neutral fashion). > > The Sun spec on SQLException talks about XOPEN SQLstate interpretation, > I'm still looking for documentation on that puppy. It also talks > about SQL99. > At this point I'm unsure which applies to this exception for postgresql. > > I'm using PostgreSQL 7.3.3 and Java 1.4.2 on linux, though hopefully > none of that > makes a difference. > > Thanks for any tips. >
Dave, If you use the 7.4 driver and database you will get real SQLState codes. That is new functionality in 7.4. --Barry Dave Tenny wrote: > FYI, additional testing with the software revs indicated below > reveals that the SQLException SQLState is null, and the vendor specific > error code is zero. > That seems to leave me stuck with interpreting the exception string > content (ugh!) > unless someone has additional information. > > Dave Tenny wrote: > >> I'm getting the following /expected/ exception: >> >> java.sql.SQLException: ERROR: Can't serialize access due to >> concurrent update >> >> at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131) >> at >> org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505) >> >> at >> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320) >> >> at >> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48) >> >> at >> org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:197) >> >> at >> org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183) >> >> at DbMutex.updateRow(DbMutex.java:70) >> at DbMutex.run(DbMutex.java:38) >> >> So I know I need to restart my transaction. What I don't know >> is how to portably interpret this particular SQLException instance as one >> that justifies a transaction restart, as opposed to the myriad other >> types of SQLExceptions I might get. >> >> So two questions: >> >> 1) How do I know this is an exception indicative of the need for a >> transaction restart in PostgreSQL? >> 2) How do I do this portably? (I.e. in a database neutral fashion). >> >> The Sun spec on SQLException talks about XOPEN SQLstate interpretation, >> I'm still looking for documentation on that puppy. It also talks >> about SQL99. >> At this point I'm unsure which applies to this exception for postgresql. >> >> I'm using PostgreSQL 7.3.3 and Java 1.4.2 on linux, though hopefully >> none of that >> makes a difference. >> >> Thanks for any tips. >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >