Thread: Need some help in postgres locking mechanism

Problem 1: how (and why) are you locking rows in table Y?

Problem 2: you cannot have a second process skip over locked rows. It
sounds to me like a job queue... what you want to do is make it work
*really* fast to mark the job as taken and commit that work so the
locks are released. Then go do the work on it. You do not want to hold
the lock the entire time the worker is running. You will likely want
another process somewhere that looks for abandoned jobs that are
started but not completed and have no worker actively working on them.

On Thu, Apr 3, 2014 at 3:19 PM, santhosh kumar
<kurasanthoshkumar@gmail.com> wrote:
> Hi
> I have two problems to discuss.an you please guide me how to proceed on
> this.
> problem 1:
> I have table X('a' is prmiary key) and table Y('b' is primary key).
> Table Y has ''a''(X primary key) as foreign key.I have one job which runs
> once in one hour.I want to lock the rows in table Y.but this will also lock
> table X rows to avoid concurrent actions.My table X is used by other jobs as
> well for updation.
> The solution what I thought is implement  KEY SHARE on 'a' column in table
> Y.That will help others jobs to update the corresponding rows in table X
> without any issue.
>
> My doubt is how to keep  key share lock on "a" column in table Y.by default
> lock is on column 'b' which is primary key of table Y. My table X is so
> huge.I dont want any other locks on it.
> we are using postgres 9.2.
>
> one more doubt:If I implement key share lock on 'a' column,will the default
> locks on table X and table Y removed or not?
>
> PLEASE PROVIDE SYNTAX AS WELL
>
> problem2:
> This is a different issue.I have a table Q('w' is primary key).When a job
> runs ,i want to lock some rows so that the other parallel job wont be
> considering this row.
>
> what is the simple and best lock I can implement on these rows?I want with
> NOWAIT option.
>
> kindly give solutions to above issues.I would be greatful for that.
>
>
>
> Thanks
> K.Santhosh

Hi,


On 2014-04-08 15:27, Vick Khera wrote:
[...]
> Problem 2: you cannot have a second process skip over locked rows.


In fact, you can: use "FOR UPDATE NOWAIT" and catch any errors.

e.g. SELECT * FROM jobqueue WHERE id=? FOR UPDATE NOWAIT



-hannes

On Tue, Apr 8, 2014 at 11:28 AM, Hannes Erven <hannes@erven.at> wrote:
> On 2014-04-08 15:27, Vick Khera wrote:
> [...]
>>
>> Problem 2: you cannot have a second process skip over locked rows.
>
>
>
> In fact, you can: use "FOR UPDATE NOWAIT" and catch any errors.
>
> e.g. SELECT * FROM jobqueue WHERE id=? FOR UPDATE NOWAIT

That's not really skipping over the locked rows and returning the next
row, that's bailing out and retrying.