At 12:01 AM 5/08/2004 +1000, you wrote:

>Context is allocating sales against a stock-sheet. The lookup is the
>stock-sheet number that contains count information regarding a product.
>eg Stock sheet contains a count of 5, and whilst someone is allocating
>against a sale that stock-sheet, no-one should be able to edit the stock
>sheet, to say decrease the stock count.

OK, I think the first thing you have to let go is your belief that a lookup
is the way to implement this 1:1 relationship. "Lookup" is really a
desktop-database thing, a Paradoxy thing, that's very costly to
client/server implementation. Although lookups have their uses in
client/server, this aint one of them. I won't explain that here, it's a
bridge you have yet to cross.

Instead, use a join to meld this relationship. Then...

>I realize that locking is something to avoid, and perhaps I haven't got
>the concept of Firebird's locking mechanisms, but no-one has been able
>to point me to a *realistic*, *real-world* example of how to solve these
>types of problems.

There are plenty of real-world examples around but they won't mean much
until you've got the multi-user, transaction-isolation aspects on board.

So, let's look at this join. I'm totally guessing the columns in your
Product and Stocksheet tables but I hope you'll get the picture.

select p.productid, p,productname, s.stockcount
from product p
join stocksheet s
on p.productid = s.productid
where....(whatever search criteria you need to drill down with)

This set has no lookups and no need for any.

Yes,*explicit* locking is something to avoid most of the time because, most
of the time, it's not necessary. Transactions have an attribute called
isolation and another called LockWait. By choosing the right combination
to suit what you need, you can get exactly the level of locking you need
performed by the database engine.

However, in this case, you *do* need an explicit lock -- one that gets
applied immediately before the dataset allows you to begin editing. This
is because "editing" isn't a database activity, it's a client
activity. The database has no idea that a client is editing anything until
AFTER the user has finished and actually posts the changes to the database.

So what happens with the explicit lock (the PessimisticLocking property of
the dataset set true) is that the dataset posts a dummy update to the
database *before* the user starts editing the row. It's all done in the
same, explicitly-controlled (not Autocommit) transaction. The dummy update
causes the row (or rows, in the case of a set that's not naturally
updatable) to be locked: no other transaction can then post an update to
it. That's all it takes.

Now, it's really important to understand that Post and Commit are two
entirely separate things. Post creates a new record version on the server
but it is invisible to other transactions. You can post many DML statements
before you actually end the transaction. Commit, finally, makes all of
that the work visible to all transactions; rollback reverts database state
to what it was before the transaction began.

Now, going back to our joined dataset. A joined dataset isn't naturally
updatable but, in IBO, you can make it so. If you need to update more than
one of the tables in the join, you write a parameterised, executable stored
procedure to do it. If you only have to update one table (your case here)
you set the dataset's KeyRelation property to be the table that is getting
the update. You will need a parameterised stored procedure for the
LockSQL. Basically, it takes the common key of the two tables and updates
each one by setting the primary key = the primary key.

Now, the dummy update will either succeed (because no other transaction has
an update pending on that row) or fail (because another transaction has an
update pending). You intercept the error in the latter case and tell the
user she can't edit that record now because someone else is doing so.

>I'm certainly not having a go, I'm just saying I haven't been able to
>find examples that can break my notions of data consistancy and
>locking...and it's quite frustrating. Any point in the right direction
>would be greatly appreciated.

I understand your frustration. It's a very big leap. But there really is
a penny waiting to drop. Work with it, it will happen.

>With that in mind, I guess my question becomes, "well, how can I achieve
>the same effect - that is prevent a count from being modified, or at
>least to ensure the quantity sold doesn't exceed the quantity on-hand?".

Ok, these are two totally different requirements. Totally preventing the
count being modified is the easy one (described here); conditioning it on
the results of others' pending changes is rocket science. I'd suggest
that, if you can live with the pessimistic lock to begin with, the way to
work the conditional situation will begin to evolve as the paint dries.

>Even if I did not pre-lock the record and used consistant locking on the
>transaction, how do you ensure that the quantity in table A remains more
>than the sum of the quantity in table b?

Now, are you saying that the stockcount is calculated from multiple
records, i.e. the relationship between the tables is 1:many and you're
updating a master field from summing the details? And that the stockcount
is modified by adding more records on the Many side?

If so, the SQL for the join is a bit more complex and the LockSQL
procedure, too. There are techniques in IBO to manage this, of course, but
you need to do stuff on the database side, too, to achieve the protection
you want. The water is quite deep in such places...

>I'm determined to get this, but at the moment the locks are winning :)

I'm tempted to yatter on, but I've typed long enough here for today and
maybe given you a few things to think about for now. Bedtime for me.

Helen