Hi!

I'm developing a point of sale C/S application and I have a doubt about
locking records.
I will try to explain it with an example:

1) User 1 (computer 1) is editing, for example, a product price (Product A),
in the products table. Products table is in edit mode.
2) User 2 (computer 2) is preparing a sale of 3 products. One of these
products is product A.
3) User 2 (computer 2) press "make sale" to store the sale in the 'sales'
table, and the application needs to update 'products' table to change the
stock.
4) The products table is still in edit mode, because it is being edited by
User/Computer 1, so User/Computer 2 can't update the record.

The problem is that the sale operation is not stored due to the error while
trying to save/update the data, instead of waiting a few seconds.
However, what about if user1 NEVER press "save data" and release the record
lock?

What is the best practice to avoid this problem?

Thanks!

Santy C.

Santy


The answer will depend in part on how much freedom you have to alter things. From the brief post it reads as though stock control and product information is mixed on the same table. If correct that's bad news. Ideally you want that separating out. If you can't then here's a suggestion:

Assuming that you don't want two people selling the same part at the same time so that you can ensure availability, and that if the product file is being edited to alter the amount of stock you don't want them selling it:

1)  Move the general product table to unbound editing - this means that other processes can lock the product table without a problem. When posting back to the product table you'll have to check and see if anything's been changed and if so notify the user for action

2) Have a separate form for editing the product's stock details - make this bound so as to lock other people out when its being altered

3) Leave the sales function as is but if you want to you could get it writing into a transaction table and have a background process updating the product file. If you go down that route it makes checking the stock on hand more difficult since you not only have to check the product table but also the transaction table.

Basic principle is to keep the transaction times as short as possible so 1) means that the product table is only locked when the user has finished input and is trying to post.

If you have the freedom to do it then rip the transactional elements out of the static data and set up a separate stock table - nothing to sophisticated but just enough to prevent this problem.

You may also need a separate pricing table if that's currently held in the product table so that someone editing the product but not the price doesn't get in the way.

Roy Lambert [Team Elevate]

Hi, Roy...

Thanks for your reply.

The problem is that User1 can be editing the price, or the stock, or the
product name, or whatever product data, so using separated tables will not
solve the problem. There can be one of the tables that will require an
update.

Having a separate form is a good idea, but products table can be in edit
mode 'for years' if User1 forgets to press 'Apply Changes'.
The table will be in edit mode until User1 closes the Edit Form. And you
know how POS application users are

I have thought about trying to update the tables during the sale operation,
and if I get an error trying to save data, then alert the User2 that
'someone is editing one of the products you're trying to sell' and pause or
cancel the sale. But I don't know if this is correct, elegant or
professional.

Until now, the app begins updating the records 'one by one', and if an edit
error is raised, the loop is canceled, so not every products are updated.
I'm trying to first ensure the products are 'editable', and if yes, update
them. If not, cancel ALL updates.
I think this is possible using transactions, but I haven't used transactions
before and I am a little confused about it.

Is there any simple example to start with?

Thanks!



"Roy Lambert"  escribió en el mensaje de
noticias:85788C5B-CC63-44B2-A88C-839FFB98CDF6@news.elevatesoft.com...

Santy


The answer will depend in part on how much freedom you have to alter things.
From the brief post it reads as though stock control and product information
is mixed on the same table. If correct that's bad news. Ideally you want
that separating out. If you can't then here's a suggestion:

Assuming that you don't want two people selling the same part at the same
time so that you can ensure availability, and that if the product file is
being edited to alter the amount of stock you don't want them selling it:

1)  Move the general product table to unbound editing - this means that
other processes can lock the product table without a problem. When posting
back to the product table you'll have to check and see if anything's been
changed and if so notify the user for action

2) Have a separate form for editing the product's stock details - make this
bound so as to lock other people out when its being altered

3) Leave the sales function as is but if you want to you could get it
writing into a transaction table and have a background process updating the
product file. If you go down that route it makes checking the stock on hand
more difficult since you not only have to check the product table but also
the transaction table.

Basic principle is to keep the transaction times as short as possible so 1)
means that the product table is only locked when the user has finished input
and is trying to post.

If you have the freedom to do it then rip the transactional elements out of
the static data and set up a separate stock table - nothing to sophisticated
but just enough to prevent this problem.

You may also need a separate pricing table if that's currently held in the
product table so that someone editing the product but not the price doesn't
get in the way.

Roy Lambert [Team Elevate]

Santy


The only real solution to a user that keeps the table locked is to shoot the user! I know I experienced this problem years ago at a company I worked for as IT Manager. We had a moron who generally started work a couple of hours before everyone else. It was a Unisys mainframe app so we had good key logging. What would happen is he'd lock his terminal up. Because it was locked he'd move on to another terminal and lock that up. I think the best he managed was a dozen. To give an idea of his mindset the rest of the factory had moved over to metric but he stayed with imperial - the problem was he ordered pallets. We'd specify them in imperial - he'd convert to feet and inches and send the order. The manufacturers converted back to metric and occasionally we were delivered pallets we just couldn't use.

Before I comment about your problem I need answers to a couple of questions (more will probably come)

1. If Admin is changing the price should Sales wait until the new price is up before making the sale or should they sell at the old price
2. Are Sales allowed to make the sale when there is no stock - what happens about back ordering
3. What information is actually transferred from the product table to the sales table. You comment about product name bein altered makes me wonder. I would store the product code in the sales table not the product name, and the product code shouldn't be altered.

For an example of a transaction - chopped straight from the manual

begin
with MyDatabase do
begin
StartTransaction;
try
{ Perform some updates to the table(s) in this database }
Commit;
except
Rollback;
end;
end;
end;

Just remember to try again later

Roy Lambert [Team Elevate]

For an example of a transaction - chopped straight from the manual

Just remember to try again later

########################################################################
Just what I was doing well almost. Then you confused me with the
"Just remember to try again later
Is that not what the Rollback does ?
The below works ok for me Its for a SuperYankee bet 5 horses or whatever
you may bet on. I always did record locks before now I prefer Transactions
as I understood the Rollback keeps on trying.
I dont think anyone could go to lunch and leave the file locked with the
code
below as its linked to a button click marked "PlaceThisBet"
It works as is but needs error trapping adding.
*******************************************************************

with dm do begin
 dbHorse.StartTransaction;
 try
    Ticket.Refresh;
    Ticket.Edit;
    nRecno := Ticket.FieldByname('TicketNumber').AsInteger;
   // a one record table contains tax codes and record numbers..
  // assigned the last number above to this bet then
  // added + 1 in below code for next bet
    Ticket.FieldByname('TicketNumber').AsInteger := nRecno +1;
    Ticket.Post;

    Balance.Edit;
    nBalance := Balance.FieldByName('Balance').AsFloat;
    nStakes  := Balance.FieldByName('Stakes').AsFloat;    // grab the
balance and stake for this bet
    Balance.FieldByName('User').AsString :=  frmMain.LoggedOn;          //
get the users name
    Balance.FieldByName('Stakes').AsFloat := nStakes + nStake * nCount;
// update the table balance and stakes with above.
    Balance.FieldByName('Balance').AsFloat := nBalance - nStake * nCount;
    Balance.Post;

    Bets.insert;
    Bets.FieldByName('Horsename1').AsString :=  sH1;
    Bets.FieldByName('Meeting1').AsString := sM1;
    Bets.FieldByName('Horsename2').AsString :=  sH2;
    Bets.FieldByName('Meeting2').AsString := sM2;
    Bets.FieldByName('Horsename3').AsString :=  sH3;
    Bets.FieldByName('Meeting3').AsString := sM3;
    Bets.FieldByName('Horsename4').AsString :=  sH4;
    Bets.FieldByName('Meeting4').AsString := sM4;
    Bets.FieldByName('Horsename5').AsString :=  sH5;
    Bets.FieldByName('Meeting5').AsString := sM5;
    Bets.FieldByName('Time1').AsString := sTim1;
    Bets.FieldByName('Time2').AsString := sTim2;
    Bets.FieldByName('Time3').AsString := sTim3;
    Bets.FieldByName('Time4').AsString := sTim4;
    Bets.FieldByName('Time5').AsString := sTim5;
    Bets.FieldByName('UnitStake').AsFloat := nStake;
    Bets.FieldByName('TotalStake').AsFloat := nStake * nCount;
    Bets.FieldByName('WinReturns').AsFloat := nTotal - nStake * nCount;
    Bets.FieldByName('TotalReturns').AsFloat := nTotal;
    Bets.FieldByName('User').AsString :=  frmMain.LoggedOn;
    Bets.FieldByName('SP1').AsString := sSP1;
    Bets.FieldByName('SP2').AsString := sSP2;
    Bets.FieldByName('SP3').AsString := sSP3;
    Bets.FieldByName('SP4').AsString := sSP4;
    Bets.FieldByName('SP5').AsString := sSP5;
    Bets.FieldByName('Day').AsString := DateToStr(now);
    Bets.FieldByName('BetPlacedAt').AsString :=  DateTimeToStr(now);
    Bets.FieldByName('Winner').AsBoolean :=  false;
    Bets.FieldByName('RecordNumber').AsString := frmMain.strzero(nRecno);
    Bets.FieldByName('BetType').AsString := cb1.Text;
    Bets.FieldByName('bWin1').AsBoolean :=  false;
    Bets.FieldByName('bWin2').AsBoolean :=  false;
    Bets.FieldByName('bWin3').AsBoolean :=  false;
    Bets.FieldByName('bWin4').AsBoolean :=  false;
    Bets.FieldByName('bWin5').AsBoolean :=  false;
    Bets.FieldByName('bPlace1').AsBoolean :=  false;
    bets.Post;
    dbHorse.Commit;                  // post this bet unless somebody else
is trying
 except
    dbHorse.Rollback;                 // exception raised so tried again
until posted.
  end;
 end;

Rita


This

until posted.

is the bit that means keep on trying or it would if I could find the matching repeat statement

ROLLBACK does what it says on the tin - it rolls the transaction back and its as if it never happened.

a repeat .. until or a while .. end black will cause it to be retried, but if you don't leave an escape route then its still going to lock things up, especially if you get a deadlock situation.

In a POS application the customer doesn't give a stuff for what's happening in the background all he/she wants to do is get their goods and walk (paying for some of them is an optional extra) which is why, if possible I'd write to an intermediate table and then update the main tables as and when possible.

Roy Lambert [Team Elevate]

Hi, Roy...

>> 1. If Admin is changing the price should Sales wait until the new price
>> is up before making the sale or should they sell at the old price.>>

I'm afraid some users want option A, but other ones want option B

>> 2. Are Sales allowed to make the sale when there is no stock - what
>> happens about back ordering>>

Yes, they can make the sale, although it is a configurable option by admin.

>> 3. What information is actually transferred from the product table to the
>> sales table. You comment about product name bein altered makes me wonder.
>> I would store the product code in the sales table not the product name,
>> and the product code shouldn't be altered.>>

'Sales' can update the stock, price and/or even the name, depending on
configuration.

I have been testing transactions. I think it is the best method for this
kind of operations and applications, but I haven't tested the speed. Will
transactions decrease the speed? (My app is C/S and shared folder
compatible).

I didn't know that transactions Rollback can even undelete records!!
So now I think I'm going to use transactions, and show an alert when error
occurs, letting the user to retry the op, but I need to test the speed.

BTW: Should I use StartTransaction('products') instead of StartTransaction
because the products table is the only table that could give problems like
this during a sale?

Thanks!!

Santy C.




"Roy Lambert"  escribió en el mensaje de
noticias:15DEF212-6387-47CE-843D-F6B8699C7DCD@news.elevatesoft.com...

Santy


The only real solution to a user that keeps the table locked is to shoot the
user! I know I experienced this problem years ago at a company I worked for
as IT Manager. We had a moron who generally started work a couple of hours
before everyone else. It was a Unisys mainframe app so we had good key
logging. What would happen is he'd lock his terminal up. Because it was
locked he'd move on to another terminal and lock that up. I think the best
he managed was a dozen. To give an idea of his mindset the rest of the
factory had moved over to metric but he stayed with imperial - the problem
was he ordered pallets. We'd specify them in imperial - he'd convert to feet
and inches and send the order. The manufacturers converted back to metric
and occasionally we were delivered pallets we just couldn't use.

Before I comment about your problem I need answers to a couple of questions
(more will probably come)

1. If Admin is changing the price should Sales wait until the new price is
up before making the sale or should they sell at the old price
2. Are Sales allowed to make the sale when there is no stock - what happens
about back ordering
3. What information is actually transferred from the product table to the
sales table. You comment about product name bein altered makes me wonder. I
would store the product code in the sales table not the product name, and
the product code shouldn't be altered.

For an example of a transaction - chopped straight from the manual

begin
with MyDatabase do
begin
StartTransaction;
try
{ Perform some updates to the table(s) in this database }
Commit;
except
Rollback;
end;
end;
end;

Just remember to try again later

Roy Lambert [Team Elevate]

Santy

>>> 1. If Admin is changing the price should Sales wait until the new price
>>> is up before making the sale or should they sell at the old price.>>
>
>I'm afraid some users want option A, but other ones want option B
>
>>> 2. Are Sales allowed to make the sale when there is no stock - what
>>> happens about back ordering>>
>
>Yes, they can make the sale, although it is a configurable option by admin.
>
>>> 3. What information is actually transferred from the product table to the
>>> sales table. You comment about product name bein altered makes me wonder.
>>> I would store the product code in the sales table not the product name,
>>> and the product code shouldn't be altered.>>
>
>'Sales' can update the stock, price and/or even the name, depending on
>configuration.

With that lot I think the best suggestion is "don't start from here" <vbg>

>I have been testing transactions. I think it is the best method for this
>kind of operations and applications, but I haven't tested the speed. Will
>transactions decrease the speed? (My app is C/S and shared folder
>compatible).

Other way - done properly transactions should increase the speed.

>I didn't know that transactions Rollback can even undelete records!!
>So now I think I'm going to use transactions, and show an alert when error
>occurs, letting the user to retry the op, but I need to test the speed.

Since the operation take place in RAM with the actual disk update taking place with the COMMIT rolling back is essentially give the RAM back to Windows.

As I said above - it should be faster. What it won't do is kick a stupid user off. You say its POS so what's going to happen to the customer whilst waiting for the locks to clear?

>BTW: Should I use StartTransaction('products') instead of StartTransaction
>because the products table is the only table that could give problems like
>this during a sale?

Definitely not. ALL of the tables involved in the transaction MUST be in the list. Only add those that are affected ie where there's and insert, edit or delete.

Roy Lambert [Team Elevate]

Hi

Ok, I have been testing transactions with a simple app connected to a remote
server (C/S). It works fine, and I can show a message to the user with a
Retry option, etc...
In some cases I got a 'record has been changed or deleted by another
user...' error message, and I wonder which are the possible causes and how
to avoid them.

>> what's going to happen to the customer
>> whilst waiting for the locks to clear?

That's the question. No matter what method should I use. It is impossible to
avoid one user to lock a record indefinitely. I know that showing an alert
to the user is not something expected, but there's no solution. Maybe trying
to save the sale data to another temp table and try to execute the
transaction later?

I will do a few more tests.

Thanks a lot!


"Roy Lambert"  escribió en el mensaje de
noticias:0E473A9C-5DA6-4645-94D6-02520AE83ED0@news.elevatesoft.com...

Santy

>>> 1. If Admin is changing the price should Sales wait until the new price
>>> is up before making the sale or should they sell at the old price.>>
>
>I'm afraid some users want option A, but other ones want option B
>
>>> 2. Are Sales allowed to make the sale when there is no stock - what
>>> happens about back ordering>>
>
>Yes, they can make the sale, although it is a configurable option by admin.
>
>>> 3. What information is actually transferred from the product table to
>>> the
>>> sales table. You comment about product name bein altered makes me
>>> wonder.
>>> I would store the product code in the sales table not the product name,
>>> and the product code shouldn't be altered.>>
>
>'Sales' can update the stock, price and/or even the name, depending on
>configuration.

With that lot I think the best suggestion is "don't start from here" <vbg>

>I have been testing transactions. I think it is the best method for this
>kind of operations and applications, but I haven't tested the speed. Will
>transactions decrease the speed? (My app is C/S and shared folder
>compatible).

Other way - done properly transactions should increase the speed.

>I didn't know that transactions Rollback can even undelete records!!
>So now I think I'm going to use transactions, and show an alert when error
>occurs, letting the user to retry the op, but I need to test the speed.

Since the operation take place in RAM with the actual disk update taking
place with the COMMIT rolling back is essentially give the RAM back to
Windows.

As I said above - it should be faster. What it won't do is kick a stupid
user off. You say its POS so what's going to happen to the customer whilst
waiting for the locks to clear?

>BTW: Should I use StartTransaction('products') instead of StartTransaction
>because the products table is the only table that could give problems like
>this during a sale?

Definitely not. ALL of the tables involved in the transaction MUST be in the
list. Only add those that are affected ie where there's and insert, edit or
delete.

Roy Lambert [Team Elevate]

Santy

>Ok, I have been testing transactions with a simple app connected to a remote
>server (C/S). It works fine, and I can show a message to the user with a
>Retry option, etc...
>In some cases I got a 'record has been changed or deleted by another
>user...' error message, and I wonder which are the possible causes and how
>to avoid them.

Causes - exatly what it says I would guess. How to avoid them - remove all users from the system Or more correctly you can't

>>> what's going to happen to the customer
>>> whilst waiting for the locks to clear?
>
>That's the question. No matter what method should I use. It is impossible to
>avoid one user to lock a record indefinitely. I know that showing an alert
>to the user is not something expected, but there's no solution. Maybe trying
>to save the sale data to another temp table and try to execute the
>transaction later?

That's pretty near what I'm suggesting. Thinking some more about it what I'd suggest is the following:

1. At the POS PC don't try and lock the Products table - just grab the information from it when the sale starts. As the frontend of a POS application stock levels and accuracy aren't an issue - if there's no stock the customer can't pick it up Selling price, product description don't seem to be a problem since sales can override those so forget them. Locking problem solved at the customer point.

2. On completion of the sale, obviously, post the information into the sales table AND post the stock movement into a transaction table. Set up a background process running on the server which trawls the transaction table and, using DBISAM transactions, attempts to update the Product table, if it can't it moves on to the next transaction and tries to catch up the missed ones on the next cycle.

3. Generate a report showing which transactions couldn't be posted to the Products table for whatever timespan you choose. You could even send a warning to a manager - product X has been locked for the last 87 days - has Fred died? Or somesuch.

Roy Lambert [Team Elevate]