Multi-user problem and probable bug

[csda1]

Re: Multi-user problem and probable bug

If a user is working in the inventory file and you attempt to post the record which is being edited from another workstation say from a button on an Order Entry screen updating inventory from the orderitems table, the script on the button will fail. If the inventory clerk walks away during an edit, which happens more frequently than you think, no real-time processing on that record is possible.

What you said is not true.

An inventory table should never be locked by anyone entering in what amounts to sales tickets of the inventory. If it is, you are locking anyone selling a large wicket from using that item if anyone else is using a large wicket, and that has never been the correct way to create the tables and sets (not to say people haven't done it, but it is wrong). It is referenced, and at save time, the post will deduct (or add back for a return) the inventory, as well as other post operations needed.

Anyone working on the sales inventory should not be actually entering the data on there, but should be normally managing in a table for received stock, which is also posted to the inventory table. They could also do updates and appends to the actual inventory table, which does not lock any record down for any length of time.

Thus no one is actually in an inventory record for any length of time, locking out others. This works perfectly with non-SQL like databases.

A5 using dbfs is not up to the task of developing enterprise systems with real-time processing with pessimistic locking. I think this record locking strategy is archaic anyway and needs to be changed.

Your swinging after the bell!

The choice of optimistic locking is yours if you use an SQL table, and active link tables. What's the beef?

[Chet Sapino]

Re: Multi-user problem and probable bug

What you said is not true.

An inventory table should never be locked by anyone entering in what amounts to sales tickets of the inventory. If it is, you are locking anyone selling a large wicket from using that item if anyone else is using a large wicket, and that has never been the correct way to create the tables and sets (not to say people haven't done it, but it is wrong). It is referenced, and at save time, the post will deduct (or add back for a return) the inventory, as well as other post operations needed.

Anyone working on the sales inventory should not be actually entering the data on there, but should be normally managing in a table for received stock, which is also posted to the inventory table. They could also do updates and appends to the actual inventory table, which does not lock any record down for any length of time.

Thus no one is actually in an inventory record for any length of time, locking out others. This works perfectly with non-SQL like databases.



Your swinging after the bell!

The choice of optimistic locking is yours if you use an SQL table, and active link tables. What's the beef?

Here is a real world scenario and you can convince yourself that pessimistic locking is not going to work.

I wrote an ERP package for a medium sized company with Clarion for Windows a few years ago. This sytem uses op lock.

There was an inventory control clerk whose job it was to receive in inventory, make changes to inventory control records, enter sales pitch data for each item, etc. This system was real-time. When inventory was received in it was added via code from a routine in Inventory control involving posting received in purchase orders to the inventory table.

At the same time, there were several clerks taking orders in order entry with inventory being updated from order entry as it was being sold.

In a5 using dbfs, if a user is in inventory control editing a record and THAT record is attempted to be posted from Order entry or receiving by a script, the script that is attempting to update the record will not be able to get a lock. This will throw an error. When the error is thrown, you could write a time delay routine to check for a lock every second before it goes any further. This is easy to do. You might want to do this for a few seconds. However, if the inventory control clerk leaves the station during the editing process, the record has the lock and the script will crash.

The script ends at this point and if other tables are being updated past this point like customer stats,AR, and GL with the script executing from OE, these tables will not be updated and the system will be totally out of balance due to the error thrown in the script.

There's nothing theoretical about this phenomena.

You can prove this to yourself very easy. Open up a form and go into edit mode. Then try to run a script to update this record.

If you look at the help under Record Locking in the Index, you will find a really absurd thing happening if you try to run the code examples. Here is an excerpt:

t1 = table.open("Customer")
t.fetch_first()
t1.change_begin()
?t1.is_record_locked()
= .t.

t2 = table.open("customer")
t2.change_begin()

The help shows an error trap here:

"ERROR: The process cannot access the file because another process has locked a portion of the file.
customer.dbf"

Unfortunately, this error trap doesn't execute in the interactive Window or a script and a5 becomes totally locked up such that you have to open task manager and shut a5 down under processes. Try it.

I believe this is a bug in v9 and V10.

Or just simply open up the same form twice. Start editing in form A, and then open up form B (the same form) and attempt to edit the same record. Here the error trap works.

During real time posting of complex systems like I described, you run a grave risk of whacking an entire system.

In an op lock system (a5 with SQL) oe even MS Access, the record updates will post perfectly and the inventory control operator will not sense anything is going on since he will never be updating quantities on hand. And even if he is, a script will not throw an error that is visible to him. The error window only pops up if two users are editing the same record in forms.

[csda1]

Re: Multi-user problem and probable bug

Hi Chet,

In a5 using dbfs, if a user is in inventory control editing a record and THAT record is attempted to be posted from Order entry or receiving by a script, the script that is attempting to update the record will not be able to get a lock. This will throw an error.

What you said above is true, but is not what I said needed to be done.

Anyone doing inventory control editing should not be (and can not be) locking any record at all. He should be instead posting to the inventory changes he needs to make, just as everyone else needs to. Posting data to the inventory table, as well as Operations like Appends, Updates and even entering new records directly in the inventory table are all OK, as these do not lock any inventory records except for very brief periods (milliseconds).

However, all changes to the inventory table, INCLUDING THE PERSON EDITING INVENTORY INFO, must be done as posts from either another table/set or via an operation/code to do it.

This is true of any many-to-many link, which an inventory (e.g. many sales receipts reference many of the same items from inventory) is an example of.

[Chet Sapino]

Re: Multi-user problem and probable bug

Hi Chet,



What you said above is true, but is not what I said needed to be done.

Anyone doing inventory control editing should not be (and can not be) locking any record at all. He should be instead posting to the inventory changes he needs to make, just as everyone else needs to. Posting data to the inventory table, as well as Operations like Appends, Updates and even entering new records directly in the inventory table are all OK, as these do not lock any inventory records except for very brief periods (milliseconds).

However, all changes to the inventory table, INCLUDING THE PERSON EDITING INVENTORY INFO, must be done as posts from either another table/set or via an operation/code to do it.

This is true of any many-to-many link, which an inventory (e.g. many sales receipts reference many of the same items from inventory) is an example of.

All posting/updating operations use tbl.change_begin() which locks the record. It is inherent in post(). If you try a tbl.change_begin() on a locked record (someone editing that record), you will lock up a5 and the whole system, requiring task manager to get out of it. You have to check if there is a lock (tbl.is_record_locked()) - this is done by operations, and proceed accordingly. We're not talking here about entering records, or appending records on a table with a single locked record. There is no problem here. We're talking about updating records on a locked table.

Any posting/updating operation requiring a pessimistic lock on a record (update/post) that could be edited by another person is theoretically impossible to accomplish in a5 without a potential a5 system crash.

If I have a record in edit mode on a form, there is no way to update that record using any kind of a5 code in real-time unless that form gets out or edit mode. It's impossible.

[csda1]

Re: Multi-user problem and probable bug

Hi Chet,

All posting/updating operations use tbl.change_begin() which locks the record. It is inherent in post().

Something like it anyway. The posting, append, updates etc operations actually batch a bunch of record locks at a time.

If you try a tbl.change_begin() on a locked record (someone editing that record), you will lock up a5 and the whole system, requiring task manager to get out of it.

NO, your code will wait until the record is unlocked, possibly forever.

I SAID, and repeat, if NO ONE or NO CODE is sitting in the record in change mode, and they only temporarily lock the record for field rule posts (which is the correct way to deal with a many-to-many), or an operation passing through the record, then which ever code gets to it 2nd will wait until the record lock is released. Because no computer is ever sitting in change mode on a record, but only temporarily requests a change lock for a post-like method, they wait for the request to be fullfilled (milliseconds or faster on most networks), then reads/writes the data and releases the record lock write after. Other code waiting for the lock will now continue, getting their own locks.

There is absolutely no code required for processing any of these, it's all handled by the field rules and A5 operations. Even XBasic code will work without regards for the locks IF AND ONLY IF, no one holds the code in change mode except for a quick read/modify/write.

This method works in A4, in A5 and even with SQL (though not needed for the last).

[Chet Sapino]

Re: Multi-user problem and probable bug

Hi Chet,


Something like it anyway. The posting, append, updates etc operations actually batch a bunch of record locks at a time.


NO, your code will wait until the record is unlocked, possibly forever.

I SAID, and repeat, if NO ONE or NO CODE is sitting in the record in change mode, and they only temporarily lock the record for field rule posts (which is the correct way to deal with a many-to-many), or an operation passing through the record, then which ever code gets to it 2nd will wait until the record lock is released. Because no computer is ever sitting in change mode on a record, but only temporarily requests a change lock for a post-like method, they wait for the request to be fullfilled (milliseconds or faster on most networks), then reads/writes the data and releases the record lock write after. Other code waiting for the lock will now continue, getting their own locks.

There is absolutely no code required for processing any of these, it's all handled by the field rules and A5 operations. Even XBasic code will work without regards for the locks IF AND ONLY IF, no one holds the code in change mode except for a quick read/modify/write.

This method works in A4, in A5 and even with SQL (though not needed for the last).

>then which ever code gets to it 2nd will wait until the record >lock is released. Because no computer is ever sitting in >change mode on a record, but only temporarily requests a change >lock for a post-like method, they wait for the request to be >fullfilled (milliseconds or faster on most networks), then >reads/writes the data and releases the record lock write >after. Other code waiting for the lock will now continue, >getting their own locks.

You need to run some tests and convince yourself that what you are saying is not true. There is no update or post code scheme which waits indefinitely for a record to be unlocked, and then proceeds to do the update.

The lock is absolute. If a record is locked by a user being in change mode, there is no a5 code which is going to update that record until the user exits change mode by saving the record. So whatever "canned code" from operations you are running against that record is going to crash immediately. Test it. Nothing waits around until the lock is released and then updates.

If you are running a "canned" post operation, there is an on error goto 123456766 etc.
prior to the:

a_tbl.post()

If a locked record is encountered, the code jumps to the goto statement and a message box fires saying: "error running operation...."

Here is an example:

t=table.open("names")
t.fetch_first()
t.change_begin()

Now the first record in this table is locked by the t.change_begin() similar to having a form open and editing this first record.

Give me some code which will update this record while it is locked. You can't. It's impossible. Now assume it is locked for 10 minutes. There is no code on earth that is going to update this record until 10 minutes later - and - there is no code that is going to wait around for 10 minutes unless it is programmed into an on error goto routine that waits indefinitely or cycles for 10 minutes.

This is hardly real-time. If you type in:

t2=table.open("names")
t2.fetch_first()

to simulate a second user attempting to update this table,

If you don't check to see if this record is locked, i.e.,
t.is_record_locked(), and you do

t2.change_begin() to simulate a second user attempting to change this record.

you will lock up a5 and it will stay locked up forever, requiring a task manager intervention.

If the db is SQL, and you do t2.change_begin(), the lock placed by t.change_begin(), is turned off and now t2, has the lock. This can be verified in the interactive window by running an is_record_locked() test on t and t2. Now t2 can update the record while t is editing it. when t2 is finished and executes t2.change_end(), t2 gives the lock back to t.

This is the way dbf tables should operate, and since they don't let's not talk about great advantages over MS access.

[JohnZaleski]

Re: Multi-user problem and probable bug

I deal with mid-market accunting software. Typically they have an inventory master file
with a unique sku. They also have a warehouse item record with the "warehouse -sku" as the unique element. It is this record that houses on hand, on order, on purchase order and staged amounts.As Ira mentioned it is not directly changed in a file maintenance program, but is transacted to.

[aRob]

Re: Multi-user problem and probable bug

Now assume it is locked for 10 minutes.

You structure the application, the tables, so that no one can place a long record lock on a record which may be requested by someone else. For example, you receive inventory to a 'receiving sheet' table and update inventory quantities when the 'receipt' is finished. The update lock only takes a fraction of a second. The 'receiving sheet' record is not going to be requested by anyone else so the operator can take a coffee break and it won't stop the system from working. Other users can do order entry because they are not dependent on any locked records.

[Tom Cone Jr]

Re: Multi-user problem and probable bug

Edited. [ my comment did nothing to advance the conversation in a positive direction, and has been deleted. ] -- tom

[martinwcole]

Re: Multi-user problem and probable bug

Chet, if you WANT to make Alpha work for you, you CAN come up with ways to make it work!

In almost all of my apps, I have a timer set for the last time a user used the keyboard, and if they don't use it for a specified length of time I cancel what they were doing (i.e., release the record in change mode), and usually also close them down from the system altogether.

[DickJames]

Is there a way to place the a_tbl.is_record_locked() as a filter in an operation (without converting the operation to an Xbasic loop) so that it just skips the record that is locked?