Обсуждение: How to make a read-write atomic?

Hi,

I used to use the BEGIN-COMMIT pair in order to ensure that two
processes didn't read the same data and write at the same time, so
messing things up.  But I've recently upgraded to the latest version of
postgresql and now it doesn't work anymore.  Does anybody know what's
going on?

To explain my situation in more detail:
I want a process to read the database for the entry that has the highest
customerid number.  I then want the process to write the next entry into
the database with a customerid that is one larger.  But if two processes
read at the same time, then they will both write an entry that has the
same customerid number.  How do I keep this from occurring since the
BEGIN command doesn't seem to work for me anymore?

I am using PostgreSQL 6.5.1 on RedHat 6.0

Thanks, Daniel Stolk

>
> Hi,
>
> I used to use the BEGIN-COMMIT pair in order to ensure that two
> processes didn't read the same data and write at the same time, so
> messing things up.  But I've recently upgraded to the latest version of
> postgresql and now it doesn't work anymore.  Does anybody know what's
> going on?
>
> To explain my situation in more detail:
> I want a process to read the database for the entry that has the highest
> customerid number.  I then want the process to write the next entry into
> the database with a customerid that is one larger.  But if two processes
> read at the same time, then they will both write an entry that has the
> same customerid number.  How do I keep this from occurring since the
> BEGIN command doesn't seem to work for me anymore?
>
> I am using PostgreSQL 6.5.1 on RedHat 6.0
>
> Thanks, Daniel Stolk
>

If you are simply looking for a unique value, you can use a sequence.

The best way to do this is to make the customerid be type serial. That way
you dont have to do anything to it, when you insert into thew table, the
new row gets a unique incrimented numeric value

                        M Simms

Daniel Stolk wrote:
>
> Hi,
>
> I used to use the BEGIN-COMMIT pair in order to ensure that two
> processes didn't read the same data and write at the same time, so
> messing things up.  But I've recently upgraded to the latest version of
> postgresql and now it doesn't work anymore.  Does anybody know what's
> going on?
>
> To explain my situation in more detail:
> I want a process to read the database for the entry that has the highest
> customerid number.  I then want the process to write the next entry into
> the database with a customerid that is one larger.  But if two processes
> read at the same time, then they will both write an entry that has the
> same customerid number.  How do I keep this from occurring since the
> BEGIN command doesn't seem to work for me anymore?

Use LOCK TABLE IN SHARE ROW EXCLUSIVE MODE before reading max
customerid. Please read doc and "Migration to v6.5" in HISTORY.

Vadim