Getting "Lock Wait Timeout Exceeded; Try Restarting Transaction" Even Though I'M Not Using a Transaction

Getting Lock wait timeout exceeded; try restarting transaction even though I'm not using a transaction

You are using a transaction; autocommit does not disable transactions, it just makes them automatically commit at the end of the statement.

What is happening is, some other thread is holding a record lock on some record (you're updating every record in the table!) for too long, and your thread is being timed out.

You can see more details of the event by issuing a

SHOW ENGINE INNODB STATUS

after the event (in SQL editor). Ideally do this on a quiet test-machine.

MySQL Error Code: 1205. Lock wait timeout exceeded; try restarting transaction on insert

The main reason I have this error is when an existing process is running, which prevents a new process being executed on the same tables.

Try

Show full processlist; kill x;

replace x with the process number that you think is causing the issue

Lock wait timeout when Laravel worker processes job batches

Speaking short, it was because of big number of jobs in a batch. The solution would be to add jobs to the batch in smaller chunks (using add() method).

The problem occurs because two things happen inside one transaction (see code fragment below):

1. Total number of jobs is updated in database
2. Jobs are sent to the queue (Amazon SQS in my case)

The key point is that second statement takes a lot of time if there are many jobs. And as long as it is performed transaction can't be committed (and the row affected in the first statement stays locked).
But as soon as the first job is pushed to the queue it is processed by worker. After processing the job worker tries to update the row in database, it performs SELECT ... FOR UPDATE. Which waits until timeout occurs

This is the corresponding fragment of Laravel's code:

$this->repository->transaction(function () use ($jobs, $count) {
    // Statement 1 (the affected row stays locked until transaction is committed)
    $this->repository->incrementTotalJobs($this->id, $count);

    // Statement 2 (can take a lot of time)
    $this->queue->connection($this->options['connection'] ?? null)->bulk(
        $jobs->all(),
        $data = '',
        $this->options['queue'] ?? null
    );
});

Why am I getting a Lock wait timeout exceeded error while trying to update a MySQL entry?

First see the list of locked tables:

show open tables where in_use>0;

Also you can see the thread which is using this table:

show full processlist;

And then you can kill that thread

kill 141;

Kill connections to handle 1205 (HY000): Lock wait timeout exceeded; try restarting transaction?

This solved my issue.

cursor.execute("select concat('kill ',id,';') sleeping_connections FROM information_schema.processlist isp WHERE Command = 'Sleep' and time > 60")
rows = cursor.fetchall()
df_ = pd.DataFrame( [[ij for ij in i] for i in rows] )
df_.rename(columns={0: 'Sleeping Connections'}, inplace=True);

for i in df_['Sleeping Connections']:
    cursor.execute(str(i))

---

Related Topics