Re: Tables Locks Quetion or Strictlly subsequent numbers

From: Kaloyan Iliev <news1(at)faith(dot)digsys(dot)bg>
To: Andrew Hammond <andrew(dot)george(dot)hammond(at)gmail(dot)com>, pgsql-novice(at)postgresql(dot)org
Subject: Re: Tables Locks Quetion or Strictlly subsequent numbers
Date: 2006-07-29 17:51:35
Message-ID: 44CBA027.3040508@faith.digsys.bg
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=windows-1251"
http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Hi,<br>
Thank you for your response.<br>
My questions about going into Read Commited is because in Serializable <br>
i wouldn't see the changes on the counter, made by other transactions,
if I am correct? <br>
And if one transaction increase the counter (If I am using table
locking in Serializable), <br>
what will the next one (in Serializable) do - try to take the same
value or will rollback?<br>
<br>
Thanks again,<br>
<br>
Kaloyan Iliev<br>
<br>
<br>
<br>
Andrew Hammond wrote:
<blockquote
cite="mid1154119912(dot)126276(dot)56810(at)i42g2000cwa(dot)googlegroups(dot)com"
type="cite">
<pre wrap="">Kaloyan Iliev wrote:
</pre>
<blockquote type="cite">
<pre wrap="">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)?
</pre>
</blockquote>
<pre wrap=""><!---->
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.

</pre>
<blockquote type="cite">
<pre wrap="">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?
</pre>
</blockquote>
<pre wrap=""><!---->
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. :)

</pre>
<blockquote type="cite">
<pre wrap="">4. Can I change the transaction level back to Serializable after I get
the number I want, without commiting the transaction?
</pre>
</blockquote>
<pre wrap=""><!---->

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

<a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/faq">http://www.postgresql.org/docs/faq</a>

</pre>
</blockquote>
<br>
</body>
</html>

Attachment Content-Type Size
unknown_filename text/html 3.5 KB

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Andrew Hammond 2006-07-29 18:03:21 Re: Tables Locks Quetion or Strictlly subsequent numbers
Previous Message Tom Lane 2006-07-29 04:48:47 Re: Schema Names