pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDead lock
6+ messages / 5 participants
[nested] [flat]
* Dead lock
@ 2010-06-14 11:50 Elias Ghanem <e.ghanem@acteos.com>
0 siblings, 2 replies; 6+ messages in thread
From: Elias Ghanem @ 2010-06-14 11:50 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I have 2 data bases trying to perform an update query at the same time
on a same table in a third data base using db link.
I'm getting a dead lock exception:
ERROR: deadlock detected
DETAIL: Process 27305 waits for ShareLock on transaction 55575; blocked
by process 27304.
Process 27304 waits for ShareLock on transaction 55576; blocked by
process 27305.
HINT: See server log for query details.
Actually the folowing function is installed on 2 dbs DB1 and DB2. This
function issues an update query on DB3.
When this function is running simultaneously on DB1 and DB2, it produces
a dead lock making one of the functions (in DB1 or DB2) stop with the
above exception:
Is it normal? should'nt postgres be able to handle such situations, for
ex: let one transaction wait untill the other commits or rollback then
continue with the first transaction?
Is there a parameter that should be set in postgresql.conf to allow
handling of concurrent transaction...?
CREATE OR REPLACE FUNCTION TEST_DB_LINK(VARCHAR)
RETURNS VOID AS'
DECLARE
C INTEGER;
P ALIAS FOR $1;
DUMMY VARCHAR;
BEGIN
C:= 0;
LOOP
EXIT WHEN C > 15;
C:= C+1;
SELECT INTO DUMMY DBLINK_EXEC(''CONNECTION_STRING TO DB3',
''UPDATE IN_FICHE_PRODUIT SET VALIDE = 1'');
RAISE NOTICE ''%, %'', C,P;
END LOOP;
END;'
LANGUAGE 'plpgsql';
Thanks for your time.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Dead lock
@ 2010-06-14 12:54 Tom Molesworth <tom@audioboundary.com>
parent: Elias Ghanem <e.ghanem@acteos.com>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Molesworth @ 2010-06-14 12:54 UTC (permalink / raw)
To: pgsql-performance
On 14/06/10 12:50, Elias Ghanem wrote:
> SELECT INTO DUMMY DBLINK_EXEC(''CONNECTION_STRING TO DB3',
> ''UPDATE IN_FICHE_PRODUIT SET VALIDE = 1'');
If there's more than one value in that table, an explicit ORDER BY might
help (otherwise you could get the situation where query A will update in
the order 1,2,3 and query B will do 3,2,1 so neither will be able to get
the requested locks until the other query has finished).
Tom
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Dead lock
@ 2010-06-14 13:59 Andy Colson <andy@squeakycode.net>
parent: Elias Ghanem <e.ghanem@acteos.com>
1 sibling, 0 replies; 6+ messages in thread
From: Andy Colson @ 2010-06-14 13:59 UTC (permalink / raw)
To: Elias Ghanem <e.ghanem@acteos.com>; pgsql-performance
On 06/14/2010 06:50 AM, Elias Ghanem wrote:
> Hi all,
> I have 2 data bases trying to perform an update query at the same time
> on a same table in a third data base using db link.
> I'm getting a dead lock exception:
> ERROR: deadlock detected
> DETAIL: Process 27305 waits for ShareLock on transaction 55575; blocked
> by process 27304.
> Process 27304 waits for ShareLock on transaction 55576; blocked by
> process 27305.
> HINT: See server log for query details.
> Actually the folowing function is installed on 2 dbs DB1 and DB2. This
> function issues an update query on DB3.
> When this function is running simultaneously on DB1 and DB2, it produces
> a dead lock making one of the functions (in DB1 or DB2) stop with the
> above exception:
> Is it normal? should'nt postgres be able to handle such situations, for
> ex: let one transaction wait untill the other commits or rollback then
> continue with the first transaction?
> Is there a parameter that should be set in postgresql.conf to allow
> handling of concurrent transaction...?
>
> CREATE OR REPLACE FUNCTION TEST_DB_LINK(VARCHAR)
> RETURNS VOID AS'
> DECLARE
> C INTEGER;
> P ALIAS FOR $1;
> DUMMY VARCHAR;
> BEGIN
> C:= 0;
> LOOP
> EXIT WHEN C > 15;
> C:= C+1;
> SELECT INTO DUMMY DBLINK_EXEC(''CONNECTION_STRING TO DB3', ''UPDATE
> IN_FICHE_PRODUIT SET VALIDE = 1'');
> RAISE NOTICE ''%, %'', C,P;
> END LOOP;
> END;'
> LANGUAGE 'plpgsql';
>
> Thanks for your time.
I think PG is doing what you want.. if you think about it. You start two transactions at the same time. A transaction is defined as "do this set of operations, all of which must succeed or fail atomicly". One transaction cannot update the exact same row as another transaction because that would break the second transactions "must succeed" rule.
-Andy
^ permalink raw reply [nested|flat] 6+ messages in thread
* Fwd: Dead lock
@ 2010-06-14 15:36 Elias Ghanem <e.ghanem@acteos.com>
0 siblings, 1 reply; 6+ messages in thread
From: Elias Ghanem @ 2010-06-14 15:36 UTC (permalink / raw)
To: pgsql-performance
Hi,
Actually i guess the problem is related to the way PG uses to aquire
lock on the rows that will be updated.
Suppose the update query will affect 5 rows: A, B, C, D and E.
Apparently the folowing senario is happening:
1- Transaction1 locks row A
2- Trnasaction2 locks row B
3- Transaction1 updates row A
4- Tranasaction2 updates row B
5- Transaction1 *tries *to acquire lock on row B(and fail because
row B is still locked by transaction2)
6- Transaction2 *tries *to acquire lock on row A(and fail because
row A is still locked by transaction1)
Hence the dead lock.
Is this a plausible explanation of what is going on?
If yes, what can be done to avoid the dead lock?
Thanks again.
-------- Original Message --------
Subject: Dead lock
Date: Mon, 14 Jun 2010 14:50:43 +0300
From: Elias Ghanem <e.ghanem@acteos.com>
To: pgsql-performance@postgresql.org
Hi all,
I have 2 data bases trying to perform an update query at the same time
on a same table in a third data base using db link.
I'm getting a dead lock exception:
ERROR: deadlock detected
DETAIL: Process 27305 waits for ShareLock on transaction 55575; blocked
by process 27304.
Process 27304 waits for ShareLock on transaction 55576; blocked by
process 27305.
HINT: See server log for query details.
Actually the folowing function is installed on 2 dbs DB1 and DB2. This
function issues an update query on DB3.
When this function is running simultaneously on DB1 and DB2, it produces
a dead lock making one of the functions (in DB1 or DB2) stop with the
above exception:
Is it normal? should'nt postgres be able to handle such situations, for
ex: let one transaction wait untill the other commits or rollback then
continue with the first transaction?
Is there a parameter that should be set in postgresql.conf to allow
handling of concurrent transaction...?
CREATE OR REPLACE FUNCTION TEST_DB_LINK(VARCHAR)
RETURNS VOID AS'
DECLARE
C INTEGER;
P ALIAS FOR $1;
DUMMY VARCHAR;
BEGIN
C:= 0;
LOOP
EXIT WHEN C > 15;
C:= C+1;
SELECT INTO DUMMY DBLINK_EXEC(''CONNECTION_STRING TO DB3',
''UPDATE IN_FICHE_PRODUIT SET VALIDE = 1'');
RAISE NOTICE ''%, %'', C,P;
END LOOP;
END;'
LANGUAGE 'plpgsql';
Thanks for your time.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fwd: Dead lock
@ 2010-06-14 15:58 Dave Crooke <dcrooke@gmail.com>
parent: Elias Ghanem <e.ghanem@acteos.com>
0 siblings, 1 reply; 6+ messages in thread
From: Dave Crooke @ 2010-06-14 15:58 UTC (permalink / raw)
To: Elias Ghanem <e.ghanem@acteos.com>; +Cc: pgsql-performance
It's a standard (indeed, required) best practice of concurrent database
programming across any brand of database to ensure that multi-row
transactions always acquire the locks they use in a predictable order based
on row identities, e.g. for the classic banking debit-credit pair, doing
something like this (Java / JDBC, simplified for brevity and clarity):
PreparedStatement debit = conn.prepareStatement("update account set balance
= balance - ? where acc_no = ? and balance > ?");
debit.setLong(1, amount);
debit.setLong(2, debit_acct);
debit.setLong(3, amount);
PreparedStatement credit = conn.prepareStatement("update account set balance
= balance + ? where acc_no = ?");
credit.setLong(1, amount);
credit.setLong(2, credit_acct);
try {
// always acquire row locks in increasing account number order
conn.beginTransaction();
if (credit_acct < debit_acct) {
credit.executeUpdate();
if (debit.executeUpdate() < 1) throw new SQLException("Insufficient
balance");
}
else {
if (debit.executeUpdate() < 1) throw new SQLException("Insufficient
balance");
credit.executeUpdate();
}
}
catch (SQLException e) {
System.err.println("Oops. transaction failed: ", e.getMessage());
conn.rollback();
}
conn.commit();
If you're doing straight SQL bulk updates, then as someone suggested, you
could use an ORDER BY on a subquery, but I don't know if that is a
guarantee, if you're not actually displaying the results then the DB may be
technically allowed to optimize it out from underneath you. The only way to
be sure is a cursor / procedure.
In short, this boils down to learning more about database programming. PG is
performing as it should.
Cheers
Dave
On Mon, Jun 14, 2010 at 10:36 AM, Elias Ghanem <e.ghanem@acteos.com> wrote:
> Hi,
> Actually i guess the problem is related to the way PG uses to aquire lock
> on the rows that will be updated.
> Suppose the update query will affect 5 rows: A, B, C, D and E.
> Apparently the folowing senario is happening:
> 1- Transaction1 locks row A
> 2- Trnasaction2 locks row B
> 3- Transaction1 updates row A
> 4- Tranasaction2 updates row B
> 5- Transaction1 *tries *to acquire lock on row B(and fail because row
> B is still locked by transaction2)
> 6- Transaction2 *tries *to acquire lock on row A(and fail because row
> A is still locked by transaction1)
> Hence the dead lock.
> Is this a plausible explanation of what is going on?
> If yes, what can be done to avoid the dead lock?
> Thanks again.
>
>
>
> -------- Original Message -------- Subject: Dead lock Date: Mon, 14 Jun
> 2010 14:50:43 +0300 From: Elias Ghanem <e.ghanem@acteos.com><e.ghanem@acteos.com> To:
> pgsql-performance@postgresql.org
>
> Hi all,
> I have 2 data bases trying to perform an update query at the same time on a
> same table in a third data base using db link.
> I'm getting a dead lock exception:
> ERROR: deadlock detected
> DETAIL: Process 27305 waits for ShareLock on transaction 55575; blocked by
> process 27304.
> Process 27304 waits for ShareLock on transaction 55576; blocked by process
> 27305.
> HINT: See server log for query details.
> Actually the folowing function is installed on 2 dbs DB1 and DB2. This
> function issues an update query on DB3.
> When this function is running simultaneously on DB1 and DB2, it produces a
> dead lock making one of the functions (in DB1 or DB2) stop with the above
> exception:
> Is it normal? should'nt postgres be able to handle such situations, for
> ex: let one transaction wait untill the other commits or rollback then
> continue with the first transaction?
> Is there a parameter that should be set in postgresql.conf to allow
> handling of concurrent transaction...?
>
> CREATE OR REPLACE FUNCTION TEST_DB_LINK(VARCHAR)
> RETURNS VOID AS'
> DECLARE
> C INTEGER;
> P ALIAS FOR $1;
> DUMMY VARCHAR;
> BEGIN
> C:= 0;
> LOOP
> EXIT WHEN C > 15;
> C:= C+1;
> SELECT INTO DUMMY DBLINK_EXEC(''CONNECTION_STRING TO DB3', ''UPDATE
> IN_FICHE_PRODUIT SET VALIDE = 1'');
> RAISE NOTICE ''%, %'', C,P;
> END LOOP;
> END;'
> LANGUAGE 'plpgsql';
>
> Thanks for your time.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Fwd: Dead lock
@ 2010-06-14 20:44 Merlin Moncure <mmoncure@gmail.com>
parent: Dave Crooke <dcrooke@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Merlin Moncure @ 2010-06-14 20:44 UTC (permalink / raw)
To: Dave Crooke <dcrooke@gmail.com>; +Cc: Elias Ghanem <e.ghanem@acteos.com>; pgsql-performance
On Mon, Jun 14, 2010 at 11:58 AM, Dave Crooke <dcrooke@gmail.com> wrote:
> If you're doing straight SQL bulk updates, then as someone suggested, you could use an ORDER BY on a subquery, but I don't know if that is a guarantee, if you're not actually displaying the results then the DB may be technically allowed to optimize it out from underneath you. The only way to be sure is a cursor / procedure.
'order by' should be safe if you use SELECT...FOR UPDATE. update
doesn't have an order by clause. Using cursor/procedure vs a query
is not the material point; you have to make sure locks are acquired in
a regular way.
update foo set x=x where id in (select * from bar order by x) does
look dangerous.
I think:
update foo set x=x where id in (select * from bar order by x for update)
should be ok. I don't usually do it that way.
merlin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-06-14 20:44 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-14 11:50 Dead lock Elias Ghanem <e.ghanem@acteos.com>
2010-06-14 12:54 ` Tom Molesworth <tom@audioboundary.com>
2010-06-14 13:59 ` Andy Colson <andy@squeakycode.net>
2010-06-14 15:36 Fwd: Dead lock Elias Ghanem <e.ghanem@acteos.com>
2010-06-14 15:58 ` Dave Crooke <dcrooke@gmail.com>
2010-06-14 20:44 ` Merlin Moncure <mmoncure@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox