I am working on a Perl / DBI / DBD / MySQL application. I am using InnoDB with transaction level "repeatable read" and "autocommit = 0". To protect data which I am manipulating against concurrent manipulation by other threads or connections, I am using the following pattern (which is quite common):
$h_DB -> do("START TRANSACTION");
$s_SQL = "SELECT data from Users where ID = ? FOR UPDATE";
$h_ST = $h_DB -> prepare($s_SQL);
$h_ST -> execute($s_ID);
# Do some other stuff
@ar_Row = $h_ST -> fetchrow_array();
# Do data manipulation
$h_DB -> do("COMMIT")
For clarity, I have left away the error handling and the variable declarations, but I think the variable names are self-explaining.
My question is: When exactly are the locks (in this case: the exclusive locks) put onto the respective row? Are they already in place after the execute has run, or are they active no sooner than after the row has been fetched? In other words, are the locks already in place in the section designated "# Do some other stuff"?
Normally, it is easy to answer such questions by doing some research using the MySQL command line tools. But in this case, I think this is not possible for the following reasons:
1) I am not sure if the MySQL client behaves exactly like Perl's DBI / DBD.
2) Of course, the example above is extremely simplified. Actually, I'd like to know when the locks are set when using the pattern above, but locking several hundred rows at once, i.e. several hundred rows matching the WHERE clause.
I don't think I could use the command line client to find out because with large data sets there might be buffering which might be handled differently by Perl and the command line client, and I doubt that I could "simulate" a statement which has been executed in Perl, but whose result rows have not been fetched yet, by using something like "LIMIT 0" in the command line client.
Could somebody give a definitive answer?
$s_SQL = "SELECT data from Users where ID = ? FOR UPDATE";
Executing this will try to acquire a lock on the affected rows and then fetches the data (or waits until it gets an exclusive lock for all affected rows). This is done in order to be thread safe and only return data which is already locked: This way it is guaranteed the server returns the latest data which can't be modified by any other client/thread.
The rows will be locked until commit or rollback.
The behavior in MySQL cli client is the same as the locking is done in the server and not in the client library.
This only holds for MySQL InnoDB tables. Other engines like MyISAM behave differently.