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

Re: possible row locking bug in 7.0.3 & 7.1

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Forest Wilkinson <fspam(at)home(dot)com>
Cc: pgsql-sql(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: possible row locking bug in 7.0.3 & 7.1
Date: 2001-03-28 06:11:08
Message-ID: 17070.985759868@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
Forest Wilkinson <fspam(at)home(dot)com> writes:
> If I remember correctly, UPDATE establishes a lock on the affected rows, 
> which will block another UPDATE on the same rows for the duration of the 
> transaction.  If that's true, shouldn't I be able to achieve my desired 
> behavior by removing the initial as follows:

> create function nextid( varchar(32)) returns int8 as '
>   update idseq set id = id + 1 where name = $1::text;
>   select id from idseq where name = $1::text;
>   ' language 'sql';

> Or, would I still have to add FOR UPDATE to that final SELECT?

You're right, the initial SELECT FOR UPDATE is a waste of cycles
considering that you're not using the value it returns.  But you'll
still need the last select to be FOR UPDATE so that it plays by the
same rules as the UPDATE does.

			regards, tom lane

In response to

pgsql-hackers by date

Next:From: Tatsuo IshiiDate: 2001-03-28 06:11:59
Subject: Re: Re: Call for platforms
Previous:From: Tom LaneDate: 2001-03-28 06:08:45
Subject: Re: Re: Call for platforms

pgsql-sql by date

Next:From: Hiroshi InoueDate: 2001-03-28 06:19:05
Subject: Re: [HACKERS] Re: possible row locking bug in 7.0.3 & 7.1
Previous:From: Forest WilkinsonDate: 2001-03-28 06:08:00
Subject: Re: possible row locking bug in 7.0.3 & 7.1

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