Skip site navigation (1) Skip section navigation (2)

Re: Row Lock

From: Andreas Prohaska <ap(at)apeiron(dot)de>
To: 'Alessandro Polverini' <polverini(at)nibbles(dot)it>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Row Lock
Date: 2003-10-07 14:27:14
Message-ID: A11D9B8C48C1D411AE0C000062A129947B1CB0@ganymed.allocation.net (view raw or flat)
Thread:
Lists: pgsql-jdbc
I don't think that there is something similar in postgres. You would have
to do this on your own, using an "int" or "postgres timestamp". What I
meant was something like:

	1. Read the row from the database together with the optimistic
control
         column (occ). Let's assume it's an int with a current value of 1.

	2. Remember this value (1) and let the user edit the record.

	3. Now before writing the changes of the user into the database:

	3.a  SELECT the row FOR UPDATE and compare the current value
           of the occ with the value you remembered. If they are
           not equal -> rollback, otherwise ...

 	3.b  increment the value of the occ to 2

	3.c  UPDATE the record (releasing the update lock)


As Dave Cramer suggested you may also use a timestamp with a
trigger to do this automatically. Although I don't know if there
wouldn't be a theoretical possibility that someone updates the
record again before the trigger runs (I really don't know!).

> > The easiest way to come around this sort of problem is to use an
> > optimistic control column (like an int) that is incremented
> > with every row update. This allows you to detect changes by other
> > users before making your own.
> 
> I'm not really sure what do you mean with the use of 
> optimistic control
> column, can you please give me some detail?
> 
> The problem (for me) is to find a solution that is functional also for
> clients that uses the db other the mine.
> 
> In mysql there is the column type "timestamp" that does exactly this:
> records the time of the last update of the row, so I need to 
> check only
> this value.
> 
> Is there something similar in postgresql?
> 

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2003-10-07 14:29:35
Subject: Re: Row Lock
Previous:From: Alessandro PolveriniDate: 2003-10-07 14:00:29
Subject: Re: Row Lock

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group