Re: Tables Locks Quetion or Strictlly subsequent numbers

Kaloyan Iliev wrote:
> Hello All,
>
> I have such a question.
> I want to receive from the database subsequent numbers and I can't
> afford to miss one. There must not be any missing numbers.
> So the sequence is not good for me because if transaction rollback the
> there will be gaps.
>
> So I make a table with one row and the row contains one int.
> Every time I update the row in Serializable transaction level:
>
> update foo set lastvalue = lastvalue+1;
> select lastvalue from foo;
>
> This is my decision of the problem. But here is my next question.
> If two apllications try to take next number at the same time one of both
> transactions will abort.
> The one way is to catch the error and try again, but this is what I
> don't want to do.
> So is there a way to escape transaction error. I read about the locks
> and I think they can solve my problem.
>
> First I thick I must change my transaction Isolation Level to Read Commited.
> Then If I first lock (in ROW EXCLUSIVE mode) the table, then update and
> then read - will this solve my problem.
> And if two functions try to do this in the same time will the second
> transaction waith until it can lock the table and then without errors to
> take the next number?
>
> And my questions:
> 1. Should I change the transaction isolation level to Read Commited or
> Serializable transaction level is good enough (I prefer to work in
> Serializable transaction level)?

Going to Read Committed from Serializable would actually decrease the
level of isolation for your transaction. I'm not sure that's what you
want to do.

> 2. Is my algorithm correct and will it give me secure way to get
> subsequent numbers without gaps?
> 3. Can I use SELECT FOR UPDATE instead ot locks in this case?

Well, if the table has only one row, and that row is only for the
counter, you could use

BEGIN;
LOCK counter_tbl IN ACCESS EXCLUSIVE MODE;
UPDATE counter_tbl SET counter = counter + 1;
SELECT counter FROM counter_tbl;
COMMIT;

Which would cause your transactions to queue up when dealing with the
counter. You don't get rollbacks that way. :)

> 4. Can I change the transaction level back to Serializable after I get
> the number I want, without commiting the transaction?