Re: Proper syntax for multiple query string

[Ivan D]

Hey Felix, I read up on transactions and locking. Transactions seem friendlier in that they won't hog as much. Their purpose seems to be solely for undoing changes if you decide somewhere along the way that the transaction needs to be cancelled. However does it guarantee that I'm the only one making changes to a specific record, or is this where locking is absolutely needed.

Example:
Given these queries (sent one by one from a php script)

START TRANSACTION;
SELECT id FROM table1 WHERE rcl=1;
(if not empty remember the id)
UPDATE table1 SET something='$something' WHERE id=$id;
COMMIT;

am i still running the risk of another script attempting the same transaction and changing the same record. Or will that other script successfully find the next available record with rcl=1? It would seem this risk is there.

Should this be done as

LOCK TABLES table1 WRITE;
SELECT id FROM table1 WHERE rcl=1;
(if not empty remember the id)
UPDATE table1 SET something='$something' WHERE id=$id;
UNLOCK TABLES;

When there is a lock on the rable, does the script wait untill its query can finally be processed, or will it get some sort of error and have to keep sending its query over and over untill it finally goes through? (i hope its the 1st of the 2)

It would be really cool if they made it so you can lock only a specific record(s), and then the script that did locking can read/write to those records. Anyone else would still be able to select it, but it would be marked as locked, and they would not be able to make changes. That would be usefull.

Tell me what you think
Thanks for all your help so far.