Error: Lock wait timeout exceeded; try restarting transaction

General
1

I got a few hundred of these errors. What does this mean?

Tim

2

Hi @Tim_Jones,

Can you provide us with the time when you were getting these errors and the steps we can take to reproduce them?

Regards,
Bohdan

3

Sep 3, 5:29 PM ET.

This was random, so I don’t have any way to replicate it. My concern is this is related to the ongoing issues you’re having.

Tim

4

Hello @Tim_Jones,

The ENOTFOUND bl-server error seems to be a separate issue. The “Lock wait timeout” error typically indicates that there is a large query locking the table, and for some reason, we are not receiving a response within the expected time frame. We will investigate the database logs to gather more information on this.

5

Thanks @sergey.kuk.

This is the codeless that I think the error is coming from -

image
image1150×202 16.3 KB

This code updates the Shipments table with updated tracking information when a webhook calls the endpoint. There may be many updates in a short time, which would explain the overlapping calls.

Is there a way to rewrite this to avoid a table-level lock?

Thanks,
Tim

6

I would really appreciate help on this. I’m getting more errors and need this to work.

Thanks,
Tim

7

Hello @Tim_Jones

Is the TrackingNumber unique for each record?
If so, then it is not advisable to use Bulk operation.
Bulk Update means Updating multiple objects, but you need Update single object.

Regards,
Volodymyr

8

Hi @Volodymyr_Ialovyi

Yes, TrackingNumber is unique.

I updated the code to use a transaction to get the row and update it. Can you confirm this is not a table-level lock?

Thanks,
Tim

9

Hello @Tim_Jones

Think of it this way – It locks every row it had to look at:

  • No index on the column - It had to check every row, so all rows are locked. That effectively locks the entire table.
  • UNIQUE index on the column - Only one row need be touched, hence, locked.
  • In between… A non-unique INDEX on the column – It must lock all the rows with that value.

Transaction - Updating a single object - ?
If yes: update by objectId unique index field.

Do you specify Transaction Isolation?
Serializable Isolation:

The database provides read/write locks for the entire set of objects retrieved in a transaction

Regards,
Volodymyr

10

Hi @Volodymyr_Ialovyi

Thanks for going over this with me.

I have added an index and a unique requirement to the TrackingNumber column. This is my transaction. Am I correct in that I should update the isolation to Serializable?

image
image1346×737 41.1 KB

Thank you,
Tim

11

Am I correct in that I should update the isolation to Serializable?

Hi. Generally it is not required. The serializable level is the higher level of isolation, but it requires significantly more time for processing. The default repeatable-read isolation is sufficient for 99% of work.

12

Thanks @oleg-vyalyh!

Since making the changes suggested by @Volodymyr_Ialovyi I have not gotten any table lock error messages. I really appreciate the help.

Tim