about Rev cgi and MySQL

[Nicolas Cueto]

Just curious about something. But I don't know the technical
vocabulary, so please forgive this verbose (and confusing?)
explanation.

I think it essentially has do with user queues.


I have a Rev.cgi script on the On-Rev server that works in this 3-step sequence:

(STEP 1) uses SELECT to retrieve data from a MySQL table

(STEP 2) does stuff based on that retrieved data in order to alter it

(STEP 3) uses UPDATE to put that now-altered data back into the same MySQL table


My question is about data mishaps that could happen during STEP 2 --
specially if it happens to take a long time (a few seconds?) -- should
it happen to come about that a new user calls a Rev.cgi which is still
working on a previous user's call.


The chaos sequence I have in mind is this:

USER 1 : STEP 1 --> Rev.cgi retrieves data from SQL table at time X ( = tData)
USER 1 : STEP 2 --> Rev.cgi works with tData to make tData_User1 (but
no UPDATE yet)

... but during USER 1 : STEP 2 ...

USER 2 : STEP 1 -->   Rev.cgi retrieves data from SQL table at time
X+1 (still = tData)
USER 2 : STEP 2 --> Rev.cgi works on tData to make tData_User2 (but no
UPDATE yet)
USER 1 : STEP 3 --> updates SQL table with tData.User1
USER 2 : STEP 3 --> updates SQL table with tData.User2

In this chaos scenario of mine -- if indeed this is how things
actually happen between servers, which I'm hoping it's not -- the
problem is the state of tData at USER 2 : STEP2. It should not be
tData but actually tData_User1.


So, is there something in-built in SQL or Rev.cgi that handles queues
in such a way that prevents this kind of queue chaos, whether it's 2
or 2 million users calling in at the "same" time

Or do I have to take care of this myself somehow, by, say, proper scripting?

Thank you.

--
Nicolas Cueto
___
use-revolution mailing list
use-revolution@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution

Re: about Rev cgi and MySQL

[Jan Schenkel]

This problem isn't unique to cgi-scripting - you have the same issues with a 
desktop application where concurrent updates need to be handled.

There are 3 approaches to concurrent changes:
- pessimistic locking (no one can change the record while somebody has it 
locked)
- optimistic locking (essentially no locking, whoever makes the last update 
wins)
- optimistic locking + versioning (again no locking, byut we use a version 
number to see if anyone else made a change)

The first approach, pessimistic locking, doesn't scale very well and you risk 
locking out users when someone opens a record and goes out for lunch without 
closing the record.
The second approach, optimistic locking without versioning, is just not an 
option in a serious business application; but it may fit the bill under other 
circumstances as it's the quickest.

Which leaves us with the third option, optimistic locking + versioning. In this 
scenario, you add a 'version' column to each table, and when you update a 
record, you only update it if the version number is the same as when you read 
it.
So when opening the record, you'd have:
  SELECT * FROM Customer WHERE cust_Id = 12345
Then you copy the version number for later use:
  put revDatabaseColumnNamed(tResultSetId, "cust_Version") into tVersion
Let's assume for a second that the version number is 8. When you want to save 
the changes, you would use a query like:
  UPDATE Customer SET cust_Name = 'Jan', cust_Version = cust_Version + 1 WHERE 
cust_Id = 12345 AND cust_Version = 8

When you use the revExecuteSQL command, the result contains the number of 
records affected by your UPDATE query. If the result for the above query is 0, 
then no records were updated, which means that someone else incremented the 
version number.
At this point, you could read the new version of the record and provide some 
sort of 'merge' functionality where the user picks the right fields.

Important note: when using a 'version' field, make sure to extract non 
user-entry data our of the table ionto a separate table. For instance, if 
there's a field cust_Balance to hold the current balance of the cusztomer, you 
should move that out of the Customer table, so that the rest of your business 
logic can update that without worrying about a version number.

Hope this helped,

Jan Schenkel.
=
Quartam Reports & PDF Library for Revolution
<http://www.quartam.com>

=
"As we grow older, we grow both wiser and more foolish at the same time."  (La 
Rochefoucauld)


--- On Mon, 11/2/09, Nicolas Cueto  wrote:

> From: Nicolas Cueto 
> Subject: about Rev cgi and MySQL
> To: "How to use Revolution" 
> Date: Monday, November 2, 2009, 3:47 PM
> Just curious about something. But I
> don't know the technical
> vocabulary, so please forgive this verbose (and
> confusing?)
> explanation.
> 
> I think it essentially has do with user queues.
> 
> 
> I have a Rev.cgi script on the On-Rev server that works in
> this 3-step sequence:
> 
> (STEP 1) uses SELECT to retrieve data from a MySQL table
> 
> (STEP 2) does stuff based on that retrieved data in order
> to alter it
> 
> (STEP 3) uses UPDATE to put that now-altered data back into
> the same MySQL table
> 
> 
> My question is about data mishaps that could happen during
> STEP 2 --
> specially if it happens to take a long time (a few
> seconds?) -- should
> it happen to come about that a new user calls a Rev.cgi
> which is still
> working on a previous user's call.
> 
> 
> The chaos sequence I have in mind is this:
> 
> USER 1 : STEP 1 --> Rev.cgi retrieves data from SQL
> table at time X ( = tData)
> USER 1 : STEP 2 --> Rev.cgi works with tData to make
> tData_User1 (but
> no UPDATE yet)
> 
> ... but during USER 1 : STEP 2 ...
> 
> USER 2 : STEP 1 -->   Rev.cgi retrieves
> data from SQL table at time
> X+1 (still = tData)
> USER 2 : STEP 2 --> Rev.cgi works on tData to make
> tData_User2 (but no
> UPDATE yet)
> USER 1 : STEP 3 --> updates SQL table with tData.User1
> USER 2 : STEP 3 --> updates SQL table with tData.User2
> 
> In this chaos scenario of mine -- if indeed this is how
> things
> actually happen between servers, which I'm hoping it's not
> -- the
> problem is the state of tData at USER 2 : STEP2. It should
> not be
> tData but actually tData_User1.
> 
> 
> So, is there something in-built in SQL or Rev.cgi that
> handles queues
> in such a way that prevents this kind of queue chaos,
> whether it's 2
> or 2 million users calling in at the "same" time
> 
> Or do I have to take care of this myself somehow, by, say,
> proper scripting?
> 
> Thank you.
> 
> --
> Nicolas Cueto
>

Re: about Rev cgi and MySQL

[Rick Harrison]

Hi,

There are some other compromising approaches to consider as well.

Don't have the record open long at all in your processing.
If it is a form, don't use the database field for your entry, use
a variable for the entry, so the record isn't actually open.
Once the user actually submits the information, then open
your record, copy the information from the variables into
the record, and perform your update.  In this way you will
use the least amount of time for your record locking technique.

Use a record locking strategy where you can use a
Date/Time stamp along with a session ID, or IP-Address,
when the record was opened for updating.
If the record isn't updated within a specified amount of time,
let it time out, send a message to the user that the update
timed out, and direct them to do it over. Close the record
so someone else can update it.

If someone else is trying to update the same record at the same
time, have the program wait for a couple of seconds and try
the update again. (You can loop this for several tries before
giving up.  If it still fails at this point you can send that user a
record busy message, ask them to wait, or ask them to try
again in a minute, or to cancel their update for later.)

I hope this helps!

Rick

On Nov 3, 2009, at 8:28 AM, Jan Schenkel wrote:

This problem isn't unique to cgi-scripting - you have the same  
issues with a desktop application where concurrent updates need to  
be handled.


There are 3 approaches to concurrent changes:
- pessimistic locking (no one can change the record while somebody  
has it locked)
- optimistic locking (essentially no locking, whoever makes the last  
update wins)
- optimistic locking + versioning (again no locking, byut we use a  
version number to see if anyone else made a change)

...
Hope this helped,

Jan Schenkel.
=
Quartam Reports & PDF Library for Revolution


=
"As we grow older, we grow both wiser and more foolish at the same  
time."  (La Rochefoucauld)




___
use-revolution mailing list
use-revolution@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution