Recently encountered too many concurrent operations on the same table, often reporting 911 errors, resulting in a transaction rollback.
Find the source part of the error, found a piece of code close to the two UPDATE operations caused by
1. Update records according to the primary key
2. Update the record according to the phone number
Problem analysis:
Analyzing DB2’s locking mechanism, since DB2 defaults to the isolation level of CS, the principle is that the cursor locks the row change every row, which is sufficient for general applications, but if a full table scan is encountered, the CS mode locks a large number of rows in the table until the query is completed.
So when the transaction executes in step 1, it is row locked because the primary key has an index. Step 2 performs no index and the full table scan locks a large number of rows. When a row of step 1 needs to be locked, it will wait if it is occupied by a transaction, which generally does not cause problems.
This occurs when there is too much concurrency in Step 2. Because there is no index update record, a large number of rows of a table are locked each time, leading to the discovery that the lock is already locked when the next scan needs to be locked. This is when the problem occurs.
DB2 locking considerations:
Ø optimized query Sql, the establishment of appropriate indexes, makes it get indexing query, due to the scope of the index and sorting, can skip many other lines, line position to meet our need.
Ø use appropriate isolation level. Since DB2 defaults to the isolation level of CS, the principle is that the cursor locks the row change every row, which is sufficient for general applications, but if a full table scan is encountered, the CS mode locks a large number of rows in the table until the query is complete. So you can change it to UR mode, depending on your business needs, and it won’t lock any rows in the table. Or set the Isolation level in the JDBC (Isolation Levels)
Ø reasonable set the lock timeout parameter, it is mainly used to avoid the transaction being used for a long time, cause the lock and the connection cannot be freed, affect the concurrent systems. Can set up the DB parameter
Ø update operations must go index, otherwise it is easy to produce a deadlock. (check for updates and operation)
Ø avoid lock escalation phenomenon, when lock waits to achieve a certain degree (the number of row locks over loctList * percent of the lock list), would be a row lock upgraded to table locks, lock escalation. Because once a lock escalation occurs, the table is no longer locked to the row, and other transactions must wait for the lock to be released in order to access any row in the table.
modify Lock timeout (-1 means Lock timeout is not detected). In general, 10s is sufficient by default.
when the system has a serious lock wait, you can use the following SQL, Sql
db2 “select AGENT_ID,substr(STMT_TEXT,1,100) as statement,STMT_ELAPSED_TIME_MS from table(SNAPSHOT_STATEMENT(‘dbname’,-1)) as B where AGENT_ID in (select AGENT_ID_HOLDING_LK from Table (SNAPSHOT_LOCKWAIT(‘ dbname ‘,-1)) as A order by LOCK_WAIT_START_TIME ASC FETCH FIRST 20 ROWS ONLY) order by STMT_ELAPSED_TIME_MS DESC”
Thanks to the author of this article
