Dear Support!

I have more tables that are keeping important values.
How to protect these tables without locking?

For example:
If I want to create a voucher, I need to update "voucher types" table (to increment the unique id of actual type), the "articles" table.(to change quantity)...

The problem that when somebody started to editing a voucher type record and he/she cancelled the typing, becaue of moving from the office to lunch, then if I need to create this kind of voucher, the creation failed on his/her lock in this record.

It can also happens when somebody actually editing the article, and the voucher creator routine trying to change the quantity of it...

First I thought: the problem is easy, I creating a cached update component, and that's all.

But the locking is very useful, because when A user caching the values, starting to edit, and B user is also caching then A and B keeps the actual value of quantity, and this not reflecting the changes of another users...

A typical problem as in threads - protecting with criticalsection, lock, etc...
But these "locks" are holding the records by modifications.

Maybe this problem can be solvable if the EDB can detecting when row changed.
For example (A and B another users):

TA.Locate('ID', 100);
TB.Locate('ID', 100);

TB.CheckChanging;
TB.Edit;
... B Change;
TB.Post;

TA.CheckChanging; -- error
TA.Edit;
...

I can simulate this with "modified" timestamp field, but it needs to change many codes, and databases, and this is too big work to do in every place...

Maybe EDB have some inner record version ID, or a sign that can be checked to see if any of users do something...

So the needed state if the modifications are very fast (cachedupdates), they cannot do long locks, but we can check if other sessions changed the Actual Row (what is the current record).
Then I must reject the modifications...

Thanks for any info!

dd

durumdara


If I understand correctly I'd suggest moving away from direct editing of table data. Create unbound controls and use a transaction when things are complete to update the tables.

Roy Lambert [Team Elevate]

Dear Roy!

>>If I understand correctly I'd suggest moving away from direct editing of table data.
>>Create unbound controls and use a transaction when things are complete to update the tables.

The main problem if I want to see that is table changed or not, I must extend the table with plus field (to detect change).
Unbound controls make same effect as when I copy all data to kbmMemTable and after edit I recopy the modified row...
I thought that EDB have some inner support to I can see when a row changed from cached state...

I know that EDB have some detection, for example when the row visibility changed, or the row deleted.

Thanks: dd

durumdara

>>>If I understand correctly I'd suggest moving away from direct editing of table data.
>>>Create unbound controls and use a transaction when things are complete to update the tables.
>
>The main problem if I want to see that is table changed or not, I must extend the table with plus field (to detect change).
>Unbound controls make same effect as when I copy all data to kbmMemTable and after edit I recopy the modified row...

I am missing something here. If you're editing the data in a state that is not bound to the dataset why does it matter if the row has changed?

>I thought that EDB have some inner support to I can see when a row changed from cached state...
>
>I know that EDB have some detection, for example when the row visibility changed, or the row deleted.

There is some change detection but its only at a level that if you have data presented and try to edit it then ElevateDB (or DBISAM) will let you know. But, I think, that only applies to pessimistic locking and to edit controls not data alterations via program.

Roy Lambert [Team Elevate]

I'm not sure if this addresses what you need but i would redesign the
logic so that the voucher number is issued when they submit/save at the end.

I'd do this using a trigger on insert.

THis way there are no locks, no need to worry about cancel and multiple
people  can edit - they get their voucher number only when they're done
so order even does not matter.

Raul

On 4/3/2012 8:07 AM, durumdara wrote:
> The problem that when somebody started to editing a voucher type record and he/she cancelled the typing, becaue of moving from the office to lunch, then if I need to create this kind of voucher, the creation failed on his/her lock in this record.
>
> It can also happens when somebody actually editing the article, and the voucher creator routine trying to change the quantity of it...
>
> First I thought: the problem is easy, I creating a cached update component, and that's all.
>
> But the locking is very useful, because when A user caching the val

>I am missing something here.
>If you're editing the data in a state that is not bound to the dataset why does it matter if the row has changed?


In the past my boss designed the tables for little usage.
So he put "Quantity field" into the article table (do not calculate it from vouchers, or not place in other table).
And he put "Last Voucher Number" field into the "Voucher Types" type.

What is this meaning?

Because the voucher creation is needs to chance the article quantity, and increment the last voucher id, these tables must edit at end of creation.
BUT: when somebody is on the record, and locked it with edit (change the article, change a type), the voucher creation failed...

When I change the article and type edit to cacheupdate then if I apply the update, may the Quantity field and Last Voucher Number field is not same, because another user created a voucher, and I overwrite the good value.

Example:
XY is Editing Type (the last number is 182)
Two man creating two vouchers, the last number is 182 + 2.
XY is Apply the changes - the last number is reset to 182...

Lock can prevent this kind of overwrite, but then if somebody hold the record for long time, the voucher creation failed...


But:
If XY can SEE, that row is changed with an inner mechanism, I say him/her that somebody changed the row, refresh it.
Ok, I can emulate it with "modified" timestamp, but this needs to changing in many-many places (in Database and code too), and that is hard to do, more chance to I write some wrong and the older usage mode is also failed.


>I'm not sure if this addresses what you need but i would redesign the
>logic so that the voucher number is issued when they submit/save at the end.
>I'd do this using a trigger on insert.
>THis way there are no locks, no need to worry about cancel and multiple
>people  can edit - they get their voucher number only when they're done
>so order even does not matter.

The problem is that trigger is also using this Type and Article table for change something, and the locks also preventing the modifications.

The solution as I see:

1.)
Apply "modified" field to see if row changed.

2.)
Using "except fields" in caching mechanism. If I ignore the "voucher type last id" and "quantity" fields from caching, they cannot be overwritten.

3.)
Redesign of tables, move these values into another tables - what is impossible now...


As I see that mix of first and second solutions can help me.

Thanks:
  dd

durumdara


Again I may be missing something, and I can see two possible scenarios:

A. User enters new quantity
B. User enters quantity for this voucher

What I would recommend is something like:

1. When the user wants to create a voucher present the information in unbound controls. If A is correct then store quantity in a variable (say qty) as well
2. When the user saves the voucher
i) start a transaction - this locks the tables
ii) read the current voucher number, increment and post back, display to user if necessary
iii) read the current quantity. If A then subtract qty from what the user has entered, and update the quantity with the difference, if B then qty is zero
iv) COMMIT the transaction - this updates the the tables and releases the locks
v) display the new information to the user if wanted

Time in the transaction is very short so no one is locked out

If a user wants to edit a voucher then display in bound controls and lock the table.


Roy Lambert

Roy Lambert wrote:

>>Again I may be missing something, and I can see two possible scenarios:

Yes...

So:

The problem is that now users can edit Article and Voucher Types tables directly.
This meaning lock on actual records, and if the user forget to close the window, the lock is constantly up for long time.
If somebody trying to create voucher, this lock is prevent him to do this.

Ok, then we creating unbound gui for Article and Voucher Type editing.
But: my boss very very long time put the needed fields into these tables, not in another.

So when the user editing the Article table with unbound method, we don't be sure that underlying record is changed or not.
Only at Edit, when we successfully get a lock we can see the actual record.

Some of the databases can check that underlying record is changed or not.
Then we don't know anything about changes - except if we hold the starting state too.

A.) The underlying row (TEDBTable)
B.) The starting row (kbmMemTable)
C.) The Edited row (kbmMemTable)

if after A.Edit  the B.Fields = A.Fields then we have original record - else somebody changed it.

Then we can determine what we do:

a.) Warn the user to change
b.) Abort
c.) Ask a question

But everytime when the record changed we have problem with these fields (Article Quantity, and Last Voucher ID), because if the user overwrite with his/her version then prev. background modifications are lost.

So: if we using locking (actual version) then long time editing of these tables prevent the voucher creation.
If we don't use locking then we must handle these fields.

As I think I must create two options:
a.) Editing Article without "Quantity" rewriting - don't use locking
b.) Editing Article Quantity only - use locking (the user know what he/she want) but for very short time...

I don't know is it understandable or not?

Thanks:
  dd

durumdara


OK, to a large degree I'm still guessing here. I'm assuming that the quantity field is some sort of stock on hand quantity. The voucher types table you refer to that can be edited directly does it contain anything apart from the voucher number and type description? If not I can see no problems with handling that. The number should only be altered within the scope of a transaction, editing the description would stop people, but that could be done unbound and who cares if someone alters it and then it has to be altered back. Inserting a new voucher type isn't going to lock anyone out.

The article table if its only the quantity being altered then you can handle it as I suggest, if its other things then yes you need to check the in table value and compare with the old values you've stored before updating.

Without actually seeing the app so I can understand fully what you're trying to achieve I don't think there's anything else I can contribute.

Roy Lambert [Team Elevate]

<< The problem is that now users can edit Article and Voucher Types tables
directly.
This meaning lock on actual records, and if the user forget to close the
window, the lock is constantly up for long time.
If somebody trying to create voucher, this lock is prevent him to do this.
>>

This seems to be the crux of the problem.  If this is a constant issue, then
I would simply put a TTimer on the applicable forms and have it fire after a
minute or so.  When it fires, simply ask the user to confirm if they're
still working, and if they don't answer within a certain period of time
(another TTimer), just cancel the edit.

<< So when the user editing the Article table with unbound method, we don't
be sure that underlying record is changed or not.  Only at Edit, when we
successfully get a lock we can see the actual record. >>

Why do you need to see the current state of the Article quantity or Voucher
Type last voucher number ?  Both should be simple difference operations that
can occur during a transaction at the end of the process:

1) Do your work on the buffered data in unbound controls, etc. (you actually
don't even need unbound controls for this)
2) Start transaction
3) Get next voucher number (get, increment, update voucher type row)
4) Update article quantity (get, add existing quantity, update article
quantity)
5) Commit transaction

Perhaps you could post the actual table structures (SQL is fine).  That
would allow us to see how everything relates.

--
Tim Young
Elevate Software
www.elevatesoft.com