MySQL PHP Transactions and table locking

[random1]

Hi All,

 

How do you do transactions and lock tables in mySQL? I am running an application that requires the use of transactions.

 

I also want to enforce locking rules so that users of my website don't edit the same data and the same time. How do I do this?  ???

[aschk]

<?php

// Start transaction
mysql_query("BEGIN");

// do lots of SQL'y stuff here...

// Commit transaction.
mysql_query("COMMIT");

?>

 

Locking tables is not a good idea, and in fact MySQL deals with it's own version of row/table locking so that you don't have to bother with it.

Because you're using a transaction until the item is commited they're working with a virtual copy of the table, i.e. what they see while inside the transaction doesn't actually represent what is in the current table layout.

 

Thus, you don't need to worry about it. MySQL will deal with all the thinking for you.

[able]

Hopefully it goes without saying, but you need to make your tables either innodb or bdb for transactions to work. If you don't, you can still perform begin/commit/rollback - but they won't have any effect.