Abstract: When transaction is started, the statement executing updata is successfully executed, and neither COMMIT nor rollback is performed. If you open another window and execute the upadate statement, a failure (error: lock wait timeout) occurs. However, if you perform rollback in the previous window, the update in this window can be successfully performed. In this case, consider whether this table is a memory table.

This article is shared by PFloyd from Huawei Cloud community. The principle of lock wait problem when The Storage table is updated concurrently.

When transaction is enabled, the statements that execute updata are successfully executed. Commit or ROLLBACK are not performed. If you open another window and execute the upadate statement, a failure (error: lock wait timeout) occurs. However, if you perform rollback in the previous window, the update in this window can be successfully performed. In this case, consider whether this table is a memory table.

【 Root cause 】

If a column table is used, the update operation in a transaction is performed on a CU basis. Therefore, if the transaction is not committed, other data on the same CU will be updated concurrently, and an error will be reported when the wait times out.

[Mechanism and Principle]

1. CU is the smallest Unit of column storage. It is generated when data is imported and the generated data is fixed and cannot be changed. A SINGLE CU stores a maximum of 60,000 rows in one column. The CU in the same column is stored in a file consecutively. When the value is greater than 1 GB, the CU is switched to a new file. The Ctid field identifies a row in the storage table, consisting of cu_id and cu_id (cu_id, offset). Multiple pieces of data written at a time reside on the same CU.

2. In order to prevent the same tuple of a page from being updated by two transactions at the same time, a row-level lock is added to each update, which is a row lock for row storage and a CU lock for column storage. When a transaction update is not committed, other transactions cannot update the same CU at the same time.

3. After an update, the old tuple is marked as deleted and the new tuple is written to a new CU.

【 Case Study 】

1. According to the field error information, we can determine the concurrent update error. Waiting forShareLock on TRANSACTION XXX after.. Waiting forShareLock on transaction XXX after.. Ms.

2. The customer responded that the updated data were not the same and the IDS were different. After inquiring the customer, the problem was found in the storage table, and the updated data was checked whether they were in the same CU.

After the query, the CU is found to be the same as expected.

3. Local scene replay:

Execute update from transaction:

Transaction not committed concurrent update data wait:

SQL > select * from cu;

[Related issues]

Why is it that after a successful update, the next update does not lock each other?

This is because after the update succeeds, the old data is marked as deleted and the new data is written to the new CU. The two data are no longer the same CU, so there is no lock conflict.

[Treatment scheme]

Frequent update of the column table is not suitable for frequent update scenarios. Frequent update of the column table easily triggers concurrent update and other lock timeouts, and leads to too many small CU’s. Each CU expands to 8192 bytes for alignment, resulting in rapid disk space expansion. You are advised to use the row memory table in the scenario of frequent point-and-click query or frequent update.

Click to follow, the first time to learn about Huawei cloud fresh technology ~