i have the following problem. i have a simple updateable view V with a status field S.
nowadays i have two clients that try to consume data out of this view V if S = 'TOBEPROCESSED' the two clients are jdbc clients and they try the following every 30 seconds. each clients tries to ----------------- update V set S ='$MYCLIENTID' where S = 'TOBEPROCESSED' in this way i try to reserve the current available data TOBEPROCESSED for one client and then process it.
when i do this i sometimes (if they overlap) get the following exception:
Stacktrace: java.sql.SQLException: ERROR: deadlock detected at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1365) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1160) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:172) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:387) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:328) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:319) at ComponentsJcdIn.jcdReader.handleConnection(ComponentsJcdIn.jcdReader:303) at ComponentsJcdIn.jcdReader.start(ComponentsJcdIn.jcdReader:108) at prjGwiJdbc1mGenericStagingMulti.csvcReader.jcdReader_Runtime_Handler.handleRequest(prjGwiJdbc1mGenericStagingMulti.csvcReader.jcdReader_Runtime_Handler:381) at sun.reflect.GeneratedMethodAccessor593.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1067) at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:176) at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2899) at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:3990) at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:197) at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:134) at $Proxy443.handleRequest(Unknown Source)
what exactly does that mean? is my tactic wrong? in oracle this works just fine.
regards chris
-- ---------- Christian Brennsteiner Salzburg / Austria / Europe