Hi, I am trying to deal with a deadlock situation caused by foreign key references on insert and I was wondering if anyone knows what order the foreign keys are locked (or evaluated) in for a particular table? Deferring the locks is unfortunately not a good option for me...
Thanks,
Shawn
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html 10  2099 
On Mon, Sep 27, 2004 at 03:19:47PM -0400, Shawn Chisholm wrote:
Hi, I am trying to deal with a deadlock situation caused by foreign
key references on insert and I was wondering if anyone knows what
order the foreign keys are locked (or evaluated) in for a particular
table? Deferring the locks is unfortunately not a good option for me...
What do you mean by "what order the foreign keys are locked"? Can
you give us an example of what you're doing and what problem you're
trying to solve? As I mentioned in reply to your earlier message,
foreign key locking and the potential for deadlock were recently
brought up in pgsql-general: http://archives.postgresql.org/pgsql...9/msg00405.php http://archives.postgresql.org/pgsql...9/msg00442.php
My followup to that thread (the second link above) mentions somebody
else's suggestion for a shared lock on the foreign key, but as far
as I can tell, no such solution has been implemented as of 8.0.0beta3.
--
Michael Fuhr http://www.fuhr.org/~mfuhr/
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
Mike,
I'm confused. Where is the lock? Is it on the 1 record in the model table?
If so, why is that record locked? Is it possible in Postgresql to update
the primary key of a record?
--RY mi**@fuhr.org (Michael Fuhr) writes: On Mon, Sep 27, 2004 at 03:19:47PM -0400, Shawn Chisholm wrote:
Hi, I am trying to deal with a deadlock situation caused by foreign
key references on insert and I was wondering if anyone knows what
order the foreign keys are locked (or evaluated) in for a particular
table? Deferring the locks is unfortunately not a good option for me...
What do you mean by "what order the foreign keys are locked"? Can
you give us an example of what you're doing and what problem you're
trying to solve? As I mentioned in reply to your earlier message,
foreign key locking and the potential for deadlock were recently
brought up in pgsql-general:
http://archives.postgresql.org/pgsql...9/msg00405.php
http://archives.postgresql.org/pgsql...9/msg00442.php
My followup to that thread (the second link above) mentions somebody
else's suggestion for a shared lock on the foreign key, but as far
as I can tell, no such solution has been implemented as of 8.0.0beta3.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--
% Randy Yates % "And all that I can do
%% Fuquay-Varina, NC % is say I'm sorry,
%%% 919-577-9882 % that's the way it goes..."
%%%% <ya***@ieee.org > % Getting To The Point', *Balance of Power*, ELO http://home.earthlink.net/~yatescr
Randy Yates <ya***@ieee.org > writes: Mike,
I'm confused. Where is the lock? Is it on the 1 record in the model table?
If so, why is that record locked? Is it possible in Postgresql to update
the primary key of a record?
Let me also ask why this is a problem. It may be a lock situation but
it isn't a DEADlock situation. I.e., the second transaction will just
have to wait until the first completes, and the first should complete
in milliseconds on a reasonable computer. Right?
Or am I completely missing the boat?
--Randy
--RY
mi**@fuhr.org (Michael Fuhr) writes:
On Mon, Sep 27, 2004 at 03:19:47PM -0400, Shawn Chisholm wrote:
Hi, I am trying to deal with a deadlock situation caused by foreign
key references on insert and I was wondering if anyone knows what
order the foreign keys are locked (or evaluated) in for a particular
table? Deferring the locks is unfortunately not a good option for me...
What do you mean by "what order the foreign keys are locked"? Can
you give us an example of what you're doing and what problem you're
trying to solve? As I mentioned in reply to your earlier message,
foreign key locking and the potential for deadlock were recently
brought up in pgsql-general:
http://archives.postgresql.org/pgsql...9/msg00405.php
http://archives.postgresql.org/pgsql...9/msg00442.php
My followup to that thread (the second link above) mentions somebody
else's suggestion for a shared lock on the foreign key, but as far
as I can tell, no such solution has been implemented as of 8.0.0beta3.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
--
% Randy Yates % "And all that I can do
%% Fuquay-Varina, NC % is say I'm sorry,
%%% 919-577-9882 % that's the way it goes..."
%%%% <ya***@ieee.org > % Getting To The Point', *Balance of Power*, ELO
http://home.earthlink.net/~yatescr
--
% Randy Yates % "Rollin' and riding and slippin' and
%% Fuquay-Varina, NC % sliding, it's magic."
%%% 919-577-9882 %
%%%% <ya***@ieee.org > % 'Living' Thing', *A New World Record*, ELO http://home.earthlink.net/~yatescr
On Tue, Sep 28, 2004 at 01:30:08PM +0000, Randy Yates wrote: Randy Yates <ya***@ieee.org > writes:
I'm confused. Where is the lock? Is it on the 1 record in the model table?
Yes.
If so, why is that record locked? Is it possible in Postgresql to update
the primary key of a record?
When you insert a row that has a foreign key reference, PostgreSQL
does a SELECT FOR UPDATE on the referenced row in the foreign table;
the lock prevents other transactions from changing the referenced
row before this transaction completes. Unfortunately it also
prevents other transactions from acquiring a lock on the same row,
so those transactions will block until the transaction holding the
lock completes.
Let me also ask why this is a problem. It may be a lock situation but
it isn't a DEADlock situation. I.e., the second transaction will just
have to wait until the first completes, and the first should complete
in milliseconds on a reasonable computer. Right?
We don't know how long it will take for the first transaction to
complete -- it might be part of a lengthy process, so performance
might suffer. Also, there *is* the potential for deadlock. Take
the table definitions in this message: http://archives.postgresql.org/pgsql...9/msg00405.php
You can create a deadlock situation that raises an error, as shown
in this message: http://archives.postgresql.org/pgsql...9/msg00442.php
Here's what's happening:
* Transaction 1 acquires a lock on foreign key 1.
* Transaction 2 acquires a lock on foreign key 2.
* Transaction 1 attempts to acquire a lock on foreign key 2, but that
lock is already held by transaction 2 so transaction 1 blocks.
* Transaction 2 attempts to acquire a lock on foreign key 1, but that
lock is already held by transaction 1, so transaction 2 blocks.
Transaction 1 is now waiting for a lock held by transaction 2, and
transaction 2 is waiting for a lock held by transaction 1. Deadlock.
PostgreSQL recognizes this and raises an exception in one of the
transactions.
The blocking and potential for deadlock can be avoided by deferring
the foreign key constraints, but then foreign key violations won't
be detected until the transaction attempts to commit. For some
applications this might be a problem, especially if one wants to
take advantage of 8.0.0's savepoints (e.g., an application might
want to know if a foreign key constraint has been violated so it
can roll back only the offending statement).
--
Michael Fuhr http://www.fuhr.org/~mfuhr/
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Michael Fuhr wrote: On Tue, Sep 28, 2004 at 01:30:08PM +0000, Randy Yates wrote:
Randy Yates <ya***@ieee.org > writes:
I'm confused. Where is the lock? Is it on the 1 record in the model table?
Yes.
If so, why is that record locked? Is it possible in Postgresql to update
the primary key of a record?
When you insert a row that has a foreign key reference, PostgreSQL
does a SELECT FOR UPDATE on the referenced row in the foreign table;
the lock prevents other transactions from changing the referenced
row before this transaction completes. Unfortunately it also
prevents other transactions from acquiring a lock on the same row,
so those transactions will block until the transaction holding the
lock completes.
There are some proposal to have another kind of lock in order to avoid the
above. I hope soon.
Regards
Gaetano Mendola
Michael,
Thank you for your responses. Further questions below.
Michael Fuhr <mi**@fuhr.or g> writes: On Tue, Sep 28, 2004 at 01:30:08PM +0000, Randy Yates wrote: Randy Yates <ya***@ieee.org > writes: >
> I'm confused. Where is the lock? Is it on the 1 record in the model table?
Yes.
> If so, why is that record locked? Is it possible in Postgresql to update
> the primary key of a record?
When you insert a row that has a foreign key reference, PostgreSQL
does a SELECT FOR UPDATE on the referenced row in the foreign table;
the lock prevents other transactions from changing the referenced
row before this transaction completes. Unfortunately it also
prevents other transactions from acquiring a lock on the same row,
so those transactions will block until the transaction holding the
lock completes.
Well, yeah - sure it does. Given that the locking mechanism's
granularity is record-level, it MUST if it is to guarantee referential
integrity.
I don't see this as a problem with the database unless you want to
argue that the locking mechanism should have finer granularity. Given
the granularity, the problem must be solved in the application or
business rule logic, not the database.
Let me also ask why this is a problem. It may be a lock situation but
it isn't a DEADlock situation. I.e., the second transaction will just
have to wait until the first completes, and the first should complete
in milliseconds on a reasonable computer. Right?
We don't know how long it will take for the first transaction to
complete -- it might be part of a lengthy process, so performance
might suffer. Also, there *is* the potential for deadlock. Take
the table definitions in this message:
http://archives.postgresql.org/pgsql...9/msg00405.php
You can create a deadlock situation that raises an error, as shown
in this message:
http://archives.postgresql.org/pgsql...9/msg00442.php
Here's what's happening:
* Transaction 1 acquires a lock on foreign key 1.
* Transaction 2 acquires a lock on foreign key 2.
* Transaction 1 attempts to acquire a lock on foreign key 2, but that
lock is already held by transaction 2 so transaction 1 blocks.
* Transaction 2 attempts to acquire a lock on foreign key 1, but that
lock is already held by transaction 1, so transaction 2 blocks.
Transaction 1 is now waiting for a lock held by transaction 2, and
transaction 2 is waiting for a lock held by transaction 1. Deadlock.
PostgreSQL recognizes this and raises an exception in one of the
transactions.
The blocking and potential for deadlock can be avoided by deferring
the foreign key constraints, but then foreign key violations won't
be detected until the transaction attempts to commit.
This just defers the problem. Yeah, it may help in some situations, but
in either case the application level or business rule logic must decide
what to do since the conflict is still POSSIBLE.
In short, I don't see a problem with postgresql. The responsibility
is on the developer to handle such cases.
Or am I still confused?
--
% Randy Yates % "Though you ride on the wheels of tomorrow,
%% Fuquay-Varina, NC % you still wander the fields of your
%%% 919-577-9882 % sorrow."
%%%% <ya***@ieee.org > % '21st Century Man', *Time*, ELO http://home.earthlink.net/~yatescr
Michael,
Thank you for your responses. Further questions below.
Michael Fuhr <mi**@fuhr.or g> writes: On Tue, Sep 28, 2004 at 01:30:08PM +0000, Randy Yates wrote: Randy Yates <ya***@ieee.org > writes: >
> I'm confused. Where is the lock? Is it on the 1 record in the model table?
Yes.
> If so, why is that record locked? Is it possible in Postgresql to update
> the primary key of a record?
When you insert a row that has a foreign key reference, PostgreSQL
does a SELECT FOR UPDATE on the referenced row in the foreign table;
the lock prevents other transactions from changing the referenced
row before this transaction completes. Unfortunately it also
prevents other transactions from acquiring a lock on the same row,
so those transactions will block until the transaction holding the
lock completes.
Well, yeah - sure it does. Given that the locking mechanism's
granularity is record-level, it MUST if it is to guarantee referential
integrity.
I don't see this as a problem with the database unless you want to
argue that the locking mechanism should have finer granularity. Given
the granularity, the problem must be solved in the application or
business rule logic, not the database.
Let me also ask why this is a problem. It may be a lock situation but
it isn't a DEADlock situation. I.e., the second transaction will just
have to wait until the first completes, and the first should complete
in milliseconds on a reasonable computer. Right?
We don't know how long it will take for the first transaction to
complete -- it might be part of a lengthy process, so performance
might suffer. Also, there *is* the potential for deadlock. Take
the table definitions in this message:
http://archives.postgresql.org/pgsql...9/msg00405.php
You can create a deadlock situation that raises an error, as shown
in this message:
http://archives.postgresql.org/pgsql...9/msg00442.php
Here's what's happening:
* Transaction 1 acquires a lock on foreign key 1.
* Transaction 2 acquires a lock on foreign key 2.
* Transaction 1 attempts to acquire a lock on foreign key 2, but that
lock is already held by transaction 2 so transaction 1 blocks.
* Transaction 2 attempts to acquire a lock on foreign key 1, but that
lock is already held by transaction 1, so transaction 2 blocks.
Transaction 1 is now waiting for a lock held by transaction 2, and
transaction 2 is waiting for a lock held by transaction 1. Deadlock.
PostgreSQL recognizes this and raises an exception in one of the
transactions.
The blocking and potential for deadlock can be avoided by deferring
the foreign key constraints, but then foreign key violations won't
be detected until the transaction attempts to commit.
This just defers the problem. Yeah, it may help in some situations, but
in either case the application level or business rule logic must decide
what to do.
In short, I don't see a problem with postgresql. The responsibility
is on the developer to handle such cases.
--
% Randy Yates % "Watching all the days go by...
%% Fuquay-Varina, NC % Who are you and who am I?"
%%% 919-577-9882 % 'Mission (A World Record)',
%%%% <ya***@ieee.org > % *A New World Record*, ELO http://home.earthlink.net/~yatescr
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
On Tue, 28 Sep 2004, Randy Yates wrote: Michael Fuhr <mi**@fuhr.or g> writes:
On Tue, Sep 28, 2004 at 01:30:08PM +0000, Randy Yates wrote: Randy Yates <ya***@ieee.org > writes:
>
> I'm confused. Where is the lock? Is it on the 1 record in the model table?
Yes.
> If so, why is that record locked? Is it possible in Postgresql to update
> the primary key of a record?
When you insert a row that has a foreign key reference, PostgreSQL
does a SELECT FOR UPDATE on the referenced row in the foreign table;
the lock prevents other transactions from changing the referenced
row before this transaction completes. Unfortunately it also
prevents other transactions from acquiring a lock on the same row,
so those transactions will block until the transaction holding the
lock completes.
Well, yeah - sure it does. Given that the locking mechanism's
granularity is record-level, it MUST if it is to guarantee referential
integrity.
But it doesn't need to prevent other transactions that want to just see if
the row is there from continuing (as opposed to ones that want to actually
modify that row). We just simply don't have that lock currently.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match ss****@megazone .bigpanda.com (Stephan Szabo) writes: On Tue, 28 Sep 2004, Randy Yates wrote:
Michael Fuhr <mi**@fuhr.or g> writes:
> On Tue, Sep 28, 2004 at 01:30:08PM +0000, Randy Yates wrote:
>> Randy Yates <ya***@ieee.org > writes:
>> >
>> > I'm confused. Where is the lock? Is it on the 1 record in the model table?
>
> Yes.
>
>> > If so, why is that record locked? Is it possible in Postgresql to update
>> > the primary key of a record?
>
> When you insert a row that has a foreign key reference, PostgreSQL
> does a SELECT FOR UPDATE on the referenced row in the foreign table;
> the lock prevents other transactions from changing the referenced
> row before this transaction completes. Unfortunately it also
> prevents other transactions from acquiring a lock on the same row,
> so those transactions will block until the transaction holding the
> lock completes.
Well, yeah - sure it does. Given that the locking mechanism's
granularity is record-level, it MUST if it is to guarantee referential
integrity.
But it doesn't need to prevent other transactions that want to just see if
the row is there from continuing (as opposed to ones that want to actually
modify that row). We just simply don't have that lock currently.
I see the light. You mean it would be nice to be able to have a "LOCK-FOR-UPDATE-ONLY"
lock as well as a "LOCK-FOR-UPDATE-OR-READ" lock, but all you have now is
"LOCK-FOR-UPDATE-OR-READ" and that gets applied even when you don't care if
someone else reads the record?
--
% Randy Yates % "So now it's getting late,
%% Fuquay-Varina, NC % and those who hesitate
%%% 919-577-9882 % got no one..."
%%%% <ya***@ieee.org > % 'Waterfall', *Face The Music*, ELO http://home.earthlink.net/~yatescr This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Bhushit Joshipura |
last post by:
This post contains one question and one proposal.
A. May I know why order of evaluation of arguments is not specified in
C/C++?
I asked a question in comp.lang.c++ for the following possibility and
because the languages do not specify the order of evaluation, doing so
was an error.
int B::f ( int i, int j = i + 1 ) { // j defaults to i + 1
|
by: der |
last post by:
Hello all,
I've a question about order of evaluations in expressions that have && and
|| operators in them.
The question is: will the evalution go left-to-right, no matter what -- even
if the || operator is before the && operator? e,g,
In an expression like a = (z>x) || (x<0) && (z-y>9);
is it guaranteed that z>x will be checked first?
|
by: dragoncoder |
last post by:
Consider the following code.
#include <stdio.h>
int main()
{
int i =1;
printf("%d ,%d ,%d\n",i,++i,i++);
return 0;
}
|
by: silpau |
last post by:
hi,
i am a bit confused on expression evaluation order in expressions
involving unary increment.decrement operators along with binary
operators.
For example in the following expression
x += i + j + k++;
|
by: Rasjid |
last post by:
Hello,
I have just joined and this is my first post.
I have never been able to resolve the issue of order of evaluation in
C/C++ and the related issue of precedence of operators, use of
parentheses.
1) "The order of evaluation of subexpressions is determined by the
precedence and grouping of operators."
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
