MySql: transactions don't detect deadlocks?

Question

Consider the following perl code:

$schema->txn_begin();

my $r = $schema->resultset('test1')->find({id=>20});

my $n = $r->num;
$r->num($n+1);
print("updating for $$\n");
$r->update();

print("$$ val: ".$r->num."\n");

sleep(4);

$schema->txn_commit();

I'm expecting that since the update is protected by a transaction, then if two processes try to update the "num" field, the second should fail with some error because it lost the race. Interbase calls this a "deadlock" error. MySQL, however will pause on the update() call, but will happily continue on after the first one has called the commit. The second process then has the "old" value of num, causing the increment to be incorrect. Observe:

$ perl trans.pl  & sleep 1 ; perl trans.pl 
[1] 5569
updating for 5569
5569 val: 1015
updating for 5571
5571 val: 1015
[1]+  Done                    perl trans.pl

the result value is "1015" in both cases. How can this be correct?

Answer 1

Try storing the $r->num in a mysql variable instead of perl. Sorry I don't know Perl, but basically what you want is

START TRANSACTION;
SELECT num INTO @a FROM test1 where id = 20;
UPDATE test1 set num=(@a+1) WJERE id=20;
COMMIT;

Answer 2

This is not a deadlock, a deadlock is something like this:

Tx1

1- updates R1 => write lock on R1 2- updates R2 => write lock on R2

Tx 2

1- updates R2 2- updates R1

If tx1 and tx2 execute simultaneously, it may happen that tx1 waits for the lock on R2 to be free, and tx2 waits for the lock on R1.

In your case, you need to lock the row with id=20 ( using select for update ). The tx arriving "late" will wait a certain amount of time (defined by the db engine) to follow.

Answer 3

Assuming you're using InnoDB as your storage engine, this is the behavior I would expect. The default transaction isolation level for InnoDB is REPEATABLE READ. This means that when you perform your SELECT, the transaction gets a snapshot of the database at that particular time. The snapshot will not include updated data from other transactions that have not yet committed. Since the SELECT in each process happens before either commits, they'll each see the database in the same state (with num = 1014).

To get the behavior you seem to be expecting, you should follow the suggestion of Lluis and perform a SELECT ... FOR UPDATE to lock the row you're interested in. To do that, change this line

my $r = $schema->resultset('test1')->find({id=>20});

to this

my $r = $schema->resultset('test1')->find({id=>20}, {for=>'update'});

and rerun your test.

If you're not familiar with the intricacies of transactions in MySQL, I highly suggest you read the section in the docs about the InnoDB Transaction Model and Locking. Also, if you haven't already, read the DBIC Usage Notes regarding transactions and AutoCommit very carefully as well. The way the txn_ methods behave when AutoCommit is on or off is a bit tricky. If you're up for it, I would also suggest reading the source. Personally, I had to read the source to fully understand what DBIC was doing so that I could get the exact behavior I wanted.