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 <niconiko at gmail.com> wrote:

> From: Nicolas Cueto <niconiko at gmail.com>
> Subject: about Rev cgi and MySQL
> To: "How to use Revolution" <use-revolution at lists.runrev.com>
> 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
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage
> your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution
>