DB2 parallel control-Reading Notes (1)

After a period of study and use, I have a certain understanding of DB2, and now I will record some of my experiences to facilitate the future

Db2 locks are automatically obtained when a transaction is required and released when the transaction ends (using a COMMIT or ROLLBACK command ). There are two types of locks:

S lock (share lock)-The lock obtained when the program attempts to read and prevents other programs from modifying the same row;
X lock (mutex lock) -- obtained when a program modifies, inserts, or deletes a row;

Four Problems to be Solved in concurrency control
Lost update)
Description:
App1: modify a row;
App2: Modify the same row;
App1 submission;
App2 submission;
Result:
App1 is lost when App2 is updated.
Uncommitted read)
Description:
App1: modify a row;
App2 reads the new value of the row
App1 rolls back its changes to this row
Result:
App2 reads uncommitted data, so the data is invalid;
Non-repeatable read)
Description:
App1 opens a cursor (that is, the result set) to obtain the result;
App2 deletes a row with a cursor limit;
App2 submits changes;
App1 is closed and the enabled cursor is updated;
Result:
In this case, because App1 does not get the same copy of data in a duplicate read, it cannot re-generate this dataset;
Phantom read)
Description:
App1 enables a cursor
App2 adds a row to the database, which matches the cursor;
App2 submits changes;
Close App1 and enable the cursor again
Result:
In this case, App1 will get more rows instead of the same data in repeated reads;

For the above four problems, db2 provides different protection levels:
Unimplemented read (UR)
Unimplemented reads are also called "dirty reads ". It is the lowest isolation level and provides the highest concurrency. Unless another program Company
The entire table is deleted (drop) or modified (alter). Otherwise, no rows are locked during the read operation. The update operation is consistent with the cursor.
The performance level is the same.
The isolation level still has the following problems:
* Unimplemented read
* Repeated read is not allowed.
* Phantom
Problems prevented by this isolation level:
* Update loss
Cursor stability (CS)
Cursor stability is the default isolation level. It provides a low degree of locking. At this isolation level, the "current" row of the cursor is
Locked. If the row is read only, the lock continues until a new row is accessed or the work unit is terminated. If this row is
Modification. The lock continues until the work unit is terminated.
Problems still exist at this isolation level:
* Repeated read is not allowed.
* Phantom
Problems solved by this isolation level:
* Update loss
* Unimplemented read
Read stability (RS)
With read stability, all rows retrieved by a program process in the same unit of work are locked. For
To lock all rows that match the result set. For example, if you have a table with 1000 rows and the query returns 10
Row, then only the 10 rows are locked. Read stability uses medium-level locks.

The isolation level still has the following problems:
* Phantom
Problems solved by the isolation level:
* Update loss
* Unimplemented read
* Non-repeated reads
Repeatable read (RR)
Deduplication is the highest isolation level. It provides maximum locking and minimal parallelism. All rows that generate the result set are
Will be locked, that is, the row will be locked even if it does not have to appear in the final result set. Before this unit ends, any
Other programs cannot be modified. Deleting or inserting a row will affect the result set. Repeat read to ensure that the program is in one work unit multiple times
Results are returned for the same query.
Problems still unsolved at this isolation level:
* None
 
This isolation level solves the problem
* Update loss
* Unimplemented read
* Non-repeated reads
* Phantom

Best practices
Follow the following suggestions to help achieve possible Parallelism
 
1. Make the transaction as short as possible. This allows you to frequently use the COMMIT statement as permitted by your program logic.
(Even for read-only transactions.
 
2. record transaction information only when necessary.
 
3. Fast data cleaning:
 
ALTER TABLE ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE
 
4. modify data in batches or in groups, for example:
 
Delete from (SELECT * FROM tedwas. t1 WHERE c1 =... Fetch first 3000 rows only)
 
5. Use the parallel features in DB2's data transfer tool.
 
6. Set the database-level LOCKTIMEOUT parameter (Recommended Value: 30 ~ 120 seconds ). Do not retain the default value-1. You can also use session-Based Timeout lock.
 
7. Do not retrieve unnecessary data. For example, use the fetch first n rows only clause in the SELECT statement.

Compared with oracle, it supports two transaction types: read/write transactions and read-only transactions. Concurrency control can be divided into write loss and read/write conflicts. The first case type of write loss is db2, and the second, third, and fourth cases of db2 are all in read/write conflicts.