Re: [GENERAL] Deadlocks

On Tue, Aug 22, 2017 at 9:42 AM, Martin Moore wrote: > Hi, I’m having issues with deadlocks. > > v9.6 on Debian Jessie. > > I have a number of ‘select’ functions which for logging purposes also call > another fn that inserts a record into a ‘logging’ table. > > However,

[GENERAL] Deadlocks

Hi, I’m having issues with deadlocks. v9.6 on Debian Jessie. I have a number of ‘select’ functions which for logging purposes also call another fn that inserts a record into a ‘logging’ table. However, even with only 1 or 2 users making very infrequent calls to the parent fn, deadlocks are

Re: [GENERAL] Deadlocks On Demand

On Sun, Jan 3, 2010 at 5:43 AM, David Fetter da...@fetter.org wrote: Folks, I'm working on some SQL intended to expose lock conditions (deadlock, etc.), but to do this, I need to be able to create such conditions at will.  Rather than build an entire infrastructure from scratch, I'd like to

[GENERAL] Deadlocks On Demand

Folks, I'm working on some SQL intended to expose lock conditions (deadlock, etc.), but to do this, I need to be able to create such conditions at will. Rather than build an entire infrastructure from scratch, I'd like to know if there are any frameworks people are using to create such

Re: [GENERAL] Deadlocks caused by a foreign key constraint

On Thu, Aug 16, 2007 at 01:21:43AM -0400, Tom Lane wrote: Decibel! [EMAIL PROTECTED] writes: But... taking a quick look at RI_FKey_check in backend/utils/adt/ ri_triggers.c, I don't see it checking to see if the FK has changed, which seems odd. I would think that if the FK fields haven't

Re: [GENERAL] Deadlocks caused by a foreign key constraint

On Fri, Aug 10, 2007 at 09:38:36PM +0400, Dmitry Koterov wrote: Hello. I have a number of deadlock because of the foreign key constraint: Assume we have 2 tables: A and B. Table A has a field fk referenced to B.idas a foreign key constraint. -- transaction #1 BEGIN; ... INSERT INTO

Re: [GENERAL] Deadlocks caused by a foreign key constraint

No. I have tested all cases, the code I quoted is complete and minimal. All operations are non-blocking (count incrementation is non-blocking, insertion with a foreign key is non-blocking too), but it still generates a deadlock time to time. Deletion of the foreign key constraint completely

Re: [GENERAL] Deadlocks caused by a foreign key constraint

On Aug 15, 2007, at 1:27 PM, Dmitry Koterov wrote: I have tested all cases, the code I quoted is complete and minimal. All operations are non-blocking (count incrementation is non- blocking, insertion with a foreign key is non-blocking too), but it still generates a deadlock time to time.

Re: [GENERAL] Deadlocks caused by a foreign key constraint

Decibel! [EMAIL PROTECTED] writes: But... taking a quick look at RI_FKey_check in backend/utils/adt/ ri_triggers.c, I don't see it checking to see if the FK has changed, which seems odd. I would think that if the FK fields haven't changed that there's no need to perform the check. You

[GENERAL] Deadlocks caused by a foreign key constraint

Hello. I have a number of deadlock because of the foreign key constraint: Assume we have 2 tables: A and B. Table A has a field fk referenced to B.idas a foreign key constraint. -- transaction #1 BEGIN; ... INSERT INTO A(x, y, fk) VALUES (1, 2, 666); ... END; -- transaction #2 BEGIN; UPDATE

Re: Intervals (was: [GENERAL] DeadLocks..., DeadLocks...)

Tom Allison wrote: I have a question though. I noticed a particular format for identifying dates like: now()-'3 days'::interval; What's '::interval' and why should I use it? Intervals are convenient, simply said. They are a special type dealing with date calculations relative to a given

Re: [GENERAL] DeadLocks...

tom wrote: I found a problem with my application which only occurs under high loads (isn't that always the case?). insert into tokens (token) select [...] This works 99% of the time. But everyone once in a long while it seems that I hit simultaneaous execute() statements that

Re: [GENERAL] DeadLocks..., DeadLocks...

The insert is deadlocking against the update delete. The problem is that the insert has to lock the records to be sure they aren't deleted. This prevents the update for updating them. But the update has already updated some other records which the insert hasn't referred to yet. When the insert

Re: [GENERAL] DeadLocks..., DeadLocks...

Gregory Stark wrote: The insert is deadlocking against the update delete. The problem is that the insert has to lock the records to be sure they aren't deleted. This prevents the update for updating them. But the update has already updated some other records which the insert hasn't referred to

Re: [GENERAL] DeadLocks..., DeadLocks...

Tom Allison [EMAIL PROTECTED] writes: The other approach would be to use an external file to queue these updates and run them from a crontab. Something like: ... and then run a job daily to read all these in to a hash (to make them unique values) and then run one SQL statement at the end of

Re: [GENERAL] DeadLocks..., DeadLocks...

Tom Allison wrote: Terry Fielder wrote: My 2 cents: I used to get a lot of these sharelock problems. Users using different records, but same tables in different order. (apparently 7.x was not as good as 8.x at row level locking) I was advised to upgrade from 7.x to 8.x I did, and all

Re: [GENERAL] DeadLocks..., DeadLocks...

Gregory Stark wrote: Tom Allison [EMAIL PROTECTED] writes: The other approach would be to use an external file to queue these updates and run them from a crontab. Something like: and then run a job daily to read all these in to a hash (to make them unique values) and then run one SQL

[GENERAL] DeadLocks...

I found a problem with my application which only occurs under high loads (isn't that always the case?). snippets of perl... insert into tokens (token) select values.token from (values TOKEN_LIST_STRING) as values(token) left outer join tokens t using (token) where t.token_idx is null $sql =~

Re: [GENERAL] DeadLocks..., DeadLocks...

[EMAIL PROTECTED] writes: But everyone once in a long while it seems that I hit simultaneaous execute() statements that deadlock on the insertion. What version of Postgres is this and do you have any foreign key constraints or triggers on the table you're inserting into? Is that insert the

Re: [GENERAL] DeadLocks...

[EMAIL PROTECTED] wrote: I found a problem with my application which only occurs under high loads (isn't that always the case?). snippets of perl... insert into tokens (token) select values.token from (values TOKEN_LIST_STRING) as values(token) left outer join tokens t using (token) where

Re: [GENERAL] DeadLocks..., DeadLocks...

On 6/14/2007, Gregory Stark [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] writes: But everyone once in a long while it seems that I hit simultaneaous execute() statements that deadlock on the insertion. What version of Postgres is this and do you have any foreign key constraints or triggers

Re: [GENERAL] DeadLocks...

On 6/14/2007, Scott Marlowe [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: I found a problem with my application which only occurs under high loads (isn't that always the case?). snippets of perl... insert into tokens (token) select values.token from (values TOKEN_LIST_STRING) as

Re: [GENERAL] DeadLocks..., DeadLocks...

In response to [EMAIL PROTECTED]: On 6/14/2007, Gregory Stark [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] writes: But everyone once in a long while it seems that I hit simultaneaous execute() statements that deadlock on the insertion. What version of Postgres is this and do you

Re: [GENERAL] DeadLocks..., DeadLocks...

I'm still not precisely clear what's going on, it might help if you posted the actual schema and the deadlock message which lists the precise locks that deadlocked. Are any of the DML you mention on other tables on those tables with foreign key references to this one? It's impossible for two

Re: [GENERAL] DeadLocks..., DeadLocks...

Gregory Stark wrote: I'm still not precisely clear what's going on, it might help if you posted the actual schema and the deadlock message which lists the precise locks that deadlocked. Are any of the DML you mention on other tables on those tables with foreign key references to this one?

Re: [GENERAL] DeadLocks..., DeadLocks...

Gregory Stark wrote: I'm still not precisely clear what's going on, it might help if you posted the actual schema and the deadlock message which lists the precise locks that deadlocked. Are any of the DML you mention on other tables on those tables with foreign key references to this one?

Re: [GENERAL] DeadLocks..., DeadLocks...

Gregory Stark wrote: I'm still not precisely clear what's going on, it might help if you posted the actual schema and the deadlock message which lists the precise locks that deadlocked. Are any of the DML you mention on other tables on those tables with foreign key references to this one?

Re: [GENERAL] DeadLocks..., DeadLocks...

Tom Allison wrote: Gregory Stark wrote: I'm still not precisely clear what's going on, it might help if you posted the actual schema and the deadlock message which lists the precise locks that deadlocked. Are any of the DML you mention on other tables on those tables with foreign key

Re: [GENERAL] DeadLocks..., DeadLocks...

Terry Fielder wrote: My 2 cents: I used to get a lot of these sharelock problems. Users using different records, but same tables in different order. (apparently 7.x was not as good as 8.x at row level locking) I was advised to upgrade from 7.x to 8.x I did, and all those sharelock problems

Re: [GENERAL] DeadLocks..., DeadLocks...

My 2 cents: I used to get a lot of these sharelock problems. Users using different records, but same tables in different order. (apparently 7.x was not as good as 8.x at row level locking) I was advised to upgrade from 7.x to 8.x I did, and all those sharelock problems went away. Terry Terry

Re: [GENERAL] deadlocks in multiple-triggers environment

On Wed, Jun 08, 2005 at 05:45:45PM +0200, hubert depesz lubaczewski wrote: hi i have a stituation a situation where i have multiple tables, and multiple triggers on all of them. at least 1 or 2 triggers on at lease 4 different tables does updates to main cache table. Do say, are there

Re: [GENERAL] deadlocks in multiple-triggers environment

[snip] Do say, are there foreign keys on those tables? If there are, that may explain the deadlocks. This is a known problem, fixed in the development version, for which there is no complete Wow, that's a good news :-) Time to drop that nasty patch we're

Re: [GENERAL] deadlocks in multiple-triggers environment

On Thu, Jun 09, 2005 at 04:26:44PM +0200, Csaba Nagy wrote: [snip] Do say, are there foreign keys on those tables? If there are, that may explain the deadlocks. This is a known problem, fixed in the development version, for which there is no complete

[GENERAL] deadlocks in multiple-triggers environment

hi i have a stituation a situation where i have multiple tables, and multiple triggers on all of them. at least 1 or 2 triggers on at lease 4 different tables does updates to main cache table. now. i have tasks which involve simultaneously (from different machines even) modifying all of the

Re: [GENERAL] Deadlocks caused by referential integrity checks

SS == Stephan Szabo [EMAIL PROTECTED] writes: SS If transaction 1 inserts a child row that references row A, then SS transaction 2 does a child row that references row B and they both then go SS to do child rows that reference the other, in the current implementation, SS there's no way to change

[GENERAL] deadlocks - sharelocks on transactions

Hi, I have a stored procedure that is causing deadlocks when called multiple times synchronously. The odd issue is that the deadlock seems to be happening on different threads waiting for locks on transactions. What exactly is this transaction lock? ERROR: deadlock detected DETAIL: Process

Re: [GENERAL] deadlocks - sharelocks on transactions

On Wed, 7 Jan 2004, Tim McAuley wrote: Hi, I have a stored procedure that is causing deadlocks when called multiple times synchronously. The odd issue is that the deadlock seems to be happening on different threads waiting for locks on transactions. What exactly is this transaction lock?

Re: [GENERAL] deadlocks - sharelocks on transactions

Tim McAuley [EMAIL PROTECTED] writes: I have a stored procedure that is causing deadlocks when called multiple times synchronously. The odd issue is that the deadlock seems to be happening on different threads waiting for locks on transactions. What exactly is this transaction lock?

[GENERAL] deadlocks

Hi all! I'm having a very strange deadlock problem in PostgreSQL 7.3.2 under RedHat Linux. All connections to this database come from Tomcat's 4.1.24 connection pooling mechanism, and for some reason frequently I have lots of INSERTs in waiting state. But what they're writing for?? To me

Re: [GENERAL] deadlocks

On Tue, Jun 17, 2003 at 06:10:06PM -0300, Felipe Schnack wrote: Hi all! I'm having a very strange deadlock problem in PostgreSQL 7.3.2 under RedHat Linux. All connections to this database come from Tomcat's 4.1.24 connection pooling mechanism, and for some reason frequently I have

[GENERAL] DeadLocks

This is my first posting to this site, but it's my last resort. We're running 7.0.3. I have 20 servlets that makeuse of our postgres database. All of the programs are able to perform their assigned SQL tasks, but not on a consistent basis. Often and unpredictably, different programs