sewells
|
| Posted: 06/01/2003, 9:13 PM |
|
I was going throught he bookstore demo in detail in ccs 2. In the custom code associated with the purchase button i see this comment around this bite of code:
'Get Order ID
'This method compatible with all databases (unsafe when multiple users insert records at the same time)
'Use your own method for your database.
SQL = "SELECT MAX(order_id) FROM store_orders"
Set RecordSet = DBInternetDB.Execute(SQL)
last_order_id = CCGetValue(RecordSet, 0)
Well guess what? Typical systems have multiple users doing work at the same time and these livelock/race conditions can kill you. Is this a fundamental problem in CCS and the bookstore ap?? Same warning about the race also exists in the header code which sets the cookie.
Any thoughts on preventing these problems?
|
|
|
 |
RonB
|
| Posted: 06/02/2003, 7:36 AM |
|
depends on your database and the amount of people doing transactions at the same time.
MySQL (from the manual):
How Can I Get the Unique ID for the Last Inserted Row?
If you insert a record in a table containing a column that has the AUTO_INCREMENT attribute, you can get the most recently generated ID by calling the mysql_insert_id() function.
You can also retrieve the ID by using the LAST_INSERT_ID() function in a query string that you pass to mysql_query().
You can check if an AUTO_INCREMENT index is used by executing the following code. This also checks if the query was an INSERT with an AUTO_INCREMENT index:
if (mysql_error(&mysql)[0] == 0 &&
mysql_num_fields(result) == 0 &&
mysql_insert_id(&mysql) != 0)
{
used_id = mysql_insert_id(&mysql);
}
The most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0).
If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like this:
INSERT INTO foo (auto,text)
VALUES(NULL,'text'); # generate ID by inserting NULL
INSERT INTO foo2 (id,text)
VALUES(LAST_INSERT_ID(),'text'); # use ID in second table
MySQL Reference Manual (C) 2002 MySQL AB
- You can also set the id to timestamp. This will only cause a problem if the transactions are at EXACTLY the same time. You could do a before insert event to check this or an on validate event to check also. If the id exists repeat the insert with id+1 until a unique id is generated.
- an even more farfetched alternative is not to use an auto increment id at all. make an id from a combination of things(timestamp, random number etc etc) that way you can be sure no combination will ever come up twice.
Ron
|
|
|
|
sewells
|
| Posted: 06/02/2003, 9:16 AM |
|
Thanks for a thoughtful reply Ron.
I'm somewhat new to doing server based, multi-user, web based systems. I am, however, very experienced in getting multi-threaded / multi-live-user systems right.
It always boils down to successfully implementing an atomic operation for semiphors and then using the darn things when you are suppose to. The really really realy hard part of building any multi-x application is getting the races and livelocks out. It takes literally a (countably) infinite amount of time to validate these systems since you can only isolate and debug those systems with finite probabilty with repeatability being a pain! I suspect I'll choose the timestamp strategy you mentioned (including milliseconds) because I have no guarantee that the insert record with autoincrementing/random keys is atomic. My only concern is that if I move to a multi-cpu server, I could be in trouble. I'll likely take the timestamp and XOR it with a random number to help with that situation.
Obviously bookstore demo choose to ignore these issues. My problem was that with the bookstore demo alone I bid a job and assumed the worked through these issues and now I have to do this extra work gratis.
---STeve
|
|
|
|
RonB
|
| Posted: 06/02/2003, 4:00 PM |
|
ahhh but.... never assume anything but the worst case scenario is what our strategy should be!! Fell for this kind of mishap myself a couple of times. You and I probably cut corners here and there because doing it right would take a lot of time, wich isn't always in great supply. I know there are a few complicated, but rough, sql solutions in the pages I coded for some of the companies webapplications. Nothing that would cause trouble, but it could have been done more resource friendly if I'd taken the time. Sometimes the fast way is to atractive 
I guess the people at Yes sometimes have the same dillema. This guy new it and even went to the trouble of mentioning it in the code.
Ron
|
|
|
|
DaveRexel
|
| Posted: 06/02/2003, 6:03 PM |
|
Hi Steve & Ron,
This is the most interesting thread I've seen on this forum in quite some time. Thanks for many insights into cases where users overlap and may overwrite each others data.
Dave
|
|
|
|
glerma
|
| Posted: 06/02/2003, 9:05 PM |
|
'Get Order ID
'This method compatible with all databases (unsafe when multiple users insert records at the same time)
'Use your own method for your database.
SQL = "SELECT MAX(order_id) FROM store_orders"
Set RecordSet = DBInternetDB.Execute(SQL)
last_order_id = CCGetValue(RecordSet, 0)
This is just a plain bad way of getting the last order_id from the database anyway.
I don't work with MySQL, but I gotta assume that it has better implementations for situations like this. For example, I work with Oracle and they have features that allow for "read-consistency" to make sure that transactions don't step on each others toes. They also have better ways of getting the last or first row of data. It's called "Top-N" analysis. It uses the ROW_ID psuedonym.
Anyway this is not a plug for Oracle or anything. I just think that MySQL, which is a very top-notch relational database system should have things in place to prevent this situation.
|
|
|
|
rclayh
|
| Posted: 06/04/2003, 2:51 AM |
|
First, Ron thanks for the lesson..
Second, given that Oracle is at 9 and MySQL at 4, it's understandable that there may be things lacking.
Also when you compare prices....
|
|
|
|
RonB
|
| Posted: 06/04/2003, 8:35 AM |
|
This has nothing to do with mysql, it has excelent features in place to prevent these kind of things. It's just a case of a yes programmer not wnating to get all complicated on himself. He admit's it to, look at the comment. So this is not mysql related it HAS functions to prevent multiuser hell
Ron
|
|
|
|
|
