Tidb: Want to know whether it is the expected result

[chalsliu]

Bug Report

1. What did you do?

+-------------------------------------------------------+-------------------------------------------------------+
| session 1                                             | session 2                                             |
+-------------------------------------------------------+-------------------------------------------------------+
| mysql> select id, k from sbtest1 where id = 364435;   |                                                       |
|                                                       |                                                       |
| +--------+-----+                                      |                                                       |
|                                                       |                                                       |
| | id | k |                                            |                                                       |
|                                                       |                                                       |
| +--------+-----+                                      |                                                       |
|                                                       |                                                       |
| | 364435 | 100 |                                      |                                                       |
|                                                       |                                                       |
| +--------+-----+                                      |                                                       |
|                                                       |                                                       |
| 1 row in set (0.00 sec)                               |                                                       |
+-------------------------------------------------------+-------------------------------------------------------+
| mysql> update sbtest1 set k = 1000 where id = 364435; | mysql> select id, k from sbtest1 where id = 364435;   |
|                                                       |                                                       |
| Query OK, 1 row affected (0.01 sec)                   | +--------+-----+                                      |
|                                                       |                                                       |
|                                                       | | id | k |                                            |
|                                                       |                                                       |
|                                                       | +--------+-----+                                      |
|                                                       |                                                       |
|                                                       | | 364435 | 100 |                                      |
|                                                       |                                                       |
|                                                       | +--------+-----+                                      |
|                                                       |                                                       |
|                                                       | 1 row in set (0.00 sec)                               |
+-------------------------------------------------------+-------------------------------------------------------+
|                                                       | mysql> update sbtest1 set k = 3000 where id = 364435; |
|                                                       |                                                       |
|                                                       | Query OK, 1 row affected (0.00 sec)                   |
+-------------------------------------------------------+-------------------------------------------------------+
|                                                       | mysql> commit;                                        |
|                                                       |                                                       |
|                                                       | Query OK, 0 rows affected (0.01 sec)                  |
+-------------------------------------------------------+-------------------------------------------------------+
|                                                       | mysql> select id, k from sbtest1 where id = 364435;   |
|                                                       |                                                       |
|                                                       | +--------+------+                                     |
|                                                       |                                                       |
|                                                       | | id | k |                                            |
|                                                       |                                                       |
|                                                       | +--------+------+                                     |
|                                                       |                                                       |
|                                                       | | 364435 | 3000 |                                     |
|                                                       |                                                       |
|                                                       | +--------+------+                                     |
|                                                       |                                                       |
|                                                       | 1 row in set (0.01 sec)                               |
+-------------------------------------------------------+-------------------------------------------------------+
| mysql> commit;                                        |                                                       |
|                                                       |                                                       |
| Query OK, 0 rows affected (0.01 sec)                  |                                                       |
+-------------------------------------------------------+-------------------------------------------------------+
| mysql> select id, k from sbtest1 where id = 364435;   |                                                       |
|                                                       |                                                       |
| +--------+------+                                     |                                                       |
|                                                       |                                                       |
| | id | k |                                            |                                                       |
|                                                       |                                                       |
| +--------+------+                                     |                                                       |
|                                                       |                                                       |
| | 364435 | 1000 |                                     |                                                       |
|                                                       |                                                       |
| +--------+------+                                     |                                                       |
|                                                       |                                                       |
| 1 row in set (0.00 sec                                |                                                       |
+-------------------------------------------------------+-------------------------------------------------------+

1. What did you expect to see?
   Session 1‘s commit should fail.

2. What did you see instead?
   It committed successfully.

3. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   Release Version: v2.1.0-rc.1-91-g27047a0
   Git Commit Hash: 27047a0e2f6f28e9cd7daa0562eee865bf6651df
   Git Branch: master
   UTC Build Time: 2018-09-11 01:33:50
   GoVersion: go version go1.11 linux/amd64
   Race Enabled: false
   TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
   Check Table Before Drop: false

[lysu]

@chalsliu Hi, thanks for feedback, it's "expect" with default retry behavior，and maybe what you want is like this:

+-------------------------------------------------------+-------------------------------------------------------+
| session 1                                             | session 2                                             |
+-------------------------------------------------------+-------------------------------------------------------+
| set @@tidb_retry_limit=0;                             | set @@tidb_retry_limit=0;                                       |
+-------------------------------------------------------+-------------------------------------------------------+
| begin                                                 | begin                                         |
+-------------------------------------------------------+-------------------------------------------------------+
| mysql> select id, k from sbtest1 where id = 364435;   |                                                       |
|                                                       |                                                       |
| +--------+-----+                                      |                                                       |
|                                                       |                                                       |
| | id | k |                                            |                                                       |
|                                                       |                                                       |
| +--------+-----+                                      |                                                       |
|                                                       |                                                       |
| | 364435 | 100 |                                      |                                                       |
|                                                       |                                                       |
| +--------+-----+                                      |                                                       |
|                                                       |                                                       |
| 1 row in set (0.00 sec)                               |                                                       |
+-------------------------------------------------------+-------------------------------------------------------+
| mysql> update sbtest1 set k = 1000 where id = 364435; | mysql> select id, k from sbtest1 where id = 364435;   |
|                                                       |                                                       |
| Query OK, 1 row affected (0.01 sec)                   | +--------+-----+                                      |
|                                                       |                                                       |
|                                                       | | id | k |                                            |
|                                                       |                                                       |
|                                                       | +--------+-----+                                      |
|                                                       |                                                       |
|                                                       | | 364435 | 100 |                                      |
|                                                       |                                                       |
|                                                       | +--------+-----+                                      |
|                                                       |                                                       |
|                                                       | 1 row in set (0.00 sec)                               |
+-------------------------------------------------------+-------------------------------------------------------+
|                                                       | mysql> update sbtest1 set k = 3000 where id = 364435; |
|                                                       |                                                       |
|                                                       | Query OK, 1 row affected (0.00 sec)                   |
+-------------------------------------------------------+-------------------------------------------------------+
|                                                       | mysql> commit;                                        |
|                                                       |                                                       |
|                                                       | Query OK, 0 rows affected (0.01 sec)                  |
+-------------------------------------------------------+-------------------------------------------------------+
|                                                       | mysql> select id, k from sbtest1 where id = 364435;   |
|                                                       |                                                       |
|                                                       | +--------+------+                                     |
|                                                       |                                                       |
|                                                       | | id | k |                                            |
|                                                       |                                                       |
|                                                       | +--------+------+                                     |
|                                                       |                                                       |
|                                                       | | 364435 | 3000 |                                     |
|                                                       |                                                       |
|                                                       | +--------+------+                                     |
|                                                       |                                                       |
|                                                       | 1 row in set (0.01 sec)                               |
+-------------------------------------------------------+-------------------------------------------------------+
| mysql> commit;                                        |                                                       |
|                                                       |                                                       |
| ERROR 1105 (HY000): [try again later]: tikv restarts txn: retryable: write conflict
                  |                                                       |
+-------------------------------------------------------+-------------------------------------------------------+

the reason that let you got 1000 is default automatic retry https://pingcap.com/docs/sql/transaction-isolation/#transaction-retry, automatic retry just helps the user easy works with legacy system's SQLs and doesn't check retry logic in everywhere. but for new system maybe better use self-retry just like you want.(maybe can encapsulate the retry handle logic into a util in client-system code)

[chalsliu]

I understand.
Thank you very much.