Thread: Table locks

Table locks

From

Jake Stride

Date:

09 September 2004, 19:10:40

I have a need to have an auto increasing field, but I don't think I can use
the serial type.

I have a table similar to:

Jobs
====

Id (bigserial/unique)
Name(varchar)
Companyid(bigint)
Jobno(bigint)

Primary Key: name/companyid

Now what I want is to increase the job number by 1 every time I do an
insert. But it must effectively be a sequence for each companyid in the
table for example:

1/a job/1/1
2/another job/1/2
3/a job/2/1
4/something else/1/3
5/yet one more/2/2

There shouldn't be any gaps in the sequence jobno sequence either. Is there
an easy way to do this?

I thought of doing:

SELECT max(jobno) from jobs where companyid=1;

Then use the result as the job number, is there a way I can lock the table
to do this or a better way of achieving the required result.

Thanks

Jake

Re: Table locks

From

Jake Stride

Date:

09 September 2004, 19:55:43

On 9/9/04 4:52 pm, "bugant" <bugant@opinioni.net> wrote:

>> There shouldn't be any gaps in the sequence jobno sequence either. Is there
>> an easy way to do this?
>>
>> I thought of doing:
>>
>> SELECT max(jobno) from jobs where companyid=1;
> you could do something like this or using a lot of different sequence
> ... but I don't know if it's a clever idea ;)

I don't want lots of sequences, but how best would I go about locking the
table?

Thanks

Jake

>
> ciao,
> bugant.
>

Re: Table locks

From

Andreas Seltenreich

Date:

09 September 2004, 20:03:54

Jake Stride writes:

> I thought of doing:
>
> SELECT max(jobno) from jobs where companyid=1;
>
> Then use the result as the job number, is there a way I can lock the table
> to do this or a better way of achieving the required result.

I think SELECT FOR UPDATE should work fine here.

See: http://www.postgresql.org/docs/7.4/static/mvcc.html

HTH
Andreas

Re: Table locks

From

Tom Lane

Date:

09 September 2004, 20:52:36

Andreas Seltenreich <seltenreich@gmx.de> writes:
> Jake Stride writes:
>> I thought of doing:
>> SELECT max(jobno) from jobs where companyid=1;

> I think SELECT FOR UPDATE should work fine here.

Nope; he'll get something like

regression=# select max(unique1) from tenk1 for update;
ERROR:  SELECT FOR UPDATE is not allowed with aggregate functions

His best bet is probably

        BEGIN;
        LOCK TABLE jobs;
        SELECT max(jobno) from jobs where companyid=1;
        INSERT INTO jobs ...
        COMMIT;

This is pretty horrid from a concurrency point of view but I don't think
there's any other way to meet the "no gaps" requirement.

You could reduce the strength of the lock a bit, for instance
        LOCK TABLE jobs IN EXCLUSIVE MODE;
which would allow readers of the jobs table to proceed concurrently,
but not writers.  If you were willing to assume that all inserters into
jobs are cooperating by explicitly obtaining the correct lock, you
could reduce it to
        LOCK TABLE jobs IN SHARE UPDATE EXCLUSIVE MODE;
which is the lowest self-conflicting table lock type.  This would allow
unrelated updates to the jobs table to proceed concurrently too (though
not VACUUMs).  See

http://www.postgresql.org/docs/7.4/static/explicit-locking.html

            regards, tom lane

Re: Table locks

From

Jaime Casanova

Date:

05 October 2004, 00:15:34

 --- Tom Lane <tgl@sss.pgh.pa.us> escribió:
> Andreas Seltenreich <seltenreich@gmx.de> writes:
> > Jake Stride writes:
> >> I thought of doing:
> >> SELECT max(jobno) from jobs where companyid=1;
>
> > I think SELECT FOR UPDATE should work fine here.
>
> Nope; he'll get something like
>
> regression=# select max(unique1) from tenk1 for
> update;
> ERROR:  SELECT FOR UPDATE is not allowed with
> aggregate functions
>
> His best bet is probably
>
>         BEGIN;
>         LOCK TABLE jobs;
>         SELECT max(jobno) from jobs where companyid=1;
>         INSERT INTO jobs ...
>         COMMIT;
>
> This is pretty horrid from a concurrency point of
> view but I don't think
> there's any other way to meet the "no gaps"
> requirement.
>
> You could reduce the strength of the lock a bit, for
> instance
>         LOCK TABLE jobs IN EXCLUSIVE MODE;
> which would allow readers of the jobs table to
> proceed concurrently,
> but not writers.  If you were willing to assume that
> all inserters into
> jobs are cooperating by explicitly obtaining the
> correct lock, you
> could reduce it to
>         LOCK TABLE jobs IN SHARE UPDATE EXCLUSIVE MODE;
> which is the lowest self-conflicting table lock
> type.  This would allow
> unrelated updates to the jobs table to proceed
> concurrently too (though
> not VACUUMs).  See
>
>
http://www.postgresql.org/docs/7.4/static/explicit-locking.html
>
>             regards, tom lane
>

Hi,
Talking about lock tables there is a way to do a
select ... for update
and then a
update .. where current of ...

I think it require a select for update in a cursor.

Thanx in advance,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

Re: Table locks

From

Andreas

Date:

05 October 2004, 17:30:04

Tom Lane wrote:

>See
>http://www.postgresql.org/docs/7.4/static/explicit-locking.html
>
>

I saw.
Now - as a beginner in the transaction-locking stuff - I'm wondering
about the last paragraph.
"So long as no deadlock situation is detected, a transaction seeking
either a table-level or row-level lock will wait indefinitely for
conflicting locks to be released. This means it is a bad idea for
applications to hold transactions open for long periods of time (e.g.,
while waiting for user input)."

I'm working on an MsAccess application that uses PG as backend. Up until
recently I didn't bother for cuncurrency issues but I like to improve
things now that PG manages the data.

For this application I read records into forms and subforms.

If - by accident - 2 users open one record, say customer #123, and I
start a transaction on opening the form and SELECT ... FOR UPDATE to
read data, then the second user's application stalls until the first
COMMITs.

With SELECT without FOR UPDATE the UPDATEs of the first committing
transaction get overwritten.

I can't predict how long a user lets open a form. I just need to protect
the displayd data against updates in the meantime.

This must be a common problem regardless what client software is used.
Are there some guidelines on locks for forms ?

Regards
Andreas

Re: Table locks

From

Michel Albert

Date:

11 October 2004, 16:03:13

Reading this, I had the idea to put some kind of timeout into the form. This
would on the one hand solve your problem, but on the other hand, it would
force the user to enter data in the given time, which makes it difficult for
him/her to - let's say - look something up while editing the data.

You could then aswell simply look for inactivity of the user. I.e. reset the
timer everytime you detect mouse-movement or text-input. As you are working
in access, and not in a web-environment this should be easily possible.

I know it's not the perfect solution, but I have to say, I'm also not a PG
guru ;)

Personally, I would not like to implement a solution like that. Because one of
Schneidermans rules states that the _user_ should be in control of the
program. Not the other way around. The named solution clearly conflicts with
that rule!

On Tuesday 05 October 2004 13:31, Andreas wrote:
> Tom Lane wrote:
> >See
> >http://www.postgresql.org/docs/7.4/static/explicit-locking.html
>
> I saw.
> Now - as a beginner in the transaction-locking stuff - I'm wondering
> about the last paragraph.
> "So long as no deadlock situation is detected, a transaction seeking
> either a table-level or row-level lock will wait indefinitely for
> conflicting locks to be released. This means it is a bad idea for
> applications to hold transactions open for long periods of time (e.g.,
> while waiting for user input)."
>
> I'm working on an MsAccess application that uses PG as backend. Up until
> recently I didn't bother for cuncurrency issues but I like to improve
> things now that PG manages the data.
>
> For this application I read records into forms and subforms.
>
> If - by accident - 2 users open one record, say customer #123, and I
> start a transaction on opening the form and SELECT ... FOR UPDATE to
> read data, then the second user's application stalls until the first
> COMMITs.
>
> With SELECT without FOR UPDATE the UPDATEs of the first committing
> transaction get overwritten.
>
> I can't predict how long a user lets open a form. I just need to protect
> the displayd data against updates in the meantime.
>
> This must be a common problem regardless what client software is used.
> Are there some guidelines on locks for forms ?
>
>
> Regards
> Andreas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly

Attachment

msg-19448-12780.dat