Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

select for update statement can't got stmt-count-limit error #48411

Closed
crazycs520 opened this issue Nov 8, 2023 · 0 comments · Fixed by #48412 or #48447
Closed

select for update statement can't got stmt-count-limit error #48411

crazycs520 opened this issue Nov 8, 2023 · 0 comments · Fixed by #48412 or #48447
Assignees
Labels
affects-4.0 This bug affects 4.0.x versions. affects-5.0 This bug affects 5.0.x versions. affects-5.1 This bug affects 5.1.x versions. affects-5.2 This bug affects 5.2.x versions. affects-5.3 This bug affects 5.3.x versions. affects-5.4 This bug affects 5.4.x versions. affects-6.1 affects-6.5 affects-7.1 affects-7.5 severity/critical sig/transaction SIG:Transaction type/bug The issue is confirmed as a bug.

Comments

@crazycs520
Copy link
Contributor

crazycs520 commented Nov 8, 2023

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

For minimum reproduce, the TiDB's configuration as follow:

[performance]
stmt-count-limit=3

Reproduce SQL:

create table t (id int key);
set @@tidb_disable_txn_auto_retry=0;
set autocommit=0;
begin optimistic;
insert into t values (1);
insert into t values (2);
select * from t for update;
insert into t values (3);
insert into t values (4);
commit;
select * from t;

2. What did you expect to see? (Required)

When execute select * from t for update;, should got error:

mysql> set @@tidb_disable_txn_auto_retry=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into t values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t for update;
ERROR 1105 (HY000): statement count 4 exceeds the transaction limitation, transaction has been rollback, autocommit = false
mysql> insert into t values (3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (4);
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from t;
+----+
| id |
+----+
|  3 |
|  4 |
+----+
2 rows in set (0.00 sec)

3. What did you see instead (Required)

All SQL are executed successfully, but the finally select result are following, the user may think that the transaction has been partially committed.

mysql> set @@tidb_disable_txn_auto_retry=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into t values (1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t for update;
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

mysql> insert into t values (3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (4);
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from t;
+----+
| id |
+----+
|  3 |
|  4 |
+----+
2 rows in set (0.00 sec)

But you can find an error log in TiDB log file:

[2023/11/08 16:13:29.486 +08:00] [ERROR] [terror.go:317] ["function call errored"] [error="statement count 4 exceeds the transaction limitation, autocommit = false"] [stack="github.com/pingcap/tidb/pkg/parser/terror.Call\n\t/Users/cs/code/goread/src/github.com/pingcap/tidb/pkg/parser/terror/terror.go:317\ngithub.com/pingcap/tidb/pkg/server.(*clientConn).handleStmt\n\t/Users/cs/code/goread/src/github.com/pingcap/tidb/pkg/server/conn.go:2042\ngithub.com/pingcap/tidb/pkg/server.(*clientConn).handleQuery\n\t/Users/cs/code/goread/src/github.com/pingcap/tidb/pkg/server/conn.go:1800\ngithub.com/pingcap/tidb/pkg/server.(*clientConn).dispatch\n\t/Users/cs/code/goread/src/github.com/pingcap/tidb/pkg/server/conn.go:1287\ngithub.com/pingcap/tidb/pkg/server.(*clientConn).Run\n\t/Users/cs/code/goread/src/github.com/pingcap/tidb/pkg/server/conn.go:1066\ngithub.com/pingcap/tidb/pkg/server.(*Server).onConn\n\t/Users/cs/code/goread/src/github.com/pingcap/tidb/pkg/server/server.go:701"]

4. What is your TiDB version? (Required)

master: 89ec3d3

@crazycs520 crazycs520 added type/bug The issue is confirmed as a bug. affects-6.5 affects-7.5 labels Nov 8, 2023
@crazycs520 crazycs520 self-assigned this Nov 8, 2023
@ti-chi-bot ti-chi-bot bot added may-affects-5.3 This bug maybe affects 5.3.x versions. may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 may-affects-7.1 labels Nov 8, 2023
@cfzjywxk cfzjywxk added the sig/transaction SIG:Transaction label Nov 8, 2023
@crazycs520 crazycs520 added affects-5.0 This bug affects 5.0.x versions. affects-5.1 This bug affects 5.1.x versions. affects-5.2 This bug affects 5.2.x versions. affects-5.3 This bug affects 5.3.x versions. affects-5.4 This bug affects 5.4.x versions. affects-6.1 labels Nov 8, 2023
@ti-chi-bot ti-chi-bot bot removed may-affects-5.3 This bug maybe affects 5.3.x versions. may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 labels Nov 8, 2023
@crazycs520 crazycs520 added may-affects-5.4 This bug maybe affects 5.4.x versions. may-affects-6.1 affects-7.1 and removed may-affects-6.1 may-affects-7.1 may-affects-5.4 This bug maybe affects 5.4.x versions. labels Nov 8, 2023
@ti-chi-bot ti-chi-bot bot added the affects-4.0 This bug affects 4.0.x versions. label Oct 14, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
affects-4.0 This bug affects 4.0.x versions. affects-5.0 This bug affects 5.0.x versions. affects-5.1 This bug affects 5.1.x versions. affects-5.2 This bug affects 5.2.x versions. affects-5.3 This bug affects 5.3.x versions. affects-5.4 This bug affects 5.4.x versions. affects-6.1 affects-6.5 affects-7.1 affects-7.5 severity/critical sig/transaction SIG:Transaction type/bug The issue is confirmed as a bug.
Projects
None yet
2 participants