bzr commit into mysql-5.1 branch (mattias.jonsson:2705) Bug#40595

[Mattias Jonsson]

#At file:///Users/mattiasj/clones/bzrroot/b40595-51-bugteam/

 2705 Mattias Jonsson	2008-11-10
      Bug#40595: Non-matching rows not released with READ-COMMITTED
      on tables with partitions
      
      Problem was that the handler function try_semi_consistent_read
      was not propagated to the innodb handler.
      
      Solution was to implement that function in the partitioning
      handler.
modified:
  mysql-test/r/partition_innodb.result
  mysql-test/t/partition_innodb.test
  sql/ha_partition.cc
  sql/ha_partition.h

per-file messages:
  mysql-test/r/partition_innodb.result
    Bug#40595: Non-matching rows not released with READ-COMMITTED
    on tables with partitions
    
    Updated test result.
  mysql-test/t/partition_innodb.test
    Bug#40595: Non-matching rows not released with READ-COMMITTED
    on tables with partitions
    
    Added test case for bug#40595.
    
    Note: the replace_regex is taking long time. I have not found
    any better regex (it takes time using 'sed' too).
  sql/ha_partition.cc
    Bug#40595: Non-matching rows not released with READ-COMMITTED
    on tables with partitions
    
    Added function to the partitioning handler for handling
    semi consistent reads (unlock_row was already implemented,
    and this is needed for unlock_row to work properly in innodb).
    It uses pruning for optimizing the call.
  sql/ha_partition.h
    Bug#40595: Non-matching rows not released with READ-COMMITTED
    on tables with partitions
    
    Added function to the partitioning handler for handling
    semi consistent reads (unlock_row was already implemented,
    and this is needed for unlock_row to work properly in innodb).
=== modified file 'mysql-test/r/partition_innodb.result'
--- a/mysql-test/r/partition_innodb.result	2008-11-04 07:43:21 +0000
+++ b/mysql-test/r/partition_innodb.result	2008-11-10 20:13:24 +0000
@@ -1,3 +1,25 @@
+drop table if exists t1;
+CREATE TABLE t1 (id INT PRIMARY KEY, data INT) ENGINE = InnoDB 
+PARTITION BY RANGE(id) ( 
+PARTITION p0 VALUES LESS THAN (5), 
+PARTITION p1 VALUES LESS THAN (10), 
+PARTITION p2 VALUES LESS THAN MAXVALUE 
+);
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8),
+(9,9), (10,10), (11,11);
+SET @old_tx_isolation := @@session.tx_isolation;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+SET autocommit = 0;
+UPDATE t1 SET DATA = data*2 WHERE id = 3;
+SHOW ENGINE InnoDB STATUS;
+Type	Name	Status
+InnoDB		2 lock struct(s) 1 row lock(s)
+UPDATE t1 SET data = data*2 WHERE data = 2;
+SHOW ENGINE InnoDB STATUS;
+Type	Name	Status
+InnoDB		6 lock struct(s) 2 row lock(s)
+SET @@session.tx_isolation = @old_tx_isolation;
+DROP TABLE t1;
 # Bug#37721, test of ORDER BY on PK and WHERE on INDEX
 CREATE TABLE t1 (
 a INT,

=== modified file 'mysql-test/t/partition_innodb.test'
--- a/mysql-test/t/partition_innodb.test	2008-10-10 10:01:01 +0000
+++ b/mysql-test/t/partition_innodb.test	2008-11-10 20:13:24 +0000
@@ -1,6 +1,44 @@
 --source include/have_partition.inc
 --source include/have_innodb.inc
 
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+#
+# Bug#40595: Non-matching rows not released with READ-COMMITTED on tables
+#            with partitions
+CREATE TABLE t1 (id INT PRIMARY KEY, data INT) ENGINE = InnoDB 
+PARTITION BY RANGE(id) ( 
+ PARTITION p0 VALUES LESS THAN (5), 
+ PARTITION p1 VALUES LESS THAN (10), 
+ PARTITION p2 VALUES LESS THAN MAXVALUE 
+);
+
+INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8),
+                      (9,9), (10,10), (11,11);
+
+SET @old_tx_isolation := @@session.tx_isolation;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+
+SET autocommit = 0;
+
+UPDATE t1 SET DATA = data*2 WHERE id = 3;
+
+# NOTE: This regex takes quite a long time (> 1 sec).
+--replace_regex /.*[^[:alnum:]]+([0-9]+ lock struct.s.), heap size [0-9]+, ([0-9]+ row
lock.s.), undo log entries .*/\1 \2/
+SHOW ENGINE InnoDB STATUS;
+
+UPDATE t1 SET data = data*2 WHERE data = 2;
+
+# NOTE: This regex takes quite a long time (> 1 sec).
+--replace_regex /.*[^[:alnum:]]+([0-9]+ lock struct.s.), heap size [0-9]+, ([0-9]+ row
lock.s.), undo log entries .*/\1 \2/
+SHOW ENGINE InnoDB STATUS;
+
+SET @@session.tx_isolation = @old_tx_isolation;
+
+DROP TABLE t1;
+
 #
 # Bug37721: ORDER BY when WHERE contains non-partitioned index column
 # wrong order since it did not use pk as second compare

=== modified file 'sql/ha_partition.cc'
--- a/sql/ha_partition.cc	2008-11-06 14:24:59 +0000
+++ b/sql/ha_partition.cc	2008-11-10 20:13:24 +0000
@@ -2813,8 +2813,42 @@ uint ha_partition::lock_count() const
 
 void ha_partition::unlock_row()
 {
+  DBUG_ENTER("ha_partition::unlock_row");
   m_file[m_last_part]->unlock_row();
-  return;
+  DBUG_VOID_RETURN;
+}
+
+
+/**
+  Use semi consistent read if possible
+
+  SYNOPSIS
+    try_semi_consistent_read()
+    yes   Turn on semi consistent read
+
+  RETURN VALUE
+    NONE
+
+  DESCRIPTION
+    See handler.h:
+    Tell the engine whether it should avoid unnecessary lock waits.
+    If yes, in an UPDATE or DELETE, if the row under the cursor was locked
+    by another transaction, the engine may try an optimistic read of
+    the last committed row value under the cursor.
+    Note: prune_partitions are already called before this call, so using
+    pruning is OK.
+*/
+void ha_partition::try_semi_consistent_read(bool yes)
+{
+  handler **file;
+  DBUG_ENTER("ha_partition::try_semi_consistent_read");
+  
+  for (file= m_file; *file; file++)
+  {
+    if (bitmap_is_set(&(m_part_info->used_partitions), (file - m_file)))
+      (*file)->try_semi_consistent_read(yes);
+  }
+  DBUG_VOID_RETURN;
 }
 
 

=== modified file 'sql/ha_partition.h'
--- a/sql/ha_partition.h	2008-11-05 20:13:54 +0000
+++ b/sql/ha_partition.h	2008-11-10 20:13:24 +0000
@@ -325,6 +325,10 @@ public:
     Call to unlock rows not to be updated in transaction
   */
   virtual void unlock_row();
+  /*
+    Call to hint about semi consistent read
+  */
+  virtual void try_semi_consistent_read(bool);
 
   /*
     -------------------------------------------------------------------------