Re: Row level exclusive locking

#1

Vesa-Matti Kari <vmkari (AT) cc (DOT) helsinki.fi> wrote:

Quote:

Are the plans to implement row level exclusive locking that prevents
readers and writers in future versions of PostgreSQL?

You can find the list of features that the developers have accepted as
desirable at http://wiki.postgresql.org/wiki/Todo . I don't see any
sign of this feature there.

If you want to put this forward as a feature request, I think the
pgsql-general mailing list is the right place. They'll probably ask why
you want such a thing.

-M-

#2

Vesa-Matti Kari wrote:

Quote:

I'd like to do exclusive row level locking that blocks readers
as well as writers. It seems impossible to do in PostgreSQL
8.4 based on this documentation:

[...]

It is very undesirable to lock the whole table because that prevents
concurrent clients that would need locks to only their "own" rows.

Are the plans to implement row level exclusive locking that prevents
readers and writers in future versions of PostgreSQL?

I am sure that this is never going to happen, as it is considered
one of the most desirable features of PostgreSQL that a reader is
(almost) never blocked.

I'd be curious to know your problem: what makes you want to block
readers? Maybe there is some other way to achieve your goal.

Yours,
Laurenz Albe

#3

Vesa-Matti Kari wrote:

Quote:

I'd like to do exclusive row level locking that blocks readers
as well as writers. It seems impossible to do in PostgreSQL
8.4 based on this documentation:

I am sure that this is never going to happen, as it is considered
one of the most desirable features of PostgreSQL that a reader is
(almost) never blocked.

I'd be curious to know your problem: what makes you want to block
readers? Maybe there is some other way to achieve your goal.

There is an integer column that needs to be read-and-updated
in exclusive mode. If I get two instances trying to do this
without exclusive locking, there exists the classic race
condition where, for instance, the following is possible:

client A reads an INT column, getting, say, 7
client B reads the same INT, also getting 7
client A increments INT value, making it 8
client B increments INT value, making it 8

I could have lots of clients, so creating SEQUENCE objects for
each of them sounds undesirable. What is more, the integer
progression is not always monotonically increasing (e.g. 1, 2, 3),
it could be 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 20, 21, 22, 23).

Anyway, this case may well be an instance of the "premature optimization
is the root of all evil" problem. I discussed the issue with a
colleague and he thinks the table level locking will not harm
performance here.

There is no need for table locking, the existing row locking
techniques can cope well with this problem.

All you have to do is use SELECT ... FOR UPDATE when you
read the integer column.

Your example will look as follows then:

Client A selects the value FOR UPDATE, getting 7
Client B tries to select the same value FOR UPDATE
and is blocked because exclusive locks conflict
with exclusive locks
Client A increments the value to 8
Client A commits
Client B is unblocked and reads the value 8
Client B increments the value to 9 and commits

So you see, the reader *is* blocked if you SELECT FOR UPDATE.

Does that solve your problem?

Yours,
Laurenz Albe

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Row level exclusive locking

comp.databases.postgresql comp.databases.postgresql

Re: Row level exclusive locking - 08-14-2009 , 01:59 PM

Re: Row level exclusive locking - 08-17-2009 , 03:12 AM

Re: Row level exclusive locking - 08-18-2009 , 11:17 AM