MySQL Bugs: #80471: Via a secondary unique index is automatically upgraded to a Shared read lock

Bug #80471	Via a secondary unique index is automatically upgraded to a Shared read lock
Submitted:	23 Feb 2016 9:05	Modified:	17 Feb 2017 12:20
Reporter:	Shinya Sugiyama	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	7.x	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	documantation

Description:
Could you update manual for this non-standard behavior?
We need to make sure this is bug or just non-standard behavior.

https://dev.mysql.com/doc/refman/5.6/ja/mysql-cluster-limitations-transactions.html
https://dev.mysql.com/doc/refman/5.6/en/mysql-cluster-limitations-transactions.html
https://dev.mysql.com/doc/refman/5.7/en/mysql-cluster-limitations-transactions.html

This behavior is reported three years ago; however, document is not updated yet.
https://bugs.mysql.com/bug.php?id=69946

【Example of non-standard behavior】

------------------------------------------------
# table create with a unique key (Transaction B is blocked by A)
------------------------------------------------

CREATE TABLE `test_tbl` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_c01` (`tag_name`) USING BTREE
) ENGINE=ndbcluster AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;

# test data

begin ;
INSERT INTO test_tbl(tag_name) VALUES('tag');
commit ;

# Transaction A
begin ;
select * from test_tbl where tag_name = 'tag';

# Transaction B
begin ;
select * from test_tbl where id = 1 for update; <---- This transaction is blocked. (timed out)

------------------------------------------------
# table create without a unique key (Transaction B is not blocked by A)
------------------------------------------------

CREATE TABLE `test_tbl2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(16) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=ndbcluster AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

begin ;
select * from test_tbl2 where tag_name = 'tag';

begin ;
select * from test_tbl2 where id = 1 for update;

Please update manual.

Best Regard
Shinya

How to repeat:
It is already verified in 

https://bugs.mysql.com/bug.php?id=69946

Suggested fix:
Please update manual.

Hello Sugiyama,

Thank you for the report.

Thanks,
Umesh

I got permission from user.
You can open this submission to public.

Best Regard
Shinya

No evidence that this isn't a bug in the software, changing category to Cluster for review by dev.

Hi Jon,

Thank you for your comment and confirmation.
If it is not a bug, it is much better.

I will wait for response from Dev Team for explaining official status to Cluster users.

Best Regard
Shiny

Hi Jon,

Is there any update about this ticket?

I confirmed with MySQL Cluster 7.5.4.
I got some detail information from ndbinfo, so please check it.

mysql> select @@version;
+----------------------------------------------+
| @@version                                    |
+----------------------------------------------+
| 5.7.16-ndb-7.5.4-cluster-commercial-advanced |
+----------------------------------------------+
1 row in set (0.00 sec)

■ Session (A) transid:13567973486755886

mysql> select * from ndbinfo.cluster_locks;
Empty set (0.05 sec)

mysql> begin; select * from test_tbl where tag_name = 'tag';
Query OK, 0 rows affected (0.00 sec)

+----+----------+
| id | tag_name |
+----+----------+
|  1 | tag      |
+----+----------+
1 row in set (0.00 sec)

mysql> select * from ndbinfo.cluster_locks;
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
| node_id | block_instance | tableid | fragmentid | rowid | transid           | mode | state | detail | op   | duration_millis | lock_num | waiting_for |
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
|       2 |              1 |     107 |          1 |     0 | 13567973486755886 | S    | H     | *      | READ |            2780 |        1 |        NULL |
|       2 |              1 |     113 |          1 |     0 | 13567973486755886 | S    | H     | *      | READ |            2780 |        3 |        NULL |
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
2 rows in set (0.07 sec)

mysql> select * from ndbinfo.cluster_locks;
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
| node_id | block_instance | tableid | fragmentid | rowid | transid           | mode | state | detail | op   | duration_millis | lock_num | waiting_for |
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
|       2 |              1 |     107 |          1 |     0 | 13569072998383655 | X    | W     |        | READ |            2593 |        0 |           1 |
|       2 |              1 |     107 |          1 |     0 | 13567973486755886 | S    | H     | *      | READ |            7641 |        1 |        NULL |
|       2 |              1 |     113 |          1 |     0 | 13567973486755886 | S    | H     | *      | READ |            7641 |        3 |        NULL |
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
3 rows in set (0.04 sec)

mysql> 

■ Session (B)  -  transid: 13569072998383655
mysql> begin ; select * from test_tbl where id = 1 for update;
Query OK, 0 rows affected (0.00 sec)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

■ Session (A): After Session (B) Became Time Out.
mysql> select * from ndbinfo.cluster_locks;
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
| node_id | block_instance | tableid | fragmentid | rowid | transid           | mode | state | detail | op   | duration_millis | lock_num | waiting_for |
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
|       2 |              1 |     107 |          1 |     0 | 13567973486755886 | S    | H     | *      | READ |           92815 |        1 |        NULL |
|       2 |              1 |     113 |          1 |     0 | 13567973486755886 | S    | H     | *      | READ |           92815 |        3 |        NULL |
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
2 rows in set (0.06 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ndbinfo.cluster_locks;
Empty set (0.06 sec)

mysql>

Just in case,

Table Information

mysql> select * from ndbinfo.table_info where table_id IN(107,113);
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
| table_id | logged_table | row_contains_gci | row_contains_checksum | read_backup | fully_replicated | storage_type | hashmap_id | partition_balance | create_gci |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
|      113 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
|      107 |            1 |                1 |                     1 |           0 |                0 | MEMORY       |          1 | FOR_RP_BY_LDM     |          0 |
+----------+--------------+------------------+-----------------------+-------------+------------------+--------------+------------+-------------------+------------+
2 rows in set (0.00 sec)

mysql> select fq_name,table_id,node_id,fragment_num,var_elem_alloc_bytes,hash_index_alloc_bytes from ndbinfo.memory_per_fragment where table_id IN(107,113);
+----------------------------+----------+---------+--------------+----------------------+------------------------+
| fq_name                    | table_id | node_id | fragment_num | var_elem_alloc_bytes | hash_index_alloc_bytes |
+----------------------------+----------+---------+--------------+----------------------+------------------------+
| sys/def/107/idx_c01$unique |      113 |       1 |            0 |                    0 |                   8192 |
| sys/def/107/idx_c01$unique |      113 |       1 |            1 |                32768 |                  16384 |
| sys/def/107/idx_c01$unique |      113 |       2 |            0 |                    0 |                   8192 |
| sys/def/107/idx_c01$unique |      113 |       2 |            1 |                32768 |                  16384 |
| TEST_DB/def/test_tbl       |      107 |       1 |            0 |                    0 |                   8192 |
| TEST_DB/def/test_tbl       |      107 |       1 |            1 |                32768 |                  16384 |
| TEST_DB/def/test_tbl       |      107 |       2 |            0 |                    0 |                   8192 |
| TEST_DB/def/test_tbl       |      107 |       2 |            1 |                32768 |                  16384 |
+----------------------------+----------+---------+--------------+----------------------+------------------------+
8 rows in set (0.03 sec)

mysql>

The problem happens even without varchar columns like below:

mysql> create table t1 (a serial, b int, unique(b)) engine ndb;                                                                                                                              Query OK, 0 rows affected (0.51 sec)

mysql> insert into t1 (b) values(1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where b=1;                                                                                                                                                           +---+------+
| a | b    |
+---+------+
| 1 |    1 |
+---+------+
1 row in set (0.00 sec)

mysql> select * from ndbinfo.cluster_locks;
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
| node_id | block_instance | tableid | fragmentid | rowid | transid           | mode | state | detail | op   | duration_millis | lock_num | waiting_for |
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
|       2 |              0 |      17 |          1 |     0 | 13566873975128137 | S    | H     | *      | READ |           17169 |        7 |        NULL |
|       2 |              0 |      43 |          1 |     0 | 13566873975128137 | S    | H     | *      | READ |           17169 |        8 |        NULL |
+---------+----------------+---------+------------+-------+-------------------+------+-------+--------+------+-----------------+----------+-------------+
2 rows in set (0.03 sec)

BUG#69946 is a duplicate of this bug.

I'm aware that BUG#69946 was filed before this one, but this was identified as a Docs issue first, so in this case I've treated the other one as the duplicate. Sorry for any confusion.

After careful review of the documentation, I have found that it states that (emphasis added):

    NDB uses READ COMMITTED for all reads unless a modifier such
    as LOCK IN SHARE MODE or FOR UPDATE is used. LOCK IN SHARE
    MODE causes shared row locks to be used; FOR UPDATE causes
    exclusive row locks to be used. *Unique key reads have their
    locks upgraded automatically by NDB to ensure a
    self-consistent read...*

To the best of my recollection, the preceding has been in the Manual (and on the same page cited by the submitter) for some years--since 2007 or thereabouts. So this is actually a long-known and long-documented issue.

Closing as !BUG.