[RESOLVED] Script/user-agent completion order for multiple near-simultaneous requests

[cbj4074]

This is a difficult question on which to search, but it should be an easy one to answer, so my apology in advance if this has been discussed before.

I have a PHP script that connects to a MySQL database and executes a few queries (an insert and then a select – and the select gets data from the first insert). Suppose for the sake of argument that I have 1,000 simultaneous users hitting that page. Am I absolutely guaranteed that each user-agent request will be completely fulfilled in the order in which it was received? In other words, is there any chance that a SQL insert for User 2 is executed before the entire PHP script (including the select) has completed for User 1?

If so, is that how it works for all server-side scripting engines?

Thanks in advance.

[power_strider]

Hi,
Please try the following discussion, i think you can solve your problem with the help of that
http://weblogs.macromedia.com/cantrell/archives/2004/11/safely_selectin.cfm

[Plasma]

You can use table locking to prevent any other thread from reading and/or writing to specified tables to make it transaction safe.

[cbj4074]

Sweet, thank you both for the suggestions. I like the idea of table locking.

I'm using MySQL, and this is excerpted from the manual:

---

7.3.2. Table Locking Issues

To achieve a very high lock speed, MySQL uses table locking (instead of page, row, or column locking) for all storage engines except InnoDB and BDB.

For InnoDB and BDB tables, MySQL uses only table locking if you explicitly lock the table with LOCK TABLES. For these storage engines, we recommend that you not use LOCK TABLES at all, because InnoDB uses automatic row-level locking and BDB uses page-level locking to ensure transaction isolation.

---

I take this to mean that MySQL automatically locks MyISAM tables on a transactional basis without explicitly having to resort to

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

Am I incorrect in this assumption?

Thanks!

[MarkR]

No, you can guarantee nothing.

The operating system may choose to switch tasks at any time, including interleaving the execution of two PHP pages in any way it feels.

You should design your application so that it works despite this. This should not be difficult:

• If you've just done an INSERT, why do you then need to select the data back out? It will just be what you inserted
• If for some reason, you have to do this, why not use the auto generated ID to select it? The last insert ID is held on a per-connection database, and won't return an ID of a more recently inserted row if it was done by a separate process.

Mark

[cbj4074]

Thanks, that was exactly the information I was looking for, Mark. You make a very good point =)