I can't lock tables from a MySQL5 procedure? How should I do it??

[JonBlockBoston]

I've created a procedure that operates on several critical tables in my database. I've already wrapped it in a transaction. However, it will be important that during the execution of this routine, that other threads aren't modifying the tables. That means I need talbe LOCKs.

What's the MySQL way of running a large routine with transactions and locks if you can't use locks within a procedure?

Thank you!
Jon

[YAOMK]

Transactions should do it, are you using innodb as the table engine, for those tables you wish to lock?

[MarkR]

In general you shouldn't mix transactions and locks. It's a recipe for disaster.

If you are in a transaction, you can use SELECT ... FOR UPDATE to force there to be a lock on those rows that you are planning on updating. This is fine, but you can still deadlock another procedure doing the same thing if not careful.

However, the client application should be able to deal with deadlocks in some useful way (rollback, retry etc)

Mark

[YAOMK]

Here is a nice discussion that extends the issue with transactions as MarkR pointed out. You must build the necessary logic into your application to deal with deadlocks.

[JonBlockBoston]

Mark - I don't understand why you'd say this is a recipe for disaster. I'm exactly trying to avoid a disaster by using locks and transactions. Here's why:

I have 3 tables "a, b, and c". I have a series of operations that must be perfoemed against each of them and together as an atomic unit of work. That's a "transaction".

It's also important that no other sql routines, that happen to be running at the same time, are modifying tables "a, b, or c". That would cause a problem.

That's why I think my sql commands must fire in this order:

START TRANSACTION
LOCK TABLES a WRITE, b WRITE, c WRITE;
(some sql queries appear here)
COMMIT;
UNLOCK TABLES;

How could I possibly have a situation where something bad happens? It would seem to me that these instructions would exclusively block out other concurrent threads from accessing tables "a, b, and c" while my transaction runs. No matter if the transaction fails or succeeds, at the conclusion, it should be business as normal because I run the UNLOCK TABLES command.

Is there something here I don't understand?
Jon

[YAOMK]

From the MySQL manual:

LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables. Also, beginning a transaction (for example, with START TRANSACTION) implicitly performs an UNLOCK TABLES. (See Section 12.4.3, “Statements That Cause an Implicit Commit”.)

[JonBlockBoston]

YAOMK,

Thank you for this reply - it is helpful. Now that I understand the discreet behaviors of transactions and lock statements, how do you suggest I solve the problem that I've described in my example with MySQL?

Jon

[YAOMK]

Take a closer look at MarkR's post, he suggested using SELECT to lock all those records you plan to update.

[JonBlockBoston]

YAOMK,

The crazy thing I've discovered is that even though the documentation tells us that locks and transactions are not meant to work together, in the test file I created, they are working together the way that I had expected!!

Here was my test plan:

1) Ommitted the commit statement. My SQL changes were not applied.
2) Restored the commit statement. The SQL changes applied successfully.
3) I then added a php "sleep(60)" command just after the first LOCK statement, and then requested the php file with my browser. I then opened a new SQL query window and tried to UPDATE one of the tables that was locked. The SQL procedure waited for 60 seconds!!! Just as soon as the PHP thread UNLOCKED the table, the UPDATE statement fired successfully!
4) Finally, I removed the LOCK TABLES command. I ran my PHP script with my browser and the PHP routine paused on my sleep(60) command. I then went to my SQL window and ran my UPDATE statement, and it executed right away.

START TRANSACTION
LOCK TABLES a WRITE, b WRITE, c WRITE;
(some sql queries appear here)
COMMIT;
UNLOCK TABLES;

In summary, these commands are working the way I had thought they would. However, they perfectly contradict what the documented behavior or MySQL is. Do you have any advice?

Jon

[YAOMK]

I see your point. I think you are committing your transaction when you use LOCK TABLES, but then I cannot figure out the roll back/commit behavior that follows, but you never know how this is going to work when you have multiple connections running queries in your db.

Try testing without the LOCK/UNLOCK TABLES command. Simply do a "SELECT * FROM table" to lock all the records that exist in that table, then run your queries and commit or roll back depending on whether you get any errors from your dbms. Also try to implement the retry logic as explained in the link on my other post, in case you run into a possible deadlock

[JonBlockBoston]

I think I've figured it out. One thing that's in my procedure is a

$dbhandle->autocommit(FALSE)

I looked up the behavior of autocommit and I see...

"If the autocommit mode is switched off with SET AUTOCOMMIT = 0, then we can consider that a user always has a transaction open. An SQL COMMIT or ROLLBACK statement ends the current transaction and a new one starts."

http://dev.mysql.com/doc/refman/5.0/en/innodb-and-autocommit.html

Therefore, if you were to look at my code again:

START TRANSACTION
LOCK TABLES a WRITE, b WRITE, c WRITE;
(some sql queries appear here)
COMMIT;
UNLOCK TABLES;

I think the order of events is:

Line 1: A new transaction is started.
Line 2: The open transaction ends because LOCK TABLES was called. However, a new transaction is also started according to the AUTOCOMMIT documentation that reads "If the autocommit mode is switched off with SET AUTOCOMMIT = 0, then we can consider that a user always has a transaction open.".
Line 3: All of my funky sql commands run within a LOCKED TRANSACTION! :-)
Line 4: I intentionally COMMIT my transaction. That also implicitly UNLOCKS TABLES.
Line 5: I manually unlock the tables.

I think that I can pretty much remove "Line 1" and "Line 5" and still get the desired behavior.

-Jon

[YAOMK]

I assume your db class is using mysqli. Here is an abstract from a Tutorial by Alejandro Gervasio which implements the mysqli extension to conduct transactions:

$mysqli=new mysqli('host','user','password','database');
if(mysqli_connect_errno()){
    trigger_error('Error connecting to host. '.$mysqli-
error,E_USER_ERROR);
}
// turn off AUTOCOMMIT, then run some queries
$mysqli->autocommit(FALSE);
// delete all rows in 'customers' table
$mysqli->query("DELETE FROM customers");
// display number of rows
if($result=$mysqli->query("SELECT COUNT(*) FROM customers")){
    $row=$result->fetch_row();
    echo 'Number of rows in CUSTOMERS table '.$row[0];
    // free result set
    $result->close();
}
// rollback transaction
$mysqli->rollback();
// display number of rows
if($result=$mysqli->query("SELECT COUNT(*) FROM customers")){
    $row=$result->fetch_row();
    echo 'Number of rows in CUSTOMERS table '.$row[0];
    // free result set
    $result->close();
}
// close connection
$mysqli->close();