What you just said doesn't apply to MS SQL database lock functionality nor it's correct.
To have a better understanding of what a database lock means :
Locking is essential to successful SQL Server transactions processing and it is designed to allow SQL Server to work seamlessly in a multi-user environment.
www.sqlshack.com
We don't make any statement without doing a research and having multiple references in case there is a comment like yours.
The subject of Database/Record Locking is not entirely complex to understand.
However, the use of LOCKING is entirely up to the programmer, who may or may not understand the severity of events that can be created, depending on how the LOCK Verb is used.
A simple example -
I am Invoicing, entering a line item on the Invoice.
Steps -
1) Select/Read a Stock Item - key an Order Quantity
2) Program checks the StockOnHand field hold enough items to satisfy the order ?
3) Complete the Order line, updating either a LineCommitted Value or the StockOnHand Value itself.
Where does the Lock Take place ?
If after step 1, then if the Operator decides not to complete the Line entry, and goes to get a cup of coffee, then the Stock Item is Locked from everyone else, until he
comes back with his coffee. (People are screaming).
If just before Step 3, then how many other Operators or On-Line Customers also sold/bought the very same item while the Operator was dreaming ?
The actual best place to lock is prior to step 3, but the program needs to do the following.
1) Operator confirms the Order Line.
2) Program reads the Stock Item again
3) Program Locks the Stock Item
4) Program Updates the Quantity in the File
5) Program releases the Lock.
If the operator had gone to get a coffee before committing the Order Line, it also would be wise to check that there still is sufficient quantity on hand after step 2 as someone else may have sold some or all of the item in the "break".
Now this is not to say that B4X Programmers don't understand the above 5 steps - but to say that many programmers are unleashed into the Business World every day that would not even have had the experience of running a high volume database application.
Point is - BEWARE when you Lock
What gets really tricky is dealing with the facility of Committing a series of records.
This becomes close to useless when you have someone taking an order of say 25 items, then trying to update the database, while other operators have already re-sold the same stock. Here, you definitely need to use an extra field containing a total the stock that has been ordered by all operators combines which is checked against the Actual Stock on hand before taking more orders for the same item. Cancelling an order needs to UN-COMMIT the committed total to release stock that was previously reserved for an order.
Not too difficult to deal with - just a little tricky
And this is why you need a real database that performs like MS SQL Server
Now tell me, was I Locking Optimistically or Pessimistically ?
