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.

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.

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.

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?".
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?

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

thanks
Mark

Helen Borrie wrote:

>At 09:28 PM 4/08/2004 +1000, you wrote:
>
>
>>Hello,
>> I'm trying to the following scenario to work, but I'm finding that
>>the second transaction magically causes the first transaction to rollback!
>>
>>Scenario:
>>I have 2 queries that are under the control of Transaction A. 1 query
>>for obtaining a record to be edited (SQL_A) and a second query to
>>perform a lookup on one of those fields (SQL_B). When the user selects
>>an option in the lookup list (ie when an OnAfterScroll event is
>>triggered), I start Transaction B and open a 3rd query (SQL_C) that
>>locks the selected record in the lookup table, using an " update lookup
>>set columnA=columnA where columnA=:columnA", parameterized query.
>>
>>On the first selection, the record is correctly locked and no
>>Transaction is rolled back. On the second OnAfterScroll event, rolling
>>back Transaction B causes Transaction A to be rolled back as well.
>>
>>The code for locking the selected record, which is in the OnAfterScroll
>>event, looks like this:
>> sqlLockSs.Close;
>> sqlLockSs.UnPrepare;
>> if tranLock.InTransaction then
>> tranLock.Rollback; // This causes Transaction A to also
>>rollback...somehow!
>> if not sqlLockSs.Prepared then
>> sqlLockSs.Prepare;
>> sqlLockSs.ParamByName('COLUMNA').AsString :=
>>IB_Dataset.FieldByName('COLUMNA').AsString;
>> tranLock.StartTransaction;
>> sqlLockSs.Open;
>>
>>Any ideas as to why it's actually happening? I thought having a separate
>>Transaction would/should not affect the first?
>>
>>If I'm doing it wrong, then is there a correct way to lock the scrolled
>>record?
>>
>>Info: Delphi 5, IBObject 4.3.Aa, Win2k, Firebird 1.5.1
>>
>>
>
>Hmm, the first question I have to ask is why you think you have to lock the
>lookup record? (After that, there would be more questions and some comments!!)
>
>Helen
>
>
>
>
>___________________________________________________________________________
>IB Objects - direct, complete, custom connectivity to Firebird or InterBase
> without the need for BDE, ODBC or any other layer.
>___________________________________________________________________________
>http://www.ibobjects.com - your IBO community resource for Tech Info papers,
>keyword-searchable FAQ, community code contributions and more !
>Yahoo! Groups Links
>
>
>
>
>
>
>
>