Re: MySQL 8.0 Command Line Client Error 1205-CodePudding

MySQL 8.0 Command Line Client is giving me a timeout error and I have restarting the transaction by typing "start transaction" another time. Keep in mind that I have another command line open with the table CIA_DATA.new_table and it is also being updated with the same changes. (I am doing this to follow a tutorial.) Here is the script:

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

mysql> update CIA_DATA.new_table set c1 = 2 where c1 = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Updated code for help in Answers Comments:

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

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

mysql> drop table CIA_DATA.new_table;
ERROR 1051 (42S02): Unknown table 'cia_data.new_table'
mysql> create table CIA_DATA.new_table ( c1 int primary key);
Query OK, 0 rows affected (0.08 sec)

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

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

mysql> select * from CIA_DATA.new_table;
 ---- 
| c1 |
 ---- 
|  1 |
 ---- 
1 row in set (0.00 sec)

mysql> update CIA_DATA.new_table set c1 = 2 where c1 = 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> close transaction
    -> \c
mysql> close transaction;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'close transaction' at line 1
mysql> --innodb-lock-wait-timeout=#
    -> \c
mysql> --innodb-lock-wait-timeout=#;
    ->

Thanks, thecoolgeek

CodePudding user response：

What happen ony your computer:

One transaction locks the table and you didn't release the lock by endind the transaction.
you start a new tansaction and it encounters a lockesd table and waits for the release
As the first transaction and the lock wasn't released, the secnd goes into to timeout.

Locks are in the best case difficult. and sometimes it takes hours or days to solve them.

so close the tranaction as fast as you can, so that the timeout not happen or increase the timeout https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout