Thread: tablelevel and rowlevel locks

tablelevel and rowlevel locks

From
"Jenny -"
Date:
I am working on a project that involves displaying locking information about
each lock taken, whether it be a row level or  table leve llock.
When dealing with struct LOCK (src/include/storage) i have noticed that
postgreSQL creates a single LOCK  struct for each table in the  db. Like if
i acquire 2 seperate row level locks on 2 seperate rows, both these locks
are represented in the same struct LOCK datastructure . And if i acquire a
table level lock on this table, then all 3 locks information( 2 row level
locks and 1 table level lock) is recorded in the same LOCK datastructure. I
would think that there should be 3 different LOCK datastructres created in
postgreSQL for the above example.
IS there are reason why only one LOCK datastructures is created in such a
case?
for the purposes of my project i need to seperate out these 3 locks and
present them as 3 distinct locked objects. i was using the LockData(defined
in src/include/storage/lock.h ) datastructures for this, but if all 3 locks
are put into the same LOCK struct even in LOCKDATA then can someone help me
as to how else i can accomplish what i intent on doing?
thank you very much
Jenny

_________________________________________________________________
Compare Cable, DSL or Satellite plans: As low as $29.95.
https://broadband.msn.com


Re: tablelevel and rowlevel locks

From
Alvaro Herrera Munoz
Date:
On Thu, Sep 04, 2003 at 08:56:31AM -0700, Jenny - wrote:
> I am working on a project that involves displaying locking information
> about each lock taken, whether it be a row level or  table leve llock.
> When dealing with struct LOCK (src/include/storage) i have noticed that
> postgreSQL creates a single LOCK  struct for each table in the  db. Like if
> i acquire 2 seperate row level locks on 2 seperate rows, both these locks
> are represented in the same struct LOCK datastructure.

I think the locks would actually by represented by PROCLOCK structures.
The LOCK structures are for lockable objects, not for actual locks.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Vivir y dejar de vivir son soluciones imaginarias.
La existencia est� en otra parte" (Andre Breton)

Re: tablelevel and rowlevel locks

From
"Jenny -"
Date:
>On Thu, Sep 04, 2003 at 08:56:31AM -0700, Jenny - wrote:
> > I am working on a project that involves displaying locking information
> > about each lock taken, whether it be a row level or  table leve llock.
> > When dealing with struct LOCK (src/include/storage) i have noticed that
> > postgreSQL creates a single LOCK  struct for each table in the  db. Like
>if
> > i acquire 2 seperate row level locks on 2 seperate rows, both these
>locks
> > are represented in the same struct LOCK datastructure.
>
>I think the locks would actually by represented by PROCLOCK structures.
>The LOCK structures are for lockable objects, not for actual locks.
>

Well,from what i understand, PROCLOCK stores the TransactionID and the LOCK
its holding lock on ,so
how would PROCLOCK be holding the 'actual' lock as apposed to the lockable
objects?
thanks!

_________________________________________________________________
Use custom emotions -- try MSN Messenger 6.0!
http://www.msnmessenger-download.com/tracking/reach_emoticon


Re: tablelevel and rowlevel locks

From
Alvaro Herrera Munoz
Date:
On Thu, Sep 04, 2003 at 11:21:05AM -0700, Jenny - wrote:

> >I think the locks would actually by represented by PROCLOCK structures.
> >The LOCK structures are for lockable objects, not for actual locks.
>
> Well,from what i understand, PROCLOCK stores the TransactionID and the LOCK
> its holding lock on ,so how would PROCLOCK be holding the 'actual' lock as
> apposed to the lockable objects?

Huh... look at http://developer.postgresql.org/pdf/internalpics.pdf pages 61 and 63.
Maybe it's clearer than whatever I can say.  Note that "HOLDER" has been renamed
to "PROCLOCK".

Anyway, I think the LOCK structure represents something that can be locked.
The PROCLOCK struct represents that some process is holding a lock on said
object.  That may be the reason why you are seeing that a lock is held by more
than one process at the same time (while in fact some of them are probably
_waiting_ for the lock).

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"I dream about dreams about dreams", sang the nightingale
under the pale moon (Sandman)

Re: tablelevel and rowlevel locks

From
Tom Lane
Date:
[ pgsql-general removed from cc list, as this is quite inappropriate
there ]

"Jenny -" <nat_lazy@hotmail.com> writes:
> I am working on a project that involves displaying locking information about 
> each lock taken, whether it be a row level or  table leve llock.
> When dealing with struct LOCK (src/include/storage) i have noticed that 
> postgreSQL creates a single LOCK  struct for each table in the  db. Like if 
> i acquire 2 seperate row level locks on 2 seperate rows, both these locks 
> are represented in the same struct LOCK datastructure .

As has been pointed out to you several times already, the LOCK
structures aren't used for row-level locks.  The objects that you are
looking at represent table-level locks.  For example, afterBEGIN;SELECT * FROM foo WHERE id IN (1,2) FOR UPDATE;

there will be a table-level AccessShareLock on "foo" that was acquired
(and not released) by the SELECT statement as a whole.  If there
actually were rows matching the WHERE clause, the locks on them are
represented by modifying their tuple headers on-disk.  There is nothing
about individual rows in the LOCK table.

Now, if you have modified the code with the intention of creating LOCK
entries for row-level locks, then all I can say is you didn't do it
right.  The LockTag created to represent a row-level lock should be
distinct from a table-level LockTag (or a page-level LockTag for that
matter).  If it is, the hash table will definitely store it as a
separate object.
        regards, tom lane


Re: [GENERAL] tablelevel and rowlevel locks

From
Tom Lane
Date:
Alvaro Herrera Munoz <alvherre@dcc.uchile.cl> writes:
> Anyway, I think the LOCK structure represents something that can be locked.

Right.

> The PROCLOCK struct represents that some process is holding a lock on said
> object.

IIRC, a PROCLOCK is created as soon as some backend tries to lock some
lockable object.  So the PROCLOCK may only indicate that that backend is
waiting for a lock on that object, not that it already has one.

> That may be the reason why you are seeing that a lock is held by more
> than one process at the same time (while in fact some of them are probably
> _waiting_ for the lock).

Also keep in mind that we use a lot of sharable lock modes --- so it's
entirely likely that multiple processes actually do have locks on the
same object.  That's not wrong if their lock modes don't conflict.

            regards, tom lane