Обсуждение: getting lock information
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_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...
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
>"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