0

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

datascientist1000
  • 51
  • 8
  • Sorry about that, I did not notice that "sql-server" was from Microsoft. I should have read more into the description XD – datascientist1000 Jul 17 '22 at 21:31
  • If one connection has a transaction open which is locking that table (or some of its rows), you need to commit (or rollback) that transaction before any other connections can make (potentially conflicting) changes. – MatBailie Jul 17 '22 at 21:46

1 Answers1

0

What happen ony your computer:

  1. One transaction locks the table and you didn't release the lock by endind the transaction.

  2. you start a new tansaction and it encounters a lockesd table and waits for the release

  3. 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

nbk
  • 45,398
  • 8
  • 30
  • 47
  • What command should be typed to close the transaction? May I have some code please? The link sent does not have that information as it just starts a new line when I enter it. – datascientist1000 Jul 17 '22 at 22:33
  • a COMMIT see https://dev.mysql.com/doc/refman/8.0/en/commit.html – nbk Jul 17 '22 at 22:35
  • The documentation does not state how to stop a transaction. Please tell me what to do considering I have inputted what is shown in the code in my question description titled, "Updated code for help in Answers Comments". – datascientist1000 Jul 17 '22 at 23:38
  • Then read the link again there is no close transaction only commit after commit the the transaction is finalized as only 1transactipn can be run all other are paused till the transaction was commited – nbk Jul 18 '22 at 00:00