hi group,

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

Search Discussions

  • Tom Lane at Jan 31, 2011 at 5:07 pm

    Christian Brennsteiner writes:
    i have a simple updateable view V with a status field S.
    ...
    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
    There are no "simple updateable views" in Postgres. Your problem is
    probably traceable to some aspect of the view update rule, or possibly
    something about foreign keys or other actions that have to be taken
    pursuant to the update on the underlying table. But since you haven't
    shown us any of the schema details, it's impossible to do more than
    guess.

    In general it seems like you're trying to reinvent a queuing mechanism.
    You'd be better off adopting one of the existing ones, as getting this
    both right and high-performing is harder than one might think.

    regards, tom lane
  • Christian Brennsteiner at Feb 1, 2011 at 8:50 am
    hi tom,

    thanks for your answer!
    we have now tried to set TRANSACTION_SERIALIZEABLE and we are seeing a
    behaviour that we can deal with.
    if we set TRANSACTION_SERIALIZEABLE on the database connection we get
    exceptions if someone else is dealing with the data at the same time.

    might this be sufficient? or do we have to to dig deeper into this?
    right now this behaviour is acceptable for us.

    the schema would be:

    CREATE VIEW staging_area.integration_staging
    AS
    SELECT
    sd.id,
    sd.creationtimestamp,
    country.name AS country,
    myDepartment.name AS myDepartmentname,
    det.detectorobjectname AS areaid,
    CASE WHEN (det.intervalduration <= 0) THEN sd.eventtimestamp ELSE
    (sd.eventtimestamp - ('00:00:01'::interval * (det.intervalduration)::double
    precision)) END AS eventtimefrom,
    sd.eventtimestamp AS eventtimeto,
    (sd.actionid)::character varying(10) AS actionid,
    sd.actionvalue,
    sd.state FROM staging_area.integration_staging_data sd,
    detectorobject det,
    locations country,
    locations myDepartment,
    locations platform,
    meassurepoints,
    meassurepoint_associations
    WHERE
    ((((((sd.fk_do_id = det.id)
    AND (myDepartment.parent_id = country.id))
    AND (platform.parent_id = myDepartment.id))
    AND (meassurepoints.fk_location_id = platform.id))
    AND (meassurepoint_associations.fk_meassurepoint_id = meassurepoints.id))
    AND (meassurepoint_associations.fk_do_id = det.id));
    GO

    we always just update state from TOBEPROCESSED to MYID and then to DONE.

    @reinventing queueing --> can you give me a hint to documentation where
    something like that is described?

    regards chris
    On Mon, Jan 31, 2011 at 6:07 PM, Tom Lane wrote:

    Christian Brennsteiner <eingfoan@yahoo.de> writes:
    i have a simple updateable view V with a status field S.
    ...
    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
    There are no "simple updateable views" in Postgres. Your problem is
    probably traceable to some aspect of the view update rule, or possibly
    something about foreign keys or other actions that have to be taken
    pursuant to the update on the underlying table. But since you haven't
    shown us any of the schema details, it's impossible to do more than
    guess.

    In general it seems like you're trying to reinvent a queuing mechanism.
    You'd be better off adopting one of the existing ones, as getting this
    both right and high-performing is harder than one might think.

    regards, tom lane


    --
    ----------
    Christian Brennsteiner
    Salzburg / Austria / Europe

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJan 26, '11 at 3:44p
activeFeb 1, '11 at 8:50a
posts3
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase