Thread: getting lock information

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

>"Himanshu Baweja" <himanshubaweja@yahoo.com> writes
>I wanted to see which tables/transaction have acquired or are waiting for
which locks....
>as far as i know there are two ways to do it....
>
>1) pg_locks ::: need to write trigger... and have high overhead...
>

"select * from pg_locks" has trivial impact on the server. pg_locks is a
view

test=# \d pg_locks;
    View "pg_catalog.pg_locks"
   Column    |  Type   | Modifiers
-------------+---------+-----------
 relation    | oid     |
 database    | oid     |
 transaction | xid     |
 pid         | integer |
 mode        | text    |
 granted     | boolean |
View definition:
 SELECT l.relation, l."database", l."transaction", l.pid, l."mode",
l.granted
   FROM pg_lock_status() l(relation oid, "database" oid, "transaction" xid,
pid
integer, "mode" text, granted boolean);

and it retrives data from server internal lock data structure via
pg_lock_status() function.

What do you mean by "write trigger"?

>2) trace_locks,trace_lwlocks ... etc etc.... ::: well for this my server
says undefined parameters... >so as far as i know i need to rebuild the
server with some flag...
>

Yes, trace_locks is used when LOCK_DEBUG is defined, so you got to rebuild
your server.

Regards,
Qingqing

"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
>> "Himanshu Baweja" <himanshubaweja@yahoo.com> writes
>> 1) pg_locks ::: need to write trigger... and have high overhead...

> What do you mean by "write trigger"?

I'm sure he's imagining that he can create a trigger on pg_locks and
thereby capture lock-related events :-(

            regards, tom lane

Himanshu Baweja wrote:
> is there any other better way by which i can get a list of locks
> acquired and waited for during entire run of my application

Hacking the backend would be the easiest route, I think. Why do you need
this information -- what are you trying to do?

-Neil

hacking the backend... can u elaborate a little bit
more... i want to write a small utility which other
ppl can use to see when the locks are acquired etc
etc... so that they can optimise their application
using that information... in my application we have
have 4 processes doing almost the same thing ie they
do the same quey at exactly the same time... and if i
can the complete lock information... i can change the
application so that they dont have to wait for each
other....

thx
Himanshu

--- Neil Conway <neilc@samurai.com> wrote:
> Himanshu Baweja wrote:
> > is there any other better way by which i can get a
> list of locks
> > acquired and waited for during entire run of my
> application
>
> Hacking the backend would be the easiest route, I
> think. Why do you need
> this information -- what are you trying to do?
>
> -Neil
>



__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new Resources site
http://smallbusiness.yahoo.com/resources/

> >> 1) pg_locks ::: need to write trigger... and have
high overhead...
>
> > What do you mean by "write trigger"?
>
> I'm sure he's imagining that he can create a trigger
on pg_locks and
> thereby capture lock-related events :-(

well that was wht i was thinking.... got it now... i
will recompile with LOCK_DEBUG
thx
Himanshu


>
> regards, tom lane
>

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com