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.

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

1 files changed, 38 insertions, 0 deletions

diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test
index c29b3458d19..b9ba03cf2de 100644
--- a/mysql-test/t/partition_innodb.test
+++ b/mysql-test/t/partition_innodb.test
@@ -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