Fwd: Want to acquire lock on tables where primary of one table is foreign key on othere

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Fwd: Want to acquire lock on tables where primary of one table is foreign key on othere

Abhishek Tripathi


---------- Forwarded message ---------
From: Abhishek Tripathi <[hidden email]>
Date: Thu, Oct 11, 2018 at 3:18 PM
Subject: Want to acquire lock on tables where primary of one table is foreign key on othere
To: <[hidden email]>


Dear Concerned Person,
 I write this email to get the information about locking which I am not getting from anywhere.
Actually I have acquired a "Select for Update" on a table whose id is refrence as a foreign key on another table So I want those table won't update until there is lock. Is it possible? Becuase postgres is acquiring lock but AccessShare Lock which allow to write on those table How I restrict this.



Thanks and Regards 
Abhishek
Reply | Threaded
Open this post in threaded view
|

Re: Want to acquire lock on tables where primary of one table is foreign key on othere

Alban Hertroys-4
On Thu, 11 Oct 2018 at 16:38, Abhishek Tripathi
<[hidden email]> wrote:
> Actually I have acquired a "Select for Update" on a table whose id is refrence as a foreign key on another table So I want those table won't update until there is lock. Is it possible? Becuase postgres is acquiring lock but AccessShare Lock which allow to write on those table How I restrict this.

For what purpose do you want that? What is inadequate about the lock
that Postgres acquires?

Table locks are very rarely what you want, as it blocks all concurrent
access to the entire table, while that is only necessary for a few
rarely used corner cases; a foreign key update is not among those.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Reply | Threaded
Open this post in threaded view
|

Re: Want to acquire lock on tables where primary of one table is foreign key on othere

Abhishek Tripathi
Thank you for your response. 

I am explaining my situation there is table A on which I have taken a row lock and the primary key of table A is related with table B as a foreign key so automatically I can not insert new row with that  foreign key  now the primary key of table B is used as foreign key in table C and insertion can be done on table C. I just want to lock table C also No insertion can be done on table C related to table B primary key.



On Thu, Oct 11, 2018 at 8:26 PM Alban Hertroys <[hidden email]> wrote:
On Thu, 11 Oct 2018 at 16:38, Abhishek Tripathi
<[hidden email]> wrote:
> Actually I have acquired a "Select for Update" on a table whose id is refrence as a foreign key on another table So I want those table won't update until there is lock. Is it possible? Becuase postgres is acquiring lock but AccessShare Lock which allow to write on those table How I restrict this.

For what purpose do you want that? What is inadequate about the lock
that Postgres acquires?

Table locks are very rarely what you want, as it blocks all concurrent
access to the entire table, while that is only necessary for a few
rarely used corner cases; a foreign key update is not among those.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
Reply | Threaded
Open this post in threaded view
|

Re: Want to acquire lock on tables where primary of one table is foreign key on othere

Alban Hertroys-4
In reply to this post by Alban Hertroys-4
You forgot to CC the list. Also, top-posting is generally not appreciated here.

> On Thu, Oct 11, 2018 at 8:26 PM Alban Hertroys <[hidden email]> wrote:
> On Thu, 11 Oct 2018 at 16:38, Abhishek Tripathi
> <[hidden email]> wrote:
> > Actually I have acquired a "Select for Update" on a table whose id is refrence as a foreign key on another table So I want those table won't update until there is lock. Is it possible? Becuase postgres is acquiring lock but AccessShare Lock which allow to write on those table How I restrict this.
>
> For what purpose do you want that? What is inadequate about the lock
> that Postgres acquires?
>
> Table locks are very rarely what you want, as it blocks all concurrent
> access to the entire table, while that is only necessary for a few
> rarely used corner cases; a foreign key update is not among those.

> On Fri, Oct 12, 2018 at 10:33 AM Abhishek Tripathi <[hidden email]> wrote:
> Thank you for your response.
>
> I am explaining my situation there is table A on which I have taken a row lock and the primary key of table A is related with table B as a foreign key so automatically I can not insert new row with that  foreign key  now the primary key of table B is used as foreign key in table C and insertion can be done on table C. I just want to lock table C also No insertion can be done on table C related to table B primary key.
>

> On 12 Oct 2018, at 8:08, Abhishek Tripathi <[hidden email]> wrote:
>
> My bad sorry actually there is updation-
>
> there is table A on which I have taken a row lock and the primary key of table A is related with table B as a foreign key so automatically I can not insert new row with that  foreign key on table B that is fine now  table C any  insertion can be done on table C. I just want to lock table C also No insertion can be done on table C becuse table C primary key is related to table B as a foreign key of B.


So your tables (simplified) are something like this?:
create table A (id primary key)
create table B (id primary key, a_id references A (id))
create table C (id primary key, b_id references B (id))

And you insert a new value for b_id in C? That's not possible, unless that value exists in table B, which in turn can't exist unless it's a_id exists in table A.
That's how foreign key constraints are designed, you don't need to do manual locking for that to work.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.