Thread: Non returning Transactions/Many Locks in Postgres 9.0.4 and 9.0.1

Hello!

I am currently having a recurring Locking problem on my Postgres 9.0.4
Database. I had the same Problem on 9.0.1 and updated to 9.0.4 then.
It worked fine for a while and just resurfaced.

Suddenly it seems as though there is some kind of "deadlock" in the
database, which prevents my client from getting results for the open
transactions, resulting in the system to stop delivering content.

Something seems to trigger a lot of simultaneous locks and i cannot
identity the problem at the moment.

I also attached my pg_locks. It contains a lot of locks on my mainly
used table with "AccessShareLock". Is there any way to get more
information on the Locks and transactions that are currently being
processed?

Restarting my Application closes the open transactions, which are
showing the status "in transaction" in my pg-admin and solves the
problem for a while ... until it returns and I have to once again
restart due to the locks.

Can anyone point me in the right direction how i can find out more and
close in on the Problem?

Best regards
Manuel

Here's my pg_locks at the time of occurrence:


locktype|database|relation|page|tuple|virtualxid|transactionid|classid|objid|objsubid|virtualtransaction|pid|mode|granted
relation|3285581|3287328||||||||15/240971|13069|AccessShareLock|t
relation|3285581|3287327||||||||40/15748|9861|AccessShareLock|t
relation|3285581|3287338||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287324||||||||52/1139|24505|AccessShareLock|t
relation|3285581|3287018||||||||17/229172|22731|AccessShareLock|t
relation|3285581|3286559||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287328||||||||48/5147|22764|AccessShareLock|t
relation|3285581|3287322||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287338||||||||31/97721|25450|AccessShareLock|t
relation|3285581|3287025||||||||20/186606|13670|AccessShareLock|t
relation|3285581|3287324||||||||38/25132|6677|AccessShareLock|t
relation|3285581|3287292||||||||37/26211|13071|AccessShareLock|t
virtualxid|||||33/77701|||||33/77701|24731|ExclusiveLock|t
relation|3285581|3287325||||||||38/25132|6677|AccessShareLock|t
relation|3285581|3287333||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3287322||||||||15/240971|13069|AccessShareLock|t
relation|3285581|3287336||||||||3/385650|2987|AccessShareLock|t
relation|3285581|3287334||||||||40/15748|9861|AccessShareLock|t
relation|3285581|3287018||||||||20/186606|13670|AccessShareLock|t
relation|3285581|3287328||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287325||||||||52/1139|24505|AccessShareLock|t
virtualxid|||||40/15748|||||40/15748|9861|ExclusiveLock|t
relation|3285581|3287333||||||||34/86125|16273|AccessShareLock|t
relation|3285581|3287322||||||||48/5147|22764|AccessShareLock|t
relation|3285581|3287332||||||||40/15748|9861|AccessShareLock|t
relation|3285581|3287338||||||||35/59028|22766|AccessShareLock|t
relation|3285581|3287025||||||||17/229172|22731|AccessShareLock|t
relation|3285581|3287336||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287337||||||||40/15748|9861|AccessShareLock|t
relation|3285581|3287328||||||||41/10255|9862|AccessShareLock|t
relation|3285581|3287329||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287322||||||||43/8183|19801|AccessShareLock|t
relation|3285581|3287324||||||||3/385650|2987|AccessShareLock|t
relation|3285581|3287334||||||||6/274649|16589|AccessShareLock|t
relation|3285581|3287018||||||||34/86125|16273|AccessShareLock|t
relation|3285581|3287329||||||||31/97721|25450|AccessShareLock|t
virtualxid|||||23/193019|||||23/193019|22531|ExclusiveLock|t
relation|3285581|3287333||||||||20/186606|13670|AccessShareLock|t
relation|3285581|3287332||||||||6/274649|16589|AccessShareLock|t
relation|3285581|3287324||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287337||||||||6/274649|16589|AccessShareLock|t
relation|3285581|3287018||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3287328||||||||43/8183|19801|AccessShareLock|t
relation|3285581|3287327||||||||6/274649|16589|AccessShareLock|t
relation|3285581|3287325||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287322||||||||41/10255|9862|AccessShareLock|t
relation|3285581|3287025||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3286953||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287336||||||||52/1139|24505|AccessShareLock|t
relation|3285581|3287325||||||||3/385650|2987|AccessShareLock|t
relation|3285581|3287329||||||||35/59028|22766|AccessShareLock|t
relation|3285581|3287333||||||||17/229172|22731|AccessShareLock|t
relation|3285581|3287025||||||||34/86125|16273|AccessShareLock|t
relation|3285581|3287336||||||||38/25132|6677|AccessShareLock|t
relation|3285581|10985||||||||16/176080|28421|AccessShareLock|t
relation|3285581|3287324||||||||33/77701|24731|AccessShareLock|t
relation|3285581|3287330||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287329||||||||40/15748|9861|AccessShareLock|t
relation|3285581|3287018||||||||23/193019|22531|AccessShareLock|t
relation|3285581|3287025||||||||23/193019|22531|AccessShareLock|t
relation|3285581|3287330||||||||48/5147|22764|AccessShareLock|t
relation|3285581|3287325||||||||33/77701|24731|AccessShareLock|t
relation|3285581|3287338||||||||6/274649|16589|AccessShareLock|t
relation|3285581|3287330||||||||15/240971|13069|AccessShareLock|t
virtualxid|||||12/237843|||||12/237843|15407|ExclusiveLock|t
relation|3285581|3287327||||||||31/97721|25450|AccessShareLock|t
relation|3285581|3287334||||||||35/59028|22766|AccessShareLock|t
relation|3285581|3287327||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287338||||||||40/15748|9861|AccessShareLock|t
relation|3285581|3287332||||||||35/59028|22766|AccessShareLock|t
relation|3285581|3287330||||||||43/8183|19801|AccessShareLock|t
relation|3285581|3287337||||||||35/59028|22766|AccessShareLock|t
relation|3285581|3287327||||||||35/59028|22766|AccessShareLock|t
relation|3285581|3287333||||||||23/193019|22531|AccessShareLock|t
relation|3285581|3287332||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287336||||||||33/77701|24731|AccessShareLock|t
relation|3285581|3287337||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287334||||||||31/97721|25450|AccessShareLock|t
relation|3285581|3287329||||||||6/274649|16589|AccessShareLock|t
relation|3285581|3287332||||||||31/97721|25450|AccessShareLock|t
relation|3285581|3286589||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287330||||||||41/10255|9862|AccessShareLock|t
relation|3285581|3287337||||||||31/97721|25450|AccessShareLock|t
relation|3285581|3287334||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287328||||||||40/15748|9861|AccessShareLock|t
relation|3285581|3287327||||||||15/240971|13069|AccessShareLock|t
relation|3285581|3287332||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287324||||||||34/86125|16273|AccessShareLock|t
relation|3285581|3287336||||||||20/186606|13670|AccessShareLock|t
relation|3285581|3287337||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287018||||||||3/385650|2987|AccessShareLock|t
relation|3285581|3287327||||||||48/5147|22764|AccessShareLock|t
relation|3285581|3287324||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3287334||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287018||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287325||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3287333||||||||38/25132|6677|AccessShareLock|t
relation|3285581|3287322||||||||40/15748|9861|AccessShareLock|t
relation|3285581|3287332||||||||48/5147|22764|AccessShareLock|t
relation|3285581|3287025||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287336||||||||17/229172|22731|AccessShareLock|t
relation|3285581|3287337||||||||48/5147|22764|AccessShareLock|t
relation|3285581|3287334||||||||15/240971|13069|AccessShareLock|t
relation|3285581|3287327||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287325||||||||34/86125|16273|AccessShareLock|t
relation|3285581|3287333||||||||52/1139|24505|AccessShareLock|t
relation|3285581|3287332||||||||15/240971|13069|AccessShareLock|t
relation|3285581|3287025||||||||3/385650|2987|AccessShareLock|t
relation|3285581|3287337||||||||15/240971|13069|AccessShareLock|t
relation|3285581|3287334||||||||48/5147|22764|AccessShareLock|t
relation|3285581|3287327||||||||41/10255|9862|AccessShareLock|t
relation|3285581|3287322||||||||6/274649|16589|AccessShareLock|t
relation|3285581|3287324||||||||17/229172|22731|AccessShareLock|t
virtualxid|||||38/25132|||||38/25132|6677|ExclusiveLock|t
relation|3285581|3287334||||||||43/8183|19801|AccessShareLock|t
relation|3285581|3287018||||||||52/1139|24505|AccessShareLock|t
relation|3285581|3287325||||||||20/186606|13670|AccessShareLock|t
relation|3285581|3287332||||||||43/8183|19801|AccessShareLock|t
relation|3285581|3287330||||||||35/59028|22766|AccessShareLock|t
relation|3285581|3287337||||||||43/8183|19801|AccessShareLock|t
relation|3285581|3287018||||||||38/25132|6677|AccessShareLock|t
relation|3285581|3287328||||||||6/274649|16589|AccessShareLock|t
relation|3285581|3287327||||||||43/8183|19801|AccessShareLock|t
relation|3285581|3287333||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287025||||||||38/25132|6677|AccessShareLock|t
relation|3285581|3287324||||||||20/186606|13670|AccessShareLock|t
relation|3285581|3287336||||||||34/86125|16273|AccessShareLock|t
relation|3285581|3287330||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287334||||||||41/10255|9862|AccessShareLock|t
relation|3285581|3287325||||||||17/229172|22731|AccessShareLock|t
virtualxid|||||15/240971|||||15/240971|13069|ExclusiveLock|t
relation|3285581|3287333||||||||3/385650|2987|AccessShareLock|t
relation|3285581|3287332||||||||41/10255|9862|AccessShareLock|t
relation|3285581|3287025||||||||52/1139|24505|AccessShareLock|t
virtualxid|||||49/2993|||||49/2993|22765|ExclusiveLock|t
relation|3285581|3287336||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3287330||||||||31/97721|25450|AccessShareLock|t
relation|3285581|3287337||||||||41/10255|9862|AccessShareLock|t
virtualxid|||||6/274649|||||6/274649|16589|ExclusiveLock|t
relation|3285581|3287329||||||||48/5147|22764|AccessShareLock|t
relation|3285581|3287329||||||||15/240971|13069|AccessShareLock|t
relation|3285581|3287338||||||||41/10255|9862|AccessShareLock|t
relation|3285581|3287329||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287333||||||||33/77701|24731|AccessShareLock|t
relation|3285581|3287338||||||||43/8183|19801|AccessShareLock|t
relation|3285581|3287336||||||||23/193019|22531|AccessShareLock|t
relation|3285581|3287330||||||||40/15748|9861|AccessShareLock|t
virtualxid|||||34/86125|||||34/86125|16273|ExclusiveLock|t
relation|3285581|3287328||||||||31/97721|25450|AccessShareLock|t
relation|3285581|3287322||||||||35/59028|22766|AccessShareLock|t
relation|3285581|3287338||||||||48/5147|22764|AccessShareLock|t
virtualxid|||||35/59028|||||35/59028|22766|ExclusiveLock|t
relation|3285581|3287018||||||||33/77701|24731|AccessShareLock|t
relation|3285581|3287328||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287329||||||||41/10255|9862|AccessShareLock|t
relation|3285581|3287338||||||||15/240971|13069|AccessShareLock|t
relation|3285581|3287324||||||||23/193019|22531|AccessShareLock|t
relation|3285581|3287330||||||||6/274649|16589|AccessShareLock|t
virtualxid|||||52/1139|||||52/1139|24505|ExclusiveLock|t
relation|3285581|3287328||||||||35/59028|22766|AccessShareLock|t
relation|3285581|3287325||||||||23/193019|22531|AccessShareLock|t
relation|3285581|3287322||||||||31/97721|25450|AccessShareLock|t
relation|3285581|3287338||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287329||||||||43/8183|19801|AccessShareLock|t
relation|3285581|3287322||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287025||||||||33/77701|24731|AccessShareLock|t
relation|3285581|3287325||||||||31/97721|25450|AccessShareLock|t
relation|3285581|3287322||||||||23/193019|22531|AccessShareLock|t
relation|3285581|3287330||||||||17/229172|22731|AccessShareLock|t
relation|3285581|3287325||||||||37/26211|13071|AccessShareLock|t
virtualxid|||||43/8183|||||43/8183|19801|ExclusiveLock|t
relation|3285581|3287324||||||||35/59028|22766|AccessShareLock|t
relation|3285581|3287328||||||||23/193019|22531|AccessShareLock|t
relation|3285581|3287325||||||||35/59028|22766|AccessShareLock|t
relation|3285581|3287329||||||||3/385650|2987|AccessShareLock|t
relation|3285581|3287338||||||||52/1139|24505|AccessShareLock|t
relation|3285581|3287324||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287330||||||||20/186606|13670|AccessShareLock|t
relation|3285581|3287226||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287329||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287338||||||||38/25132|6677|AccessShareLock|t
relation|3285581|3287324||||||||31/97721|25450|AccessShareLock|t
relation|3285581|3287332||||||||33/77701|24731|AccessShareLock|t
relation|3285581|3287336||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287330||||||||34/86125|16273|AccessShareLock|t
relation|3285581|3287337||||||||33/77701|24731|AccessShareLock|t
virtualxid|||||20/186606|||||20/186606|13670|ExclusiveLock|t
relation|3285581|3287336||||||||31/97721|25450|AccessShareLock|t
relation|3285581|3287330||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3287334||||||||33/77701|24731|AccessShareLock|t
relation|3285581|3287329||||||||52/1139|24505|AccessShareLock|t
relation|3285581|3287338||||||||3/385650|2987|AccessShareLock|t
virtualxid|||||22/224556|||||22/224556|24042|ExclusiveLock|t
relation|3285581|3286959||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287327||||||||33/77701|24731|AccessShareLock|t
relation|3285581|3287329||||||||38/25132|6677|AccessShareLock|t
relation|3285581|3287338||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287336||||||||35/59028|22766|AccessShareLock|t
relation|3285581|3287333||||||||41/10255|9862|AccessShareLock|t
relation|3285581|3287332||||||||3/385650|2987|AccessShareLock|t
relation|3285581|3287025||||||||15/240971|13069|AccessShareLock|t
relation|3285581|3287337||||||||3/385650|2987|AccessShareLock|t
relation|3285581|3287018||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287334||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287328||||||||20/186606|13670|AccessShareLock|t
virtualxid|||||31/97721|||||31/97721|25450|ExclusiveLock|t
relation|3285581|3287322||||||||17/229172|22731|AccessShareLock|t
relation|3285581|3287332||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287348||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287025||||||||48/5147|22764|AccessShareLock|t
relation|3285581|3287324||||||||6/274649|16589|AccessShareLock|t
relation|3285581|3287336||||||||40/15748|9861|AccessShareLock|t
relation|3285581|3287330||||||||23/193019|22531|AccessShareLock|t
relation|3285581|3287337||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287334||||||||3/385650|2987|AccessShareLock|t
relation|3285581|3287327||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287325||||||||6/274649|16589|AccessShareLock|t
relation|3285581|3287333||||||||43/8183|19801|AccessShareLock|t
relation|3285581|3287338||||||||33/77701|24731|AccessShareLock|t
relation|3285581|3287018||||||||48/5147|22764|AccessShareLock|t
relation|3285581|3287328||||||||17/229172|22731|AccessShareLock|t
relation|3285581|3287327||||||||3/385650|2987|AccessShareLock|t
relation|3285581|3287322||||||||20/186606|13670|AccessShareLock|t
relation|3285581|3287025||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287018||||||||15/240971|13069|AccessShareLock|t
relation|3285581|3287325||||||||40/15748|9861|AccessShareLock|t
relation|3285581|3287333||||||||15/240971|13069|AccessShareLock|t
relation|3285581|3287322||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3287332||||||||52/1139|24505|AccessShareLock|t
relation|3285581|3287025||||||||41/10255|9862|AccessShareLock|t
relation|3285581|3287337||||||||52/1139|24505|AccessShareLock|t
relation|3285581|3287334||||||||38/25132|6677|AccessShareLock|t
relation|3285581|3287333||||||||48/5147|22764|AccessShareLock|t
relation|3285581|3287322||||||||34/86125|16273|AccessShareLock|t
relation|3285581|3287332||||||||38/25132|6677|AccessShareLock|t
relation|3285581|3287337||||||||38/25132|6677|AccessShareLock|t
relation|3285581|3287018||||||||43/8183|19801|AccessShareLock|t
relation|3285581|3287334||||||||52/1139|24505|AccessShareLock|t
relation|3285581|3287328||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3287327||||||||38/25132|6677|AccessShareLock|t
relation|3285581|3287329||||||||33/77701|24731|AccessShareLock|t
relation|3285581|3287025||||||||43/8183|19801|AccessShareLock|t
relation|3285581|3287328||||||||34/86125|16273|AccessShareLock|t
relation|3285581|3287327||||||||52/1139|24505|AccessShareLock|t
relation|3285581|3287333||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287324||||||||40/15748|9861|AccessShareLock|t
relation|3285581|3287336||||||||6/274649|16589|AccessShareLock|t
relation|3285581|3287018||||||||41/10255|9862|AccessShareLock|t
relation|3285581|3287329||||||||20/186606|13670|AccessShareLock|t
relation|3285581|3287333||||||||31/97721|25450|AccessShareLock|t
virtualxid|||||48/5147|||||48/5147|22764|ExclusiveLock|t
relation|3285581|3287330||||||||3/385650|2987|AccessShareLock|t
relation|3285581|3287334||||||||23/193019|22531|AccessShareLock|t
relation|3285581|3287333||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287332||||||||23/193019|22531|AccessShareLock|t
relation|3285581|3287330||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287337||||||||23/193019|22531|AccessShareLock|t
virtualxid|||||41/10255|||||41/10255|9862|ExclusiveLock|t
relation|3285581|3287327||||||||23/193019|22531|AccessShareLock|t
relation|3285581|3287329||||||||17/229172|22731|AccessShareLock|t
relation|3285581|3287333||||||||35/59028|22766|AccessShareLock|t
relation|3285581|3287338||||||||34/86125|16273|AccessShareLock|t
virtualxid|||||17/229172|||||17/229172|22731|ExclusiveLock|t
relation|3285581|3287338||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3287046||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287338||||||||20/186606|13670|AccessShareLock|t
relation|3285581|3287025||||||||31/97721|25450|AccessShareLock|t
virtualxid|||||3/385650|||||3/385650|2987|ExclusiveLock|t
relation|3285581|3287330||||||||52/1139|24505|AccessShareLock|t
relation|3285581|3287322||||||||33/77701|24731|AccessShareLock|t
relation|3285581|3287025||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287330||||||||38/25132|6677|AccessShareLock|t
relation|3285581|3287018||||||||35/59028|22766|AccessShareLock|t
virtualxid|||||37/26211|||||37/26211|13071|ExclusiveLock|t
relation|3285581|3287224||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287329||||||||34/86125|16273|AccessShareLock|t
relation|3285581|3287338||||||||17/229172|22731|AccessShareLock|t
relation|3285581|3287025||||||||35/59028|22766|AccessShareLock|t
relation|3285581|3287018||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287328||||||||33/77701|24731|AccessShareLock|t
relation|3285581|3287329||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3287225||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287052||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287018||||||||31/97721|25450|AccessShareLock|t
relation|3285581|3286565||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287325||||||||41/10255|9862|AccessShareLock|t
relation|3285581|3287322||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287332||||||||17/229172|22731|AccessShareLock|t
relation|3285581|3287025||||||||40/15748|9861|AccessShareLock|t
relation|3285581|3287336||||||||48/5147|22764|AccessShareLock|t
relation|3285581|3287337||||||||17/229172|22731|AccessShareLock|t
relation|3285581|3287327||||||||20/186606|13670|AccessShareLock|t
relation|3285581|3287322||||||||3/385650|2987|AccessShareLock|t
relation|3285581|3287324||||||||43/8183|19801|AccessShareLock|t
virtualxid|||||16/176080|||||16/176080|28421|ExclusiveLock|t
relation|3285581|3287336||||||||15/240971|13069|AccessShareLock|t
relation|3285581|3287334||||||||17/229172|22731|AccessShareLock|t
relation|3285581|3286582||||||||37/26211|13071|AccessShareLock|t
relation|3285581|3287328||||||||49/2993|22765|AccessShareLock|t
relation|3285581|3287325||||||||43/8183|19801|AccessShareLock|t
relation|3285581|3287333||||||||6/274649|16589|AccessShareLock|t
relation|3285581|3287332||||||||20/186606|13670|AccessShareLock|t
relation|3285581|3287336||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287337||||||||20/186606|13670|AccessShareLock|t
relation|3285581|3287328||||||||3/385650|2987|AccessShareLock|t
relation|3285581|3287327||||||||17/229172|22731|AccessShareLock|t
relation|3285581|3287329||||||||23/193019|22531|AccessShareLock|t
relation|3285581|3287324||||||||41/10255|9862|AccessShareLock|t
relation|3285581|3287018||||||||40/15748|9861|AccessShareLock|t
relation|3285581|3287334||||||||20/186606|13670|AccessShareLock|t
relation|3285581|3287325||||||||15/240971|13069|AccessShareLock|t
relation|3285581|3287333||||||||40/15748|9861|AccessShareLock|t
relation|3285581|3287322||||||||38/25132|6677|AccessShareLock|t
relation|3285581|3287332||||||||34/86125|16273|AccessShareLock|t
relation|3285581|3287324||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287330||||||||33/77701|24731|AccessShareLock|t
relation|3285581|3287337||||||||34/86125|16273|AccessShareLock|t
relation|3285581|3287334||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3287325||||||||48/5147|22764|AccessShareLock|t
relation|3285581|3287322||||||||52/1139|24505|AccessShareLock|t
relation|3285581|3287332||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3287336||||||||41/10255|9862|AccessShareLock|t
relation|3285581|3287337||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3287018||||||||6/274649|16589|AccessShareLock|t
relation|3285581|3287334||||||||34/86125|16273|AccessShareLock|t
relation|3285581|3287328||||||||38/25132|6677|AccessShareLock|t
relation|3285581|3287327||||||||22/224556|24042|AccessShareLock|t
relation|3285581|3287025||||||||6/274649|16589|AccessShareLock|t
relation|3285581|3287324||||||||48/5147|22764|AccessShareLock|t
relation|3285581|3287328||||||||52/1139|24505|AccessShareLock|t
relation|3285581|3287327||||||||34/86125|16273|AccessShareLock|t
relation|3285581|3287325||||||||12/237843|15407|AccessShareLock|t
relation|3285581|3287338||||||||23/193019|22531|AccessShareLock|t
relation|3285581|3287324||||||||15/240971|13069|AccessShareLock|t
relation|3285581|3287336||||||||43/8183|19801|AccessShareLock|t

On Mon, Jun 6, 2011 at 11:20 AM, Tarabas <tarabas@tarabas.de> wrote:
> Hello!
>
> I am currently having a recurring Locking problem on my Postgres 9.0.4
> Database. I had the same Problem on 9.0.1 and updated to 9.0.4 then.
> It worked fine for a while and just resurfaced.
>
> Suddenly it seems as though there is some kind of "deadlock" in the
> database, which prevents my client from getting results for the open
> transactions, resulting in the system to stop delivering content.

[snip]


locktype|database|relation|page|tuple|virtualxid|transactionid|classid|objid|objsubid|virtualtransaction|pid|mode|granted
> relation|3285581|3287328||||||||15/240971|13069|AccessShareLock|t
> relation|3285581|3287327||||||||40/15748|9861|AccessShareLock|t
...[snip]

One thing that stands out here is that all the locks are 'granted' --
that means at least according to pg_locks your are not blocking in the
database.  This is evidence the problem is in your end, not on the
server side...
*) is your application threaded?
*) are you using a connection pooler? (if so, which one?)

merlin

On Mon, Jun 6, 2011 at 10:20 AM, Tarabas <tarabas@tarabas.de> wrote:
> Hello!
>
> I am currently having a recurring Locking problem on my Postgres 9.0.4
> Database. I had the same Problem on 9.0.1 and updated to 9.0.4 then.
> It worked fine for a while and just resurfaced.
>
> Suddenly it seems as though there is some kind of "deadlock" in the
> database, which prevents my client from getting results for the open
> transactions, resulting in the system to stop delivering content.
>
> Something seems to trigger a lot of simultaneous locks and i cannot
> identity the problem at the moment.
>
> I also attached my pg_locks. It contains a lot of locks on my mainly
> used table with "AccessShareLock". Is there any way to get more
> information on the Locks and transactions that are currently being
> processed?
>
> Restarting my Application closes the open transactions, which are
> showing the status "in transaction" in my pg-admin and solves the
> problem for a while ... until it returns and I have to once again
> restart due to the locks.
>
> Can anyone point me in the right direction how i can find out more and
> close in on the Problem?
>
> Best regards
> Manuel
>
> Here's my pg_locks at the time of occurrence:

Snip.  Those are ALL either AccessShareLock (which is very low level
and non-blocking) or virtual tx locks, which again don't block
anything but their own transaction.  Nothing there screams "locks!"
for a better view of locks and how they're blocking things you can use
the queries from here: http://wiki.postgresql.org/wiki/Lock_Monitoring

Hi Scott,

SM> Snip.  Those are ALL either AccessShareLock (which is very low level
SM> and non-blocking) or virtual tx locks, which again don't block
SM> anything but their own transaction.  Nothing there screams "locks!"
SM> for a better view of locks and how they're blocking things you can use
SM> the queries from here: http://wiki.postgresql.org/wiki/Lock_Monitoring

Thank you, I will try that. I am currently using Hibernate 3.2 as a
Database-Layer but with an older JDBC for 8.2, i will try updating
these components to more recent versions.

If the problem arises again i will hopefully be able to better
analyze, where the locking in my application occurs. It is in fact a
multi-threaded environment.

Best regards
Manuel

Hello,

the problem just resurfaced and the Wiki page dows not really help
very much.

When i look into the pg_stat_activity, i only see that the connections
all state "<IDLE> in transaction".

Is there any way to find out what the transaction is doing exactly to
be able to debug the Problem?

Best regards
Manuel


Tuesday, June 7, 2011, 5:50:13 PM, you wrote:

T> Hi Scott,

SM>> Snip.  Those are ALL either AccessShareLock (which is very low level
SM>> and non-blocking) or virtual tx locks, which again don't block
SM>> anything but their own transaction.  Nothing there screams "locks!"
SM>> for a better view of locks and how they're blocking things you can use
SM>> the queries from here: http://wiki.postgresql.org/wiki/Lock_Monitoring

T> Thank you, I will try that. I am currently using Hibernate 3.2 as a
T> Database-Layer but with an older JDBC for 8.2, i will try updating
T> these components to more recent versions.

T> If the problem arises again i will hopefully be able to better
T> analyze, where the locking in my application occurs. It is in fact a
T> multi-threaded environment.

T> Best regards
T> Manuel

On Wed, Jun 8, 2011 at 9:32 AM, Tarabas <tarabas@tarabas.de> wrote:
> Hello,
>
> the problem just resurfaced and the Wiki page dows not really help
> very much.
>
> When i look into the pg_stat_activity, i only see that the connections
> all state "<IDLE> in transaction".
>
> Is there any way to find out what the transaction is doing exactly to
> be able to debug the Problem?

The transactions aren't doing anything.  Your applciation began
transactions in your application but didn't commit them.  Classic
causes of this are:
1) straight up bugs (code branches that do not commit)
2) multi threaded code, especially when threads share connections
(basically a factory for #1 above)
3) busted connection poolers (php_pconnect)

merlin