UPDATE fails to match all rows in a transactional scenario

Bug #308557 reported by Philip Stoev on 2008-12-16
2
Affects Status Importance Assigned to Milestone
PBXT
Undecided
Vladimir Kolesnikov

Bug Description

When executing a test for transactional consistency, an UPDATE statement fails to match all matching rows in the table.

The test is based on the textbook example for testing transactions with a bank and bank accounts - a table is populated with known records, then certain queries which move integers (dollars) around are executed and the total sum of all integers in the table is constantly checked for deviations from the desired value.

How to repeat:

bzr branch lp:~randgen/randgen/main

$ perl runall.pl \
   --basedir=/build/mysql-5.1.30 \
   --engine=PBXT \
   --gendata=conf/transactions.zz \
   --grammar=conf/transactions.yy \
   --mysqld=--plugin-dir=/build/bzr/pbxt/src/.libs/ \
   --mysqld=--plugin-load=PBXT=libpbxt.so \
   --validator=DatabaseConsistency \
   --queries=100000 \
   --reporter=Deadlock,Backtrace \
   --threads=10

This will populate a table and proceed to run zero-sum queries against the table. PBXT will complain that savepoints are not supported, however this does not impact the rest of the test. The test will soon fail with :

# 15:11:12 Bad checksum table: table10_pbxt_int_autoinc; sum: 2020; affected_rows:1; query: UPDATE `table10_pbxt_int_autoinc` SET `int_key` = `int_key` + IF(`pk` % 2 = 1 , 20, -20) WHERE `pk` >= 5 ORDER BY `pk` ASC LIMIT 2

This statement updated 1 row instead of 2, even though there are two rows that match the WHERE predicate and ORDER BY is used to ensure deadlock-free operation.

Since Falcon exhibits a similar bug, I would be very interested to know if this is a known and legitimate behavior for MVCC engines.

Related branches

Changed in pbxt:
status: New → Confirmed
Changed in pbxt:
status: Confirmed → In Progress
Changed in pbxt:
assignee: nobody → vkolesnikov
Changed in pbxt:
status: In Progress → Fix Committed
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers