MySQL, Perl DBI, execute() and locking (exact moment of locking)

Question

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?

Answer 1

$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.

Answer 2

Rather than

$h_DB->do("START TRANSACTION");
...
$h_DB->do("COMMIT");

you should probably be calling the DBI methods

$h_DB->begin_work;
...
$h_DB->commit;

The only difference is that you are second-guessing the database driver's behaviour in your own code while making it less portable. The DBI calls rely on the driver knowing how to start and stop transactions for this database, and is the proper way

The essence of database transactions is that everything inside the transaction will go ahead unmolested by any parallel database connections. Some databases will simply lock the entire database for the duration of your transaction, but most will be more optimised and allow reads from anywhere and write to unrelated tables while your transaction is ongoing

The implication of a SELECT with the FOR UPDATE qualifier is that you are asking for a transaction to be started when the read begins, and there is no need to explicitly open a transaction yourself. Once you have obtained the values they will not be changed, and nor will anything else in the database that affects them. Connections that need to access the data that you have locked will be suspended unless they have asked for dirty read functionality

Does that cover everything?