|
It is important to understand that using cursors in your stored procedures can affect other applications or people using the database. Whenever you access data, DB2 puts a lock on the row or table where it is stored. Locks in DB2 vary in severity from those that will prevent anyone from reading any data in a table to a row on a row that only lasts for as long as you are reading it. The type of lock that DB2 uses depends on the type of cursor being used and the DB2 isolation level. Both factors are summarized in the following sections. Lock ModesDB2 has different lock modes, and each has its own specific attributes. The common locks that can be held on rows are listed in Table 5.1. A complete list of all the lock modes and their behaviors can be found in the DB2 documentation under "Lock Attributes."
Isolation LevelsIsolation levels are used by DB2 to control the level of protection you provide to the data you are reading. Increasing the isolation level will reduce the ability to access the data by the other applications. This makes your queries more consistent, but it can also greatly reduce concurrency of your application as more lock contention can occur. The four isolation levels in order from least severe to most severe are Uncommitted Read, Cursor Stability (the default), Read Stability, and Repeatable Read. In addition to the four isolation levels supported by all three platforms, DB2 for iSeries supports a fifth isolation level No Commit which is less severe than Uncommitted Read. Uncommitted Read (UR)This isolation level is the lowest level and is often referred to as Dirty Read because it ignores all locks on rows in a table and will return the data regardless. With this isolation level, any row read during a unit of work can be read and changed by any other application. The application can also read any uncommitted changes made by other application. Uncommitted changes are also known as dirty data. The values could be rolled back, and you would never know that you have read incorrect data. This isolation level is normally only used in read-only databases, such as data warehouses, or where data integrity is not an issue. No Commit (NC)iSeries OnlyThis isolation level behaves the same as the UR isolation level, with the exception that COMMIT and ROLLBACK operations have no effect on the SQL statements. What this means is that when a COMMIT or ROLLBACK is issued, cursors are not closed and locks held are not released (even those acquired with the LOCK TABLE statement). Additionally, any updates to the database are applied immediately and visible to other applications. With this isolation level, no locks are acquired for READ operations. For UPDATE operations, the duration of the lock is minimized--for example, a row is locked only while it is being updated. Cursor Stability (CS)This is the default isolation level and it will keep the row currently being read or updated locked and protected from changes by other applications. You will also not be able to read data altered by other processes until the data has been committed. A share lock will be placed on any rows being read, while an X lock will be used for deleted rows. An X lock will also be placed for updated rows. Read Stability (RS)Read Stability is similar to Cursor Stability but instead of locking the current row that is being processed, it will apply the appropriate locks to all rows that are in the result set. This will ensure that within the same transaction, rows that have been previously read by a cursor cannot be altered by other applications. Repeatable Read (RR)Repeatable Read is the strongest isolation level. DB2 will lock all the rows in the result set as well as rows that are used to build the result set. If a row is read by the application using this isolation level, no other application can alter it until the transaction is completed. This will ensure that your result set is consistent. However, it can greatly reduce concurrency because of the large number of locks may be held. The example in Figure 5.23 uses the data in the department table to demonstrate how the rows would be locked by each ISOLATION level as a cursor runs against it. Figure 5.23. Partial table for DEPARTMENT.DEPTNO LOCATION ------ -------- A00 A B01 B0 C01 B1 D01 D D11 E Figure 5.24. SELECT statement for the example.SELECT deptno, location FROM department WHERE location LIKE 'B%' The example in Figure 5.24 demonstrates the locks that will he held on the different rows in the department table when the cursor is first opened. Figure 5.25 demonstrates which locks will be held during the second fetch. Figure 5.25. Lock types on each row at first fetch of row (B01, B0).Deptno Location UR/NC CS RS RR A00 A S B01 B0 S S S C01 B1 S S D01 D S E01 E S Figure 5.26. Lock types on each row at second fetch of row (C01, B1).Deptno Location UR/NC CS RS RR A00 A S B01 B0 S S C01 B1 S S S D01 D S E01 E S As you can see in the example, the locking behavior on each row is the same with RR, RS, and UR/NC during the first and second fetch. The row that is locked does vary with CS because when a row is fetched, the lock on the previous row is released. You should use the isolation level that gives you the data integrity you need in your queries but also causes the minimal amount of contention. Cursor Stability and Read Stability are most often used to achieve these goals. Controlling Locking in DB2 LUWDatabase applications can sometimes result in concurrency issues when two different applications both try to access the same row. By default, DB2 will lock all rows that it scans until it determines if the row will be used in the result set. This is normally the desired behavior, but it may cause unnecessary locks if there is lock contention on a row that will end up not even being in the result set. You have the ability to change this behavior for CS and RS. DB2 can evaluate the row first before it tries to lock it. The DB2 registry variable DB2_EVALUNCOMMITTED must first be turned on before this will take effect. The registry variable can be set by following command by a user with DB2 System Administrator authority. The example in Figure 5.27 demonstrates how the DB2_EVALUNCOMMITTED registry variable can be set. Figure 5.27. Turning on the DB2_EVALUNCOMMITTED behavior.Set: db2set DB2_EVALUNCOMMITTED=YES Unset db2set DB2_EVALUNCOMMITTED= The following example illustrates the new uncommitted evaluation behavior. The table in Figure 5.28 illustrates the new uncommitted evaluation behavior on the department table. Figure 5.28. DEPARTMENT table data.DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION ------ ----------------------------- ------ -------- ---------------- A00 SPIFFY COMPUTER SERVICE DIV. 000010 A00 - B01 PLANNING 000020 A00 - C01 INFORMATION CENTER 000030 A00 - D01 DEVELOPMENT CENTER - A00 - D11 MANUFACTURING SYSTEMS 000060 D01 - D21 ADMINISTRATION SYSTEMS 000070 D01 - E01 SUPPORT SERVICES 000050 A00 - E11 OPERATIONS 000090 E01 - E21 SOFTWARE SUPPORT 000100 E01 - The following two transactions in Figure 5.29 are acting against the same data table. Figure 5.29. department table data.Application 1 | Application 2 ------------------------------------------------------------------------------- CONNECT TO sample | CONNECT TO sample (+c) ------------------------------------------------------------------------------- UPDATE | department | SET mgrno = 005000 | WHERE deptno = B01 | ------------------------------------------------------------------------------- | SELECT deptname, mgrno | FROM department | WHERE mgrno > 00050 ------------------------------------------------------------------------------- The first application updates a row in the table without committing the change. This is controlled by the command-line option (+c) to turn off autocommit. The update stops Application 2 from returning any rows because it waits to obtain a lock on the second row before the application can continue processing the remainder of the rows. The second application waits even though the row it is waiting on will not be part of its final result set. Unless the first application commits or rolls back the transaction to release the lock, the second application would not be able to complete its update. This problem could be avoided if the evaluate uncommitted feature was used. With the feature turned on, the second application would have first checked to see if the second row is part of the final result set. Because it would not be, the application will not lock the row and no lock contention would occur. Application 2 could then continue processing the other rows. Using the feature can help you minimize concurrency issues that can occur with data that is updated, and also has reports run against it. No Wait and Lock WaitThe amount of time an application will wait for a lock before returning an error is set at the database level using the LOCKTIMEOUT parameter. This controls the number of seconds that an application should wait for a lock. However, there are times when you do not want your application to ever wait on a lock. You may want to know immediately if there is any lock contention for certain processes or applications. You can directly control how long an individual application will wait for a lock using the SET CURRENT LOCK TIMEOUT command. This command will override the default wait time for the particular application. This would be useful in a system where there is a mixed work load of large long running reports and update batch sessions. The reports would be willing to wait indefinitely until the locks were freed, but the update sessions may want to return immediately. You could then determine why there is lock contention occurring and fix the problem. The syntax of the command is given in Figure 5.30. Figure 5.30. The SET LOCK MODE syntax..-CURRENT-. .-=-. >>-SET--+---------+--LOCK TIMEOUT--+---+------------------------> >--+-WAIT-----------------------+------------------------------>< +-NOT WAIT-------------------+ +-NULL-----------------------+ | .-WAIT-. | +-+------+--integer-constant-+ '---host-variable---' Figure 5.31 demonstrates how you can use the SET LOCK TIMEOUT command. Figure 5.31. SET CURRENT LOCK TIMEOUT examples.Setting lock waiting to 0 SET CURRENT LOCK TIMEOUT NOT WAIT Setting the application for up to 30 seconds on a lock SET CURRENT LOCK TIMEOUT WAIT 30 Return the lock timeout value to the database LOCKTIMEOUT value SET CURRENT LOCK TIMEOUT NULL Controlling Locking in DB2 UDB for iSeriesDB2 UDB for iSeries is tightly integrated with the operating system. As such, 'its locking algorithms are the same as the i5/OS object locking algorithms. For DML statements, the record (row) wait time can be modified using the WAITRCD parameter of the create (CRTPF, CRTLF) or change (CHGPF, CHGLF) file commands or the override database file command (OVRDBF). The default is 60 seconds. Similarly, the file (table) wait time can be modified using the WAITFILE parameter of the create or change file commands, or the override database file command. The default is 0 seconds. For DDL statements and the LOCK TABLE statements, the lock-wait time is defined by the job default wait time (DFTWAIT), and can be changed with the change job (CHGJOB) or class (CHGCLS) commands. Controlling Locking in DB2 UDB for zSeriesDB2 for zSeries uses two bind parameters to control lock duration:
Evaluate Uncommitted and Avoiding LockszSeries has a parameter called EVALUATE UNCOMMITTED that is the equivalent to the registry variable DB2_EVALUNCOMMITTED used in LUW. This parameter can be found in installation panel DSNTIP8. In addition, zSeries has mechanisms to avoid locking on rows and pages for committed data. A small instruction is used to test if a row or page contains committed data. If it does, zSeries will avoid using locks on that row or page at all. Lock avoidance is normally the default for most use; however, a combination of settings and events need to occur for zSeries to avoid using locks:
Using isolation level UR by itself will also provide for lock avoidance. Lock Timeout and Deadlock DetectionzSeries does not have controls available at the statement level to manage lock timeouts or deadlock detection. The parameters that are used for these at the subsystem level are
|
|