On Friday 23 November 2001 05:20 am, Andreas Pohl wrote:

> I can't see how using an id_table should avoid broken sequence nr. in a
> transaction context.

Your example below doesn't take in to account locking between transactions by
different users.

> simple test case: current nr=100;
> 1. User_A needs a nr so he occupy 101

In doing this, there is a row level lock on the id_table where it got the
next number from.

> 2. User_B needs a nr so he occupy 102

This user will block waiting for the above lock to go away. He can't get a
number until User_A has either committed his transaction or rolled it back.
This would be a very bad idea if your design had long life transactions but
mine doesn't.

> 3. User_B finishes transaction with 102
> 4. User_A failed so 101 is not used
> 5. User_C needs a nr so system is suggesting 101 (maybe nr is used or
> not...) 6. Meanwhile User_D will provide a report and miss 101

> Working with locking is not recommended in c/s environment. From theory

What??? Client/server is where you need locking. Its how you keep clients
from interfering with each other. Its called concurrency and I suggest you
read Chapter 14 of An Introduction to Database Systems by Date if you want to
understand it.

The concurrency problem that I'm taking advantage of is called the
uncommitted dependency problem. The problem is causes when two transactions
try to update the same data. Here is a timeline:

Transaction A time Transaction B

t1 Update P
Retrieve P t2
t3 Rollback

The problem is that Transaction A now has retrieved tuple P which has values
that no longer exist. This is a common problem with client/server databases
and they all have to handle it in some way. The majority of them use locking
to accomplish this. When Transaction B updates P, it gets a exclusive lock
on that tuple. When Transaction A tries to retrieve P, it tries first to get
a shared lock on P. This lock request blocks until the point where
Transaction B does the rollback which releases its exclusive lock on P. At
that point the shared lock on P is granted to Transaction A it carries on.

The issue is the time period between t1 and t3. If this is large then
Transaction A is left blocked for a long period of time. If you know that
your transaction times are short though, this locking period is not a problem.

I know that Firebird does all this differently without locks and that its way
of doing it has distinct advantages (and some disadvantages) but it still has
to solve this concurrency problem in some way and as long as it does, then my
id_table code works properly too.

> there should not be a broken sequence nr in database at any time. That's
> only possible if you create these numbers in one transaction context. Multi
> user systems are working in different transaction context. So there are
> always possibilities to create gaps in sequences. Of course you can avoid
> it with special business rules (don't post this before finishing this if
> not take phone and make a call etc.) but this another topic :)

No you are wrong. If the database handles concurrency between users (and to
even be considered a non-toy database it has to do this), then you can take
advantage of this to have sequence numbers working perfectly.

> BTW, your test case doesn't reflect random delays in completing
> transactions!

And these delays are??? The environment assumes ethernet and a local LAN so
I don't have to worry about the network speed or modems. If the server is so
bogged down that it can't complete the first transaction in a reasonable
time, there is no use flooding it with others so they might as well wait out
the first one. Worst case is if the client dies in the fraction of a second
between when it has first started a transaction and when its finished and
dies in a way that the server doesn't notice and keeps the transaction alive.
Small window of concern that happens with any client/server database that
uses locking. Same problem happens even without the sequential ids so its
just something that could happen (but so far never has with any of our
customers to my knowledge).

And of course with the code I have you can just add a setup flag that allows
the user to say they wish to commit just after its gets the next transaction
number. Then it will work like generators and hand out unique but not
neccessarily consecutive numbers and it will lessen the concurrency issues.

This is really basic database stuff and I'm surprised at just how many people
don't get it. Perhaps its the issue of sequential numbers that confuses the
issue. But think of two applications that are both updating a balance. They
will both do "update account set balance = balance + 100". Internally it has
to get the current balance value, update it by adding 100, and then write out
the new value. If the balance starts out at zero, you expect the ending
result to be 200. Without locking or something equivalent, you would
sometimes get a new balance of only 100 though which is really not good. So
every database does something to deal with it and almost all of them use
locking whether you explicitly lock data or not.

--
Brad Pepers
brad@...