MySQL Bugs: #69946: Shared read lock on SELECT in transaction on VARCHAR column with UK constraint

Bug #69946	Shared read lock on SELECT in transaction on VARCHAR column with UK constraint
Submitted:	7 Aug 2013 7:13	Modified:	11 Feb 2017 2:38
Reporter:	Daniel Coteanu	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Cluster: Documentation	Severity:	S2 (Serious)
Version:	5.6.11-ndb-7.3.2-cluster	OS:	Linux (RHEL 6.2)
Assigned to:		CPU Architecture:	Any
Tags:	shared read lock

Description:
We've observed the following strange behaviour of MySQL cluster engine. 

Problem statement 
----------------- 
Behaviour may be reproduced as follows: 
1) Open one MySQL session (S1) 
2) In S1: set autocommit=0 
3) In S1: issue a simple SELECT statement (without "FOR UPDATE" clause, without COMMIT or ROLLBACK) 
4) Open another MySQL session (S2) 
5) In S2: set autocommit=0 
6) In S2: issue the same SELECT statement with FOR UPDATE (from step 3) 

Expected result of step 6: the SELECT returns successfully. 
Actual result of step 6: the SELECT blocks until the timeout expires, then returns with timeout error. 

Investigation results 
--------------------- 
In MySQL cluster engine implementation, all SELECT statements are handled as if they include a "FOR UPDATE" clause. 
This means that all SELECT statements will lock the selected records; the locks will be released at next COMMIT / ROLLBACK statement; if no such statement follows, records remain blocked. 
As consequence of this fact: 
1) If autocommit is set to 1, there is no impact, because in this mode, all SQL statements are automatically followed by a COMMIT; therefore the lock is released. This behaviour is identical with that of any DB engine. 
2) If autocommit is set to 0, the impact is that the selected records are locked. This behaviour is not a standard one. 
3) Problem occurs only when the SELECT is transactional (autocommit=0) and search condition use UNIQUE KEY index. For searches using PRIMARY KEY the lock is NOT held by first select. 

How to repeat:
Session1
 
mysql> show create table test_uk;
+---------+--------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                         |
+---------+--------------------------------------------------------------------------------------------------------------------------------------+
| test_uk | CREATE TABLE `test_uk` (
  `name` varchar(255) DEFAULT NULL,
  UNIQUE KEY `name` (`name`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from test_uk where name='test1';
+-------+
| name  |
+-------+
| test1 |
+-------+
1 row in set (0.00 sec)
 
Session2
 
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from test_uk where name='test1' for update;
## session is waiting
 
 
From documentation: 
Prior to MySQL Cluster NDB 7.0.12, for any SELECT which used a primary key lookup or unique key lookup to retrieve any columns that used any of the BLOB or TEXT data types and that was executed within a transaction, a shared read lock was held on the table for the duration of the transaction—that is, until the transaction was either committed or aborted.
In MySQL Cluster NDB 7.0.12 and later, for primary key lookups, the lock is released as soon as all BLOB or TEXTdata has been read. (Bug #49190) However, for unique key lookups, the shared lock continues to be held for the lifetime of the transaction.
 
But in our case the column is not TEXT or BLOB, but VARCHAR.

Suggested fix:
Use SELECT with autocommit set to 1 in session 1.

Hello Daniel,

Thank you for the bug report and the test case. 
Verified as described on recent MySQL-5.6.11 ndb-7.3.2

Thanks,
Umesh

Change the severity level.

Hi,

Regarding your comment "But in our case the column is not TEXT or BLOB, but VARCHAR."

Please note that this has nothing to do with the fact that the column is VARCHAR. This is related only to the fact that the data in the first select are accessed using a UNIQUE KEY, instead of a PRIMARY KEY. As you wrote in the bug description, the lock does not occur if you use a PK instead of a UK

Note that even if you have a PK, like in this table

CREATE TABLE `test_uk2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

if you use the UK to filter the data you will incur in the lock as well, but you will not get it if you will use the PK.

This is a know limitation that needs to be documented better in our documentation

Possible workarounds:

1. Can I ask you if you are using the unique constraint just as a constraint (in order to avoid duplicates), or if you really need to use it to lookup rows efficiently? if you are using it just to avoid duplicates, then you can workaround the problem by using the PK to lookup the row (use of FORCE INDEX can help)

2. If you need to use the UK to efficiently lookup rows, then another option is to do this:

i) Transaction 1, lookup unique index to get row primary key value, commit.
ii) Transaction 2, Use primary key value to read/write row

The first transaction is short lived, so will not block updates for long.
The second transaction does not use the unique key for access, so does not block updates.

The downside here is that the row with primary key value retrieved in transaction 1 may have been modified by the time Transaction 2 reads it - this is what the lock upgrade avoids.

This is not a bug, but is rather intentional. It thus becomes a documentation issue, and as such is a duplicate of Docs BUG#80471.

Closed as duplicate. See previous comment.

Sorry for the long hiatus. There was some confusion due to the fact that this remained a code bug when it should have been moved to the Docs queue--I'll try to get this issue resolved in the documentation within the next few days.

Thanks!

Note: This is actually not a bug, but rather a known issue that was documented quite a long time ago. See BUG#80471.