Get a resultset from a mysql transaction

Question

Hello

I was wondering if it's possible to get the resultset of the select query from my transaction. Mysql returns that the table was updated, but returned 0 rows.

This is the transaction:

START TRANSACTION;
SELECT *, @A:=id FROM mailer_log LIMIT 0,10;
UPDATE mailer_log SET picked=1 WHERE id=@A;
COMMIT;

Answer 1

If you do these step by step you have the resultset after step 2.

If you'd like the resultset of the modified records, you'll have to do the query again.

This is not as bad as it sounds, since all data will be in memory anyhow (unless you have millions of records with the same id of course) since you just retrieved and updated them.

Example in perl :

$dbh->begin_work;
$ary_ref  = $dbh->selectall_arrayref("SELECT * FROM mailer_log WHERE id = '$a' LIMIT 0,10");
# ... do something interesting with the result set
$dbh->do("UPDATE mailer_log SET picked=1 WHERE id='$a'");
$dbh->commit;