WAS and exclusive table access

[CALocklin]

Is there any reason for setting exclusive table access when using the WAS? I get the feeling there is not because it's basically running like a client/server db but I'd like some verification.

[Selwyn Rabins]

RE: WAS and exclusive table access

if you open a table in exclusive mode then another page might get locked out.

i don't see any reason to use exclusive mode.

[Peter.Wayne]

RE: WAS and exclusive table access

One possible reason for trying to access a table exclusively would be to back up or otherwise maintain the table. If the table is in constant use over the web and you are accessing it remotely, you might not be in a position to shut down the web server. However, if you have a script that tries to access the table in exclusive mode, once it succeeds, you can then zip up the table, or copy new records to an archive table, or do whatever other form of backup you deem appropriate, and other users attempting to add or modify records will be temporarily locked out--presumably what you want in the middle of your database maintenance.

[CALocklin]

RE: WAS and exclusive table access

That's a good point but, just to challenge Ignacious Picky-Picky to see who can be the biggest nit picker, is it necessary to set exclusive access if the WAS works like a client server and, "i"as I understand it"/i", only performs one operation on a table at a time while others wait in line for the last operation to finish? (Of course, my understanding could be completely wrong.)

One thing that I need to do daily (actually, my clients need to do it) is to copy new orders to a temp file that will be downloaded to the client's desktop. Currently I check to make sure the original table is not in use then open it in Exclusive mode then run a "copy" using a While loop and Record_Clone(). (*Don't ask why I didn't use an Append or other method - I don't recall.) I chose not to download the original table because it was faster to copy it to a 'temp' table thus "shutting down the app" for a much shorter time. In fact, I suspect that users will never even know there was a delay since there are seldom more than 200 orders a day.

So, a better question might be - Is there any possibility that other users might somehow add/delete/modify records while a "long" process, such as copying all records to another table, is running without having exclusive access?

If the WAS basically has exclusive access while each page is running and there is no point in setting it 'manually' then it would make routines like this simpler to create.

A related question (maybe): What about batch_begin()/end()? Any value in using this in the WAS? It seems to me there still would be when changing numerous records because the big issue seems to be the updating of indexes with every record change vs. "update indexes when done" which seems to be faster when there are a large number of records being updated.

Cal Locklin
"a href="http://www.aimsdc.net""www.aimsdc.net"/a"


*If anyone is curious, I checked my notes and there "i"is"/i" a good reason I did not use an append operation. If I used a fixed append operation, it would have to be updated whenever the original table structure was modified. Also, the table the data was being appended to would have to be modified. By rebuilding the download table with <tbl".create_clone() then using .Record_clone() to copy the records, the original table structure doesn't matter - the copy always works. Here are my notes from the script:
"font color="Blue""'Re-create the download table. This way we KNOW it can always be
'appended even if the New_orders_webonly table is restructured."/font"
Good notes are a "i"real"/i" time saver in the long run!

[Peter.Wayne]

RE: WAS and exclusive table access

It is certainly true that other users could modify/add/delete records during the minute or two that the copy is being run, but it hardly seems to matter in your case -- even if you open the table in exclusive access, you are only taking a snapshot of one minute or two in time, and afterwards users might still modify/add/delete to their heart's desire. If your process of copying also then permanently locked the records by, say, setting a field named "uneditable" to .t., then it doesn't matter whether other users are trying to update -- they will be prevented as each record is copied.
Or look at it this way. Suppose I am online and have a grid up to edit a record. You are running your copy in exclusive mode. Nothing I am doing keeps you from running the copy in exclusive mode, since I'm not even connected to the table while I'm editing at my computer. You finish your copy in a minute or two and I finish my edit. I save my edit and you have your copy which is now instantly out of date. The only way you can keep your copy from being out of date is by preventing me from saving any edits as soon as your process copies each record, in which case it doesn't matter whether you run your process in exclusive mode or not.

[CALocklin]

RE: WAS and exclusive table access

Peter, that's what brought up the whole issue and made me wonder about the need for exclusive access.

Your comment that "The only way you can keep your copy from being out of date is by preventing me from saving any edits as soon as your process copies each record..." is exactly what I was doing but in a different way than you suggested. By locking it for exclusive access, nobody could save their changes until I removed the lock and copying 200 records certainly doesn't take more than a few seconds. Then, the table is zapped before releasing the exclusive lock and changes cannot be saved unless the original record still exists - and, of course, it won't once the table is zapped.

I'll have to look at your idea of changing a field to block changes. I'm not sure which method is better. My only concern would be that somebody might be able to save a new order between the time my Copy routine ends and the Zap runs.

I'd still like confirmation from Selwyn, or anyone at Alpha who knows, on my original question. I know that opening a table in exclusive mode will lock others out. In some cases that is exactly what I want to do. The question is, "Do I need to actually request exclusive access to keep people out while my routine runs or will the WAS do that automatically as long as it is one continuous While loop?" In the desktop version it would be necessary to get exclusive access but I'm not sure about the WAS.

Here's more info on my example. I tried to keep it "short" before but inadequate definition just adds to the confusion so here's the rest of the story:

Customers enter new orders on the website. They can also go back and edit those orders if necessary until they are actually downloaded. At some arbitrary point in time (probably late in the afternoon) the business owner chooses to download the new orders. When that happens, no new or changed orders should be accepted until the "offload" of existing orders has finished - because once the are offloaded, the table will be zapped and any changes would be lost.

The "offload" consists of copying the existing records to a temp table then zapping all records in the 'new orders' table. Once the "offload" is complete, new orders can be accepted but changes can only be submitted if the original record can still be found. (Yes, this required additional programming in my web page but it's absolutely crucial.) If the original record is not found (which it won't be once the table is zapped), a warning message pops up telling the user that the orders have just been downloaded and they will have to contact the business by phone, fax, or e-mail to request any changes.

Once the "offload" is complete, the "temp" table is downloaded to the desktop so the new orders can be reviewed and imported.

As you suggested, it's the requirement to NOT allow users to save changes at the same time as the "offload" that creates the need for exclusive access (or some other method such as you suggested to stop changes). If they save the change after the record has been copied, the changes will not be downloaded until the next day - after the original order has already been completed.

As I think this over, I'm more and more convinced that exclusive access is necessary here because there are actually at least 2 steps: (1) copying the data to the temp table in a While loop and (2) zapping the records. Although the WAS may effectively create an "exclusive access" situation during the copy routine, I suspect there may be a chance for it to release the table and allow someone to save a new order before the zap() runs - not much chance but it only takes one missed order to really upset a customer.

This is why I would like to know exactly how the WAS handles something like this. If my script has a table open, will the WAS finish all the actions on that table in my script before it releases it to someone else's script? As long as I'm not sure how it works, I will continue to use exclusive access just to play it safe.

Cal Locklin
www.aimsdc.net

[Lenny Forziati]

RE: WAS and exclusive table access

The Web Application Server in no way whatsoever gives a running A5W page exclusive access to a table unless you explicitly open the table that way. It is fully multi-threaded and the threads run asynchronously and independently.

While it is highly dependent on timing, it would be entirely possible for you to begin an operation in one user session and have a second user session add/update/delete records in that same table before the first session completes the work it is doing.

If you need exlcusive access and you rely on the Web App Server to provide it for you without asking for it, you are going to run into problems.

-Lenny