MySQL Forums
Forum List  »  Newbie

Re: Are queries queued?
Posted by: Rick James
Date: June 03, 2009 07:40PM

Suggest you keep it simple. If your volume gets to the "millions per day" stage, you may hit some of the issues you mentioned.

Let's see if I can spell out the flow

* User logs in (PHP script controls this; perhaps SQL to deal with his password?)
* User searches for something (PHP -- SELECT...LIMIT 3 + Lock those three)
* User clicks on some of them and "submits" (PHP & SQL to "buy" them)

Note: Each of the three steps is separate interactions with the web server. So, I assume you are passing a cookie (or something) back through the user in order to keep track of him. Cookie is the best way, and the best cookie is probably a "session" number that was assigned when he logged in. Also, the cookie needs to "expire" so that he can come back tomorrow without logging in again (actually to keep someone from using his computer). To verify the cookie, there need to be extra SQL statements to look up the session, etc.

The "lock" will be only long enough to flag the 3 items as "reserved" for him. And other searches must avoid returning the reserved items to other users.

Since you have another job that is "clearing" the "reserved" flag after 3 minutes, it may be better to have all the reserving mechanisms in another table, something like
CREATE TABLE reservations (
   id INT UNSIGNED AUTO_INCREMENT NOT NULL,
   prod_id  INT NOT NULL  COMMENT 'for joining to products',
   ts TIMESTAMP NOT NULL  COMMENT 'when reservation made',
   user_id ...,
   PRIMARY KEY (id),
   INDEX (ts),
   INDEX (prod_id),
   INDEX (user_id)
);

Reserving: Insert 3 rows.
Clearing: DELETE ... WHERE ts < DATE_SUB(NOW(), INTERVAL 3 MINUTE)
Buying: ?

Assuming you go with this extra tables, then
For MyISAM:
LOCK TABLES products, reservations FOR WRITE;
...
UNLOCK TABLES;

For InnoDB:
BEGIN TRANSACTION;
search, making sure to avoid any rows that are already reserved
Insert 3 rows
COMMIT;

What I am describing is possibly slower than trying to do it all in the 'products' table, but I like it better. Anyway, I have not heard that you will have enough traffic to care about speed. This should easily handle a few thousand 'actions' in a 3 minute period. (And, hence, there won't be more than a few thousand rows in the new table.)

Options: ReplyQuote


Subject
Written By
Posted
May 15, 2009 05:32AM
May 15, 2009 01:27PM
May 15, 2009 02:50PM
May 16, 2009 07:19PM
May 16, 2009 07:26PM
May 18, 2009 03:38PM
May 18, 2009 03:35PM
May 18, 2009 11:16PM
June 03, 2009 02:37PM
Re: Are queries queued?
June 03, 2009 07:40PM
June 05, 2009 01:35PM
June 05, 2009 08:47PM
June 06, 2009 11:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.