PostgreSQL Discussion - getting lock information

Author

getting lock information

Himanshu Baweja

2005-05-24, 1:23 pm

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...

2) trace_locks,trace_lw
locks ... 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...

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....

thx
Himanshu

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

Qingqing Zhou

2005-05-25, 3:23 am

>"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_lw
locks ... 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

Tom Lane

2005-05-25, 3:23 am

"Qingqing Zhou" <zhouqq@cs.toronto.edu> writes:
[color=darkred]
> 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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org

Neil Conway

2005-05-25, 3:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Himanshu Baweja

2005-05-25, 3:23 am

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:
> list of locks
> 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/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Himanshu Baweja

2005-05-25, 3:23 am

> >> 1) pg_locks ::: need to write trigger... and have
high overhead...
>
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Sponsored Links