MySQL Bugs: #40595: Non-matching rows not released with READ-COMMITTED on tables with partitions

Bug #40595	Non-matching rows not released with READ-COMMITTED on tables with partitions
Submitted:	9 Nov 2008 0:25	Modified:	10 Dec 2008 21:01
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.29	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any
Tags:	innodb_locks_unsafe_for_binlog, read committed

Description:
5.1 Manual says (e.g. http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-isolation.html):

"In MySQL 5.1, if the READ COMMITTED isolation level is used or the innodb_locks_unsafe_for_binlog system variable is enabled, there is no InnoDB gap locking except in constraint checking. Also, record locks for non-matching rows are released after MySQL has evaluated the WHERE condition."

However, if an InnoDB table has partitions, the latter part is not true for READ-COMMITTED isolation level (innodb_locks_unsafe_for_binlog is OFF) -- scanned rows stay locked until the transaction is finished. 

Checked with partition by range and partition by key.

For tables without partitions extra locks are released.

How to repeat:
drop table if exists tp;

create table tp ( id int, 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 tp values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8), (9,9), (10,10), (11,11);

set session transaction isolation level read committed;

set autocommit = 0;

# if id column (partitioning function) is used in where clause, all rows of the corresponding partition stay locked:  

update tp set data = data*2 where id = 2;

show engine innodb status \G
# ....
# ---TRANSACTION 0 1796, ACTIVE 8 sec, process no 3174, OS thread id 1141414208
# 2 lock struct(s), heap size 368, 4 row lock(s), undo log entries 1
# ....

# if data column is used in where clause, all rows of the table stay locked:

update tp set data = data*2 where data = 2;

show engine innodb status \G
# ....
# ---TRANSACTION 0 1798, ACTIVE 3 sec, process no 3174, OS thread id 1141414208
# 6 lock struct(s), heap size 1216, 11 row lock(s), undo log entries 1
# ....

Suggested fix:
Release extra locks or, if it impossible with partitioning, declare it as partitioning limitation in documentation.

ha_partition DO propagate the call to unlock_row() from the 'root' handler to the innodb handler. I will try to find out why the locks are not releases.

The problem was that the handler virtual function try_semi_consistent_read was not implemented in ha_partition, adding that fixes this bug. Will commit a patch soon.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/58371

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.

Mikael approved it.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/58856

2708 Mattias Jonsson	2008-11-14 [merge]
      Bug#40595: Non-matching rows not released with READ-COMMITTED on tables
      with partitions
      
      Pre push fix, optimized replace_regex, to cut 2 seconds
      from test time.

Patch queued in mysql-6.0-bugteam and mysql-5.1-bugteam

I will add was_semi_consisten_read() too.

Pushed into 5.1.31  (revid:mattias.jonsson@sun.com-20081114225117-rfbmi7134x4arcq5) (version source revid:mattias.jonsson@sun.com-20081114232648-afp3f61r8trswd8w) (pib:5)

Pushed into 6.0.9-alpha  (revid:mattias.jonsson@sun.com-20081114225117-rfbmi7134x4arcq5) (version source revid:ingo.struewing@sun.com-20081121151447-dtf2ofz2ys0zqed1) (pib:5)

Noted in 5.1.31, 6.0.9 changelogs.

With READ COMMITTED transaction isolation level, InnoDB uses a
semi-consistent read that releases non-matching rows after MySQL has
evaluated the WHERE clause. However, this was not happening if the
table used partitions.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/61751

2733 Mattias Jonsson	2008-12-16
      post push fix for bug#40595
      Addition of hander function was_semi_consistent_read

Pushed the addition of was_semi_consistent_read to mysql-5.1-bugteam and mysql-6.0-bugteam

Pushed into 5.1.31 (revid:joro@sun.com-20090115053147-tx1oapthnzgvs1ro) (version source revid:azundris@mysql.com-20081230114838-cn52tu180wcrvh0h) (merge vers: 5.1.31) (pib:6)

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090115073240-1wanl85vlvw2she1) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)

Pushed into 6.0.10-alpha (revid:joro@sun.com-20090119171328-2hemf2ndc1dxl0et) (version source revid:azundris@mysql.com-20081230114916-c290n83z25wkt6e4) (merge vers: 6.0.9-alpha) (pib:6)