Lock tables

[meee]

I am not sure if I understand correctly lock tables statement. I hope somebody can explain this situation:

I would like to lock table photos, so nobody can insert new photo while user A is inserting photo and getting inserted id.

photos
photoId | photo | userId

$db->query("LOCK TABLES photos write");
$db->query("INSERT INTO photos (userId,photo) VALUE (1,'$photo')");
$photoId=mysql_insert_id();
$db->query("UNLOCK TABLES"); 

I tried this in localhost. First I removed the last line ($db->query("UNLOCK TABLES")), so when a user A open the script, table stays locked. I opened script test.php in firefox and then the same script as user B in chrome. As user B I didn't get any error and new photo has been inserted. Why that? Isn't supossed to get error in this case as table is still locked?

tnx!

[r937]

you don't need to do that

mysql_insert_id is guaranteed to get you the correct value for each separate connection

[meee]

So there is no chance that another user will in exact the time between insert and mysql_inser_id insert another photo or is it just so small possibility?

But anyway if I or somebody else needs that for other purposes. I think my the problem was that after the php script ends, it also unlock table automatically. Normally lock will means that execution of script will wait for beeing table unlocked, without any error. Am I right?

[jurn]

So there is no chance that another user will in exact the time between insert and mysql_inser_id insert another photo or is it just so small possibility?

But anyway if I or somebody else needs that for other purposes. I think my the problem was that after the php script ends, it also unlock table automatically. Normally lock will means that execution of script will wait for beeing table unlocked, without any error. Am I right?

no, the php mysql insert id / auto_increment is guaranteed to be unique, MySQL will handle this internally.
http://dev.mysql.com/doc/refman/5.0/...increment.html

Yes, I think it will be fine in most cases,
BUT what happens if your php script dies right after it 'locks tables'.
and it can also put extra load on the database - mysql will be handle the concurrency around the id generation, but you're making it handle concurrency for the whole table.

[meee]

jurn I just read today OReilly Learning PHP MySQL and JavaScript book and the author wrote about this function:
"But there’s a slight window of opportunity for an error to slip in. Suppose that two
people visit the website at the same time and submit new information, causing the web
server to run your program twice at the same time. (Web servers can run several programs
at the same time to speed up response time.) The second visitor might insert a
new cat just before the first visitor’s program issues mysql_insert_id. This is a rare but
serious problem, because the first person could end up being associated with the second
person’s cat.
So a completely safe procedure for linking tables through the insert ID is to use locks"

[jurn]

jurn I just read today OReilly Learning PHP MySQL and JavaScript book and the author wrote about this function:
"But there’s a slight window of opportunity for an error to slip in. Suppose that two
people visit the website at the same time and submit new information, causing the web
server to run your program twice at the same time. (Web servers can run several programs
at the same time to speed up response time.) The second visitor might insert a
new cat just before the first visitor’s program issues mysql_insert_id. This is a rare but
serious problem, because the first person could end up being associated with the second
person’s cat.
So a completely safe procedure for linking tables through the insert ID is to use locks"

I acquired the book because you made me worry
See
http://forums.oreilly.com/content/Le...sql-insert-id/
The author gracefully admits to a mistake.

[r937]

jurn, awesome update, thanks for that

in any dispute between the vendor's docs and a book, i would go with the docs

you're right, that was a graceful admission