Iam trying to acquire rowlevel locks in postgresql. I try doing this:
'select * from students where name='Larry' for update;
But by looking at the holding array of proclock , I've noticed that by doing 
this only
AccessShareLock gets acquired which is a table level lock.
How do I acquire rowlevelock and what fields of Lock or Proclock 
datastructures indicate it.
Thanks
Jenny

_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online  
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963


Wed, 23 Jul 2003 12:48:19 -0700
"Jenny -" <nat_lazy@hotmail.com>
this is in plain text (row level locks)
"Jenny -" <nat_lazy@hotmail.com> writes:
> Iam trying to acquire rowlevel locks in postgresql. I try doing this:
> 'select * from students where name='Larry' for update;
> But by looking at the holding array of proclock , I've noticed that by doing 
> this only
> AccessShareLock gets acquired which is a table level lock.

Row-level locks are not recorded in proclock --- they are implemented by
marking the individual tuple on-disk.  If we tried to record them in
shared memory, it'd be very easy to run out of shared memory, because
you could be holding row locks on a large number of tuples.

			regards, tom lane

Wed, 23 Jul 2003 20:36:30 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: this is in plain text (row level locks) 
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> "Jenny -" <nat_lazy@hotmail.com> writes:
    >> Iam trying to acquire rowlevel locks in postgresql. I try doing
    >> this: 'select * from students where name='Larry' for update;
    >> But by looking at the holding array of proclock , I've noticed
    >> that by doing this only AccessShareLock gets acquired which is
    >> a table level lock.

    Tom> Row-level locks are not recorded in proclock --- they are
    Tom> implemented by marking the individual tuple on-disk.  If we
    Tom> tried to record them in shared memory, it'd be very easy to
    Tom> run out of shared memory, because you could be holding row
    Tom> locks on a large number of tuples.

Of course, other database systems do this without too much hassle
.. including relying on lock escalation (move up to page/table level
locks) when the number of locks grow too large. 

Does pgsql only record X locks on the individual tuples on-disk or
does it do so for S locks as well ? 

Not that I dislike the idea - Toby Lehman suggested this in his
Ph.D. thesis in the mid-eighties for main-memory databases (where you
don't take the write penalty).

-- 
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh



23 Jul 2003 18:12:21 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: this is in plain text (row level locks)
Sailesh Krishnamurthy wrote:
> >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
> 
>     Tom> "Jenny -" <nat_lazy@hotmail.com> writes:
>     >> Iam trying to acquire rowlevel locks in postgresql. I try doing
>     >> this: 'select * from students where name='Larry' for update;
>     >> But by looking at the holding array of proclock , I've noticed
>     >> that by doing this only AccessShareLock gets acquired which is
>     >> a table level lock.
> 
>     Tom> Row-level locks are not recorded in proclock --- they are
>     Tom> implemented by marking the individual tuple on-disk.  If we
>     Tom> tried to record them in shared memory, it'd be very easy to
>     Tom> run out of shared memory, because you could be holding row
>     Tom> locks on a large number of tuples.
> 
> Of course, other database systems do this without too much hassle
> .. including relying on lock escalation (move up to page/table level
> locks) when the number of locks grow too large. 

I wouldn't say they do it with little hassle --- it is quite a pain, in
fact, at least for users.

> Does pgsql only record X locks on the individual tuples on-disk or
> does it do so for S locks as well ? 

We don't need to shared lock individual rows because of MVCC --- well,
we sort of do by recording our xid in our proc structure, so folks don't
change things underneath us.  We prevent expired rows from disappearing
from the disk by others looking at our proc start xid.


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Wed, 23 Jul 2003 21:59:44 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: this is in plain text (row level locks)
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Sailesh Krishnamurthy wrote:
>> Does pgsql only record X locks on the individual tuples on-disk or
>> does it do so for S locks as well ? 

> We don't need to shared lock individual rows because of MVCC --- well,
> we sort of do by recording our xid in our proc structure, so folks don't
> change things underneath us.  We prevent expired rows from disappearing
> from the disk by others looking at our proc start xid.

This is actually an issue though.  Row-level shared locks would be
really nice to have for foreign-key handling.  Right now we have to
use X locks for those, and that leads to deadlocking problems for
applications.

			regards, tom lane

Wed, 23 Jul 2003 22:03:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: this is in plain text (row level locks) 
Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
>>Sailesh Krishnamurthy wrote:
>>
>>>Does pgsql only record X locks on the individual tuples on-disk or
>>>does it do so for S locks as well ? 
> 
> 
>>We don't need to shared lock individual rows because of MVCC --- well,
>>we sort of do by recording our xid in our proc structure, so folks don't
>>change things underneath us.  We prevent expired rows from disappearing
>>from the disk by others looking at our proc start xid.
> 
> 
> This is actually an issue though.  Row-level shared locks would be
> really nice to have for foreign-key handling.  Right now we have to
> use X locks for those, and that leads to deadlocking problems for
> applications.

Yes! Yes! It's the last big hurdle for an otherwise excellent RI
implementation...

Just wanted "Joe-user's" enthusiasm for row-level S locks registered
somewhere... :-)

Mike Mascari
mascarm@mascari.com



Wed, 23 Jul 2003 23:38:54 -0400
Mike Mascari <mascarm@mascari.com>
Re: this is in plain text (row level locks)
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Sailesh Krishnamurthy wrote:
> >> Does pgsql only record X locks on the individual tuples on-disk or
> >> does it do so for S locks as well ? 
> 
> > We don't need to shared lock individual rows because of MVCC --- well,
> > we sort of do by recording our xid in our proc structure, so folks don't
> > change things underneath us.  We prevent expired rows from disappearing
> > from the disk by others looking at our proc start xid.
> 
> This is actually an issue though.  Row-level shared locks would be
> really nice to have for foreign-key handling.  Right now we have to
> use X locks for those, and that leads to deadlocking problems for
> applications.

Is the plan to allow one backend to shared lock the row while others can
read it but not modify it, or is the idea to actually allow multiple
backends to record their shared status on the row?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 24 Jul 2003 00:12:27 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: this is in plain text (row level locks)
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> This is actually an issue though.  Row-level shared locks would be
>> really nice to have for foreign-key handling.  Right now we have to
>> use X locks for those, and that leads to deadlocking problems for
>> applications.

> Is the plan to allow one backend to shared lock the row while others can
> read it but not modify it, or is the idea to actually allow multiple
> backends to record their shared status on the row?

Plan?  We have no plan to fix this :-(.  But clearly there has to be
some way to tell which backends hold read locks on a shared-locked row,
else you can't tell if they've all dropped the lock or not.

			regards, tom lane

Thu, 24 Jul 2003 00:29:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: this is in plain text (row level locks) 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> This is actually an issue though.  Row-level shared locks would be
> >> really nice to have for foreign-key handling.  Right now we have to
> >> use X locks for those, and that leads to deadlocking problems for
> >> applications.
> 
> > Is the plan to allow one backend to shared lock the row while others can
> > read it but not modify it, or is the idea to actually allow multiple
> > backends to record their shared status on the row?
> 
> Plan?  We have no plan to fix this :-(.  But clearly there has to be
> some way to tell which backends hold read locks on a shared-locked row,
> else you can't tell if they've all dropped the lock or not.

I suppose we could allow one backend to mark the page with a shared lock
for primary key purposes while others read it.  Does that buy us
anything?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 24 Jul 2003 01:21:53 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: this is in plain text (row level locks)
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I suppose we could allow one backend to mark the page with a shared lock
> for primary key purposes while others read it.  Does that buy us
> anything?

That doesn't work, unless you insist that the first backend can't exit
its transaction until all the other ones are done.  Which introduces its
own possibilities for deadlock --- but even worse, how does the first
backend *know* that the other ones are done?  You're right back where
you started: it has to be possible to tell which backends have
share-locked a particular row.

			regards, tom lane

Thu, 24 Jul 2003 01:28:09 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: this is in plain text (row level locks) 
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> That doesn't work, unless you insist that the first backend
    Tom> can't exit its transaction until all the other ones are done.
    Tom> Which introduces its own possibilities for deadlock --- but
    Tom> even worse, how does the first backend *know* that the other
    Tom> ones are done?  You're right back where you started: it has
    Tom> to be possible to tell which backends have share-locked a
    Tom> particular row.

Is a count a solution ? 

The first backend gets the S lock on the row - I'm assuming you plan
to do it by recording it on the tuple and not in a shared memory lock
table, which means that you might have to unnecessarily write an
unmodified page if its buffer pool frame is stolen. 

The problem is that on commit time, you must carefully decrement the
count value of shared locks on any tuple that you own. This can be
accomplished by having each backend keep track of the list of files
and TIDs for any rows for which it acquired S locks. Is this the same
way that pgsql releases the X locks ? 

Bruce, I don't disagree that MVCC has the very nice property that
writers don't block readers. However, I don't buy that 2-phase
locking, with lock escalation is either unworkable because of too many
locks, or causes any extra pain for the user application (apart from
the fact that writers not blocking readers gives you more concurrency
at some very minor overhead of not being strictly serializable).

-- 
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh



23 Jul 2003 23:45:39 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: this is in plain text (row level locks)
On Thu, 2003-07-24 at 02:45, Sailesh Krishnamurthy wrote:
> >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
> 
>     Tom> That doesn't work, unless you insist that the first backend
>     Tom> can't exit its transaction until all the other ones are done.
>     Tom> Which introduces its own possibilities for deadlock --- but
>     Tom> even worse, how does the first backend *know* that the other
>     Tom> ones are done?  You're right back where you started: it has
>     Tom> to be possible to tell which backends have share-locked a
>     Tom> particular row.
> 
> Is a count a solution ? 

Almost.  Problem with a count is the difficulty decrementing the count
after a crash.  You would also need to store a log of some kind so you
know what to reset while starting up in error recovery mode which would
add a large amount of overhead.

It may be best to have a locking manager run as a separate process. 
That way it could store locks in ram or spill over to disk. Now the
problem is communication overhead. It probably isn't any worse than
writing them to disk for long queries (actual disk activity resulting),
but short queries are probably going to notice.
24 Jul 2003 06:14:16 -0400
Rod Taylor <rbt@rbt.ca>
Re: this is in plain text (row level locks)
Rod Taylor <rbt@rbt.ca> writes:
> It may be best to have a locking manager run as a separate process.
> That way it could store locks in ram or spill over to disk.

Hmm, that might be workable.  We could imagine that in place of the 
HEAP_MARKED_FOR_UPDATE status bit, we have a "this row is possibly
locked" hint bit.  Only if you see the bit set do you need to query
the lock manager.  If the answer comes back that no lock is held,
you can clear the bit --- so no need for any painful "undo" stuff
after a crash, and no communication overhead in the normal case.

			regards, tom lane

Thu, 24 Jul 2003 09:36:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: this is in plain text (row level locks) 
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> Rod Taylor <rbt@rbt.ca> writes:
    >> It may be best to have a locking manager run as a separate
    >> process.  That way it could store locks in ram or spill over to
    >> disk.

    Tom> Hmm, that might be workable.  We could imagine that in place
    Tom> of the HEAP_MARKED_FOR_UPDATE status bit, we have a "this row
    Tom> is possibly locked" hint bit.  Only if you see the bit set do
    Tom> you need to query the lock manager.  If the answer comes back

Why do you want to query the lock manager as a separate process ? 

Why not have the traditional approach of a lock table in shared
memory, growing and shrinking as appropriate, and have each individual
process update it (need to protect it with a latch of course).

-- 
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh



24 Jul 2003 15:01:43 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: this is in plain text (row level locks)
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu> writes:
> Why not have the traditional approach of a lock table in shared
> memory, growing and shrinking as appropriate,

Because we can't grow shared memory.  Whatever size we get at startup is
what we're stuck with.  (I suppose we could try asking the kernel for
additional segments, but there's every likelihood that that will fail.)

			regards, tom lane

Thu, 24 Jul 2003 18:09:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: this is in plain text (row level locks) 
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> Sailesh Krishnamurthy <sailesh@cs.berkeley.edu> writes:
    >> Why not have the traditional approach of a lock table in shared
    >> memory, growing and shrinking as appropriate,

    Tom> Because we can't grow shared memory.  Whatever size we get at
    Tom> startup is what we're stuck with.  (I suppose we could try
    Tom> asking the kernel for additional segments, but there's every
    Tom> likelihood that that will fail.)

We implemented a Shared Memory MemoryContext using OSSP libmm (used in
Apache) for TelegraphCQ. 

If you think it's useful I can submit it as a patch.

-- 
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh



24 Jul 2003 15:11:46 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: this is in plain text (row level locks)
Sailesh Krishnamurthy wrote:
> >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
> 
>     Tom> Sailesh Krishnamurthy <sailesh@cs.berkeley.edu> writes:
>     >> Why not have the traditional approach of a lock table in shared
>     >> memory, growing and shrinking as appropriate,
> 
>     Tom> Because we can't grow shared memory.  Whatever size we get at
>     Tom> startup is what we're stuck with.  (I suppose we could try
>     Tom> asking the kernel for additional segments, but there's every
>     Tom> likelihood that that will fail.)
> 
> We implemented a Shared Memory MemoryContext using OSSP libmm (used in
> Apache) for TelegraphCQ. 
> 
> If you think it's useful I can submit it as a patch.
> 
> -- 
> Pip-pip
> Sailesh
> http://www.cs.berkeley.edu/~sailesh

             ^^^^^^^^^^^^^^^
Watch out, that code from Berkeley usually is a mess.  :-)

Seriously, though, it would be good to see what you guys are up to.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 24 Jul 2003 18:14:44 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: this is in plain text (row level locks)
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu> writes:
> We implemented a Shared Memory MemoryContext using OSSP libmm (used in
> Apache) for TelegraphCQ. 

How portable is that?  What kind of real-world performance do you get?

			regards, tom lane

Thu, 24 Jul 2003 18:15:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: this is in plain text (row level locks) 
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> Sailesh Krishnamurthy <sailesh@cs.berkeley.edu> writes:
    >> We implemented a Shared Memory MemoryContext using OSSP libmm
    >> (used in Apache) for TelegraphCQ.

    Tom> How portable is that?  What kind of real-world performance do
    Tom> you get?

As portable as libmm I guess. We've only tried it on Linux and MacOS
X. I understand that the Apache guys use it - so it should be fairly
portable. 

http://www.ossp.org/pkg/lib/mm/

Actually I've also tried it successfully on SPARC Solaris. 

I believe that a user of our first TelegraphCQ release (that we did
not announce) tried it on FreeBSD. Sean Chittenden has tried it on
FreeBSD but I'm not sure if he built it and got it to work properly.

As for our SHMemoryContext code itself, it's pretty much a straight
copy of the code in aset.c and mctx.c, with appropriate changes where
we call the mm_alloc functions and also use mm_lock and mm_unlock for
protection. 

As for performance, I'm in the midst of a performance study, but it's
not a micro-benchmark of the shmem allocator. After I get some numbers
I'll start profiling our code.

One problem is that on some platforms the locking implementation uses
the file-system. This is not in our control and we just trust mm to do
the best possible. 

Given that we rely on shared query execution (we run multiple queries
in a single adaptive query plan) we don't have much choice - we must
rely on shared memory.

-- 
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh



24 Jul 2003 15:54:17 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: this is in plain text (row level locks)
>>>>> "Bruce" == Bruce Momjian <pgman@candle.pha.pa.us> writes:

    >> -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh

    Bruce>              ^^^^^^^^^^^^^^^ Watch out, that code from
    Bruce> Berkeley usually is a mess.  :-)

LOL ! 

That's why we release the code - in the hope of getting real hackers
playing with it :-)

    Bruce> Seriously, though, it would be good to see what you guys
    Bruce> are up to.

Our code is based on 7.3.2 pgsql and available at:
 
          http://telegraph.cs.berkeley.edu/telegraphcq 

If the specific shm stuff is interesting, I can easily take it out and
submit it. If you prefer to just look at the code in the tarball
that's fine too. 

Or you can browse our cvs repository at:

   http://triplerock.cs.berkeley.edu:8080/viewcvs/viewcvs.cgi/

Look for shmctx.c and shmset.c in src/backend/utils/mmgr 

-- 
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh



24 Jul 2003 16:00:06 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: this is in plain text (row level locks)

I was thinking of adding to TODO:
	
	* Allow shared row locks for referential integrity

but how is that different from:

	* Implement dirty reads and use them in RI triggers


---------------------------------------------------------------------------

Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > It may be best to have a locking manager run as a separate process.
> > That way it could store locks in ram or spill over to disk.
> 
> Hmm, that might be workable.  We could imagine that in place of the 
> HEAP_MARKED_FOR_UPDATE status bit, we have a "this row is possibly
> locked" hint bit.  Only if you see the bit set do you need to query
> the lock manager.  If the answer comes back that no lock is held,
> you can clear the bit --- so no need for any painful "undo" stuff
> after a crash, and no communication overhead in the normal case.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 31 Jul 2003 00:16:27 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: this is in plain text (row level locks)
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I was thinking of adding to TODO:
> 	* Allow shared row locks for referential integrity
> but how is that different from:
> 	* Implement dirty reads and use them in RI triggers

It'd be a completely different approach to solving the FK locking
problem.  I wouldn't think we'd do both.

Personally I'd feel more comfortable with a shared-lock approach, if we
could work out the scalability issues.  Dirty reads seem ... well ...
dirty.

			regards, tom lane

Thu, 31 Jul 2003 00:20:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: this is in plain text (row level locks) 
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I was thinking of adding to TODO:
> > 	* Allow shared row locks for referential integrity
> > but how is that different from:
> > 	* Implement dirty reads and use them in RI triggers
> 
> It'd be a completely different approach to solving the FK locking
> problem.  I wouldn't think we'd do both.
> 
> Personally I'd feel more comfortable with a shared-lock approach, if we
> could work out the scalability issues.  Dirty reads seem ... well ...
> dirty.

TODO updated:

	* Implement dirty reads or shared locks and use them in RI
	  triggers


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 31 Jul 2003 00:49:55 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: this is in plain text (row level locks)
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> Personally I'd feel more comfortable with a shared-lock
    Tom> approach, if we could work out the scalability issues.  Dirty
    Tom> reads seem ... well ...  dirty.

Tom

I was going to do some experiments to measure the costs of our shared
memory MemoryContext.

Is there something specific you'd be interested in ? My first goal is
mainly to measure the amount of time it takes to allocate and
deallocate shared memory in the contested and uncontested cases.

-- 
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh



30 Jul 2003 22:57:10 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: this is in plain text (row level locks)