SQLitening Support Forum

Dear All,

I am not sure how to make sure a particular record can be updated cleanly if multiple clients are trying to update the same record field in a client/server configuration.

To take a concrete example - if we have two PCs (PCa and PCb) each running a client with the server on a third PC (PCc). If the server PC database (PCc) holds stock records and PCa and PCb need to update the same stock record at the same time - then in order for this to happen properly (without the stock record just ending up with the last record sent from PCa or PCb resulting in an incorrect stock value) there needs to be a way of several database operations being able to perform to completion (say from PCa) before the other operations are performed from the other PC (sayPCb).

To further clarify the following operations : read stock record ; update it with new level ; write stock record back to database   -  all need to run to completion on one client PC before the other client does the same thing.

I am hoping that slExe with these operations within BEGIN and END will do what I want ie if this sequence of operations is done on one of the client it locks out the other client until the operations are finished ? Is this what is meant by a 'transaction' in SQLITENING ?

Hopefully I am on the right track here ? It must be a common scenario ie two clients tryinging to increment the same value in the same record in a database at the same time without ending up with a invalid value in the record (with the second client value overwiting the first rather than both clients correctly incrementing the record value).

Would appreciate any guidance on this.

Best Regards,
John Lever

In my experience I don't feel this is an issue. If I have a user that wants to edit a record in the inventory file (ie add supplier,edit product description) I put a temp 'lock' on the record. If another user wants to edit that record; their system sees the record is locked and prevents them to accessing the record.

However if several users are accessing the inventory to post sales from invoicing. The only fields that I modify are qty on hand and sales history (when the invoice is posted) . I pass all my updates via an sql statement. (I do this so that I keep my code is compatible with MYSQL and I can change the server if required). Thus the only information sent to the server is an update statement the entire record is not retreived from the server.


That being said -- I must say that SQLitening has exceeded all my expectations. I have found it extremely reliable and fast. One other thing that I like about SQLitening is the fact that I can very easily backup the database. Which allows me to keep redundency in my databases.

QuoteI put a temp 'lock' on the record.

Mind giving us a few more details on how this is done?   Thanks.  -- tom

This is how I do lock. It seems to work for me. I know others use lock tables that they look at before opening the record.

I have a user field in each record. This field serves two purposes :

1) It tells me who the last user that edited the record

2) It provides the ability to lock editing of the record.

When user A wants to edit a record if there is no one else accessing the record. User A's information is put in that field and he can edit that record (This takes just milliseconds). If user B wants to edit that same record his system sees that the record is being edited and prevents access. Once User A is done editing and the record is updated; his user information is updated in the field and the lock is removed.

In my experience this is works for my needs. Inventory qtys are posted from invoicing and this allow the office to do editing on the inventory file or do such things such as creating purchase orders.

The other thing that I do is I only update the fields that have changed (If that makes any sense) I do not update all the fields in the record. So if user A is modifing the item ie vendor details and user B is invoicing. The inventory change user B did (by selling inventory) will be posted to the system even though the record is opened by User A. In my system I can prevent User A from changing inventory qty  (we have an inventory change form that allows us to track who is making changes to inventory ). 

I am not sure this 'lock' scheme work in all situations - surely if Client A reads the record with a qty to be incremented and Client B does the same at pretty well exactly the same time - then there will still be a problem ie both will think they rewrite their record and so you will get incorrect data in the database ? Or am I missing something ?

I still do not understand what you are saying regarding UPDATE - surely if any stock record needs updating with an incremented value you get the same potential for ending up with bad data in the database ie since any SQL operation even an UPDATE at the end of the day just involves a WRITE to the record - in order for your solution always to work - it would need SQLITE or SQLITENING to have an operation which did an increment (or decrement a field in a record in the SQLITE database. Is this possible ? Is this what is being suggested ?

Best Regards,
John Lever

I have often wondered what the best approach to locking would be in an SQL environment. Here is something that has been floating around my head for a while... it saves the user from having to use physical lock files or bytes in a record.

SQLitening is already client/server so we have no problem sending requests to the server application. The application could send a message to the server prior to initiating an edit on a record.

The message could be anything that signifies that it is about to edit a particular record (for example, EDIT_CUSTNUM=12324).

When the server recieves this text it searches an internal array or linked list for the same text and, if found, then we know that another application has already requested to edit the record so the server sends back a message to the user saying that the record is in use. The user then simply needs to abort initiating any edit activity on that record.

If the text does not exist on the server then SQLitening creates the text in an array/linked list and sends back a message to the user saying that it is okay to commence the edit. When the user finishes editing then it sends another special message to the server to remove the previous message from the array/linked list.

Seems pretty simple, right? Other information could be stored with the text such as user id of the person doing the editing and maybe even the socket so that if after a certain number of pings it becomes evident that the connection is down then SQLitening can remove any "locks" that that connection may have placed.

Anyone see any holes in this idea? Hopefully I have transferred my thoughts clearly from my brain to the keyboard.

I think my original question may have been too complex - tried to simplify it here.

I am still trying to work out how to update stock levels in the same record
in the same table and SQLITENING database. Most client/server
databases at some stage will run up against the situation where two or more
clients want the perform exactly the same set of operations ie READ FIELD ,
ADD VALUE TO FIELD, REWRITE FIELD to the record in the database.

Where multiple clients try to update the same field in the same record in the database pretty much at the same time there could be problems. This will not happen very often - but always could happen and result in
false info. in the database. I just want to find a simple method of doing
this which will work in all situations. I was hoping slEXE would give me
this capability ? But am not sure ?  Is there a simple fooolproof way of
doing this ?

Best Regards,
John

Quote from: TechSupport on September 21, 2009, 08:39:40 AM
I have often wondered what the best approach to locking would be in an SQL environment. Here is something that has been floating around my head for a while... it saves the user from having to use physical lock files or bytes in a record.

SQLitening is already client/server so we have no problem sending requests to the server application. The application could send a message to the server prior to initiating an edit on a record.

The message could be anything that signifies that it is about to edit a particular record (for example, EDIT_CUSTNUM=12324).

When the server recieves this text it searches an internal array or linked list for the same text and, if found, then we know that another application has already requested to edit the record so the server sends back a message to the user saying that the record is in use. The user then simply needs to abort initiating any edit activity on that record.

If the text does not exist on the server then SQLitening creates the text in an array/linked list and sends back a message to the user saying that it is okay to commence the edit. When the user finishes editing then it sends another special message to the server to remove the previous message from the array/linked list.

Seems pretty simple, right? Other information could be stored with the text such as user id of the person doing the editing and maybe even the socket so that if after a certain number of pings it becomes evident that the connection is down then SQLitening can remove any "locks" that that connection may have placed.

Anyone see any holes in this idea? Hopefully I have transferred my thoughts clearly from my brain to the keyboard.

Dear Paul,

Are you suggesting something outside of SQLITENING to do this ? If so or if not so do you have any pointers as to how to do this practically ?

Best Rgds,
John Lever

Locking can fail if not designed correctly.  Since SQLite does not have any mechanism then you will have to define your own.  I suggest some Googling.

Think about these issues:
1) Deadlocks - Process A requests a lock on record 1 then record 2 to do an update, Process B requests a lock on record 2 first then record 1.  If B requests the lock on record 2 in between the requests by A you will be deadlocked (sometimes called Fatal Embrace).

2) Releasing orphaned locks promptly.  Paul mentioned this one.  If you don't handle this the server will quickly deteriorate.

3) If the locking scheme can be interrupted at the thread level it can still fail.  Process A starts a lock request and determines the resource is available but before placing the lock flag the OS reschedules the thread and it is temporarily suspended.  Process B requests a lock and completes.  Process A gets its thread rescheduled to pick up where it left off and assumes it can complete the lock.  Both Process A and B now have locks on the same record.

I have not recently done any work with SQLitening but my understanding it queues requests and handles them one at a time.  This is how it makes SQLite multi user.  Since SQLitening allows (in some places) multiple SQL statements to be stacked in a single request you probably can construct some SQL statements that will check for a current lock and either return that the requested record is locked by another process or actually place the desired lock and return with success.  This would all be done in a SQL application level table of locks much as Paul described but his solution would be in memory.  This approach would use the "one request at a time" inherent feature of SQLitening to avoid #3 above.

Testing locking can be "interesting" since failure may only occur in a very narrow slice of time.

One other trick, if this is used in all interactive processes, is this technique:
1) Read the record at the beginning of the "transaction" and save it in a temp buffer
2) Interact with the user
3) At the point of save read the record again into a second temp buffer
4) If the two temp buffers are not equal the record has changed so you reject the input by the user and make them do it again.

This still has issues but if you need to leave a lock open for a very long time (user typing, answering the phone, etc) and you do not frequently update the same records it has some merit.  You must lock for a moment at the end if you determine the update is allowed since nothing changed.

If you do this with "system" processes you will have to build some additional logic in the code to handle things when the record changes.

Food for thought.

Really does sound v.v. complex for something that must be a part of just about every client server database application ?

I am still wondering if there is a v. simple foolproof approach (even if clunky and slow - who cares  as long as it works?).

Perhaps something which say :

1. Reads a pseudo  'lock' field in the record to be updated in the database and if empty goes to 2. If not goes to 1. again - tries this say 20 times and if never gets an empty field - clears the pseudo 'lock' field (just in case we have got in some sort of 'lock' bind).
2. writes an identifier to a pseudo 'lock' field in the record to be updated.
3. Reads the lock field and checks that the identifier matches that sent in 2 if so goes to 4. If not goes to 1.
4. Writes the updated stock value to the record and clears the pseudo 'lock' field.

Would this complex scheme work ? Is there an easier way to say lock the database or the table - even if v. slow doing this ?

Helpppppp ?

Rgds,
John Lever

I think that if you wrap the whole thing in a transaction then it should work. The immediate transaction should place a lock on the database so once you get the pseudo lock placed then you should be okay. The problem I see is a situation where the connection drops and the pseudo lock never gets unset. How would it ever get unset? Maybe need to have to run a SQL statement on the entire table to release the locks.

I still like my idea of keeping the locks in memory on the server. However, we really need to talk to Fred to get his thoughts on locking before we implement any changes.

http://www.agiledata.org/essays/concurrencyControl.html

http://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking

http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/ba00f9c9-bab1-4390-8b44-84cd6f7a5a5b/

We'll have to come up with some good way to handle multiuser locking.

Thanks for all your help so far.

Having thought a bit about it - what would I believe work is something which just added a number to a field in a table row on the database (including a negative number) as a single 'atomic' action - this should solve any problems where fields in a database (eg stock levels) were being updated. What I mean by 'atomic' is that the operation would run to completion in SQLITENING/SQLITE without any possiblity of interruption.

I think Paul's suggestion of an in memory operations would work in most cases - but if a crash occurred on the server then this data would be lost and so operations the user thought had been performed would not have been (persistent storage in a database tables in an ad hoc lock solution would maybe protect against failures such as this ?).

A question for Paul Squires - when saying a 'transaction' should solve the problem in most cases. Is anything between BEGIN and END in a slEXE a 'transaction' or what is a 'transaction' ? If you have a set of operations like  : READ  a field in a record from database ; add a quantity to the field (eg + 10 or -10) ; WRITE field back to record in database   -  this can never be a transaction because the 'add a quantity' operation even though within the slEXE BEGIN END block is POWERBASIC code - not SQLITENING/SQLITE code - so can never work as an 'atomic' operation (see above for defintion of atomic') ?

Best Regards,
John

If you wrap your SQL statements within a BEGIN IMMEDIATE, END TRANSACTION block then you will be guaranteed exclusive write access to the database during the length of the transaction so that no other processes can interrupt your transaction. If the server crashes then a rollback journal is created that puts the database back into its previous state whenever the server restarts. Of course, this only works if you send the SQL statement to the server all in one shot like:

sSQL = "BEGIN IMMEDIATE;" & _
   "UPDATE stock SET quantity = quantity + 10 WHERE partnum = 'AF121'; & _
   "END TRANSACTION;"

Notice how the multiple sql statements are joined to each other by the ";" semicolon.

Now, if you are reading the data, displaying it on the screen and waiting for a user to change the values before writing it back to disk then the above is not valid. You would have to use optimistic or pessimistic locking techniques like in the links in the previous post.

Here is the documentation on sqlite transactions: http://www.sqlite.org/lang_transaction.html

Good to know that the TRANSACTIONS in SQLite do guarantee exclusive access.  That solves half of the problem.

The other half is when there are interactive users involved and you read a record, let it sit on the screen, then do an update.  The update can happen in a few seconds or after hours of waiting (after the person returns from lunch or a long phone call!).

The only satisfactory way I have found that does not lock out other users is to re-read the record just prior to the update and if it has changed reject the transaction by refreshing the screen with the new data and make the user input their changes again.  Some of the multi-million dollar "big boy" systems use this as a standard technique.  Unless you update the same row repeatedly it does not cause too many problems.

You can get into other ideas like timing out a screen that has read a record and locked it for update making the user reload the record if they wait too long to save it.

If you use some lock method that leaves the record locked upon reading it you end up locking out other users for potentially long periods of time.  In the case of going to lunch (see above) it becomes impractical.  If you don't know who locked the record then it is even more complex because you cannot track down the user that has the record on their screen to get them to release it.  I suppose the proper lock method could show the user who owns the lock to you could track them down.

My guess is many interactive systems don't handle this properly and sometimes do what is called "Interleaved Updates" and don't know it happened since it is not reported as an error.

Ah the joys of interactive computing.