Unit 9: Locking and Concurrency

Task 1: Monitor the Locks Acquired for a SQL UPDATE

1. Logon to the Linux system using the user id inst23, with a password of ibm2blue.

2. Right-click the empty Linux desktop and select Open in Terminal.

3. Issue the following series of commands in the Linux terminal session:

cd $HOME/ddl
db2 connect to musicdb
db2 grant select on table music.stock to user user23
db2 +C -tvf stock_update.sql

The output will look similar to the following:

SELECT sum(qty) from music.stock where itemno=10
1
-----------
109
1 record(s) selected.
update music.stock set qty = qty + 1 where itemno between 10 and 15
DB20000I The SQL command completed successfully.
SELECT sum(qty) from music.stock where itemno=10
1
-----------
112
1 record(s) selected.

4. Issue the following command using the Linux terminal session:

db2pd -db musicdb -locks | more

5. Review the list of locks that were acquired to execute the SQL UPDATE:

• You should see a series of Exclusive (X) row locks and several table locks.
• You should see two Intent Exclusive (IX) table locks.

Task 2: Processing SQL SELECT Statements Using Different Locking Isolation Levels

1. Issue the following series of commands using the Linux terminal session:

db2 update db cfg using mon_lockwait without_hist
db2 update db cfg using mon_locktimeout without_hist
db2 -tvf create_lock_monitor.sql

2. Issue the following series of commands using the Linux terminal session:

db2 connect to musicdb
db2 +C -tvf stock_update.sql

3. To start a second Linux terminal session, right-click the empty Linux desktop and select Open in Terminal.

4. Using the second Linux terminal session, issue the following series of commands:

cd $HOME/ddl
db2 connect to musicdb user user23 using ibm2blue

5. Using the second Linux terminal session, issue the following command:

db2 -tvf stock_select_ur.sql

The output will look similar to the following:

set current lock timeout 300
DB20000I The SQL command completed successfully.
set current isolation ur
DB20000I The SQL command completed successfully.
select sum(qty) from music.STOCK where itemno = 10
1
-----------
115
1 record(s) selected.

6. Using the second Linux terminal session, issue the following command:

db2 -tvf stock_select_cs.sql

The output will look similar to the following:

set current lock timeout 300
DB20000I The SQL command completed successfully.
set current isolation cs
DB20000I The SQL command completed successfully.
select sum(qty) from music.STOCK where itemno = 10
1
-----------
112
1 record(s) selected.

7. Using the second Linux terminal session, issue the following series of commands:

db2 connect to musicdb user user23 using ibm2blue
db2 -tvf stock_select_rs.sql

8. Switching back to the first terminal session, issue the following command:

db2pd -db musicdb -wlock

9. Using the first terminal session, issue the following command:

db2pd -db musicdb -lock

10. In the second Linux terminal session, wait for the lock timeout condition to occur and the Db2 error message to be returned. The output will look similar to the following:

set current lock timeout 180
DB20000I The SQL command completed successfully.
set current isolation rs
DB20000I The SQL command completed successfully.
select sum(qty) from music.STOCK where itemno = 10
SQL0911N The current transaction has been rolled back because of a deadlock
or timeout. Reason code "68". SQLSTATE=40001

Task 3: Monitor a Db2 Database for Lock Wait Conditions Using the Data Server Manager

1. Using the first terminal session, issue the following series of commands:

db2 commit
db2 connect to musicdb
db2 +C -tvf stock_update.sql

2. Using the second Linux terminal session, issue the following series of commands:

db2 connect to musicdb user user23 using ibm2blue
db2 -tvf stock_select_rs.sql

3. Logon to DSM at http://localhost:11080 with the following credentials:

• User id: db2admin
• Password: ibm2blue

4. Click Monitor on the left side of the DSM application.

5. Select Database from the options.

6. Click Locking from the menu options at the top.

7. Use the arrow next to Blocker to expand the data and show the WAITER, user23.

8. Select the Locked Objects with Waiting Connections view.

9. Sign Out to exit the Data Server Manager.

Task 4: Analyze the Data Collected by a LOCKING Event Monitor for Locking Related Issues

1. Using the first Linux terminal session, issue the following series of commands:

db2 commit
db2 connect to musicdb
db2 set event monitor mon_locks state 0
db2 -tvf query_lock_events.sql | more

The output will look similar to the following:

select event_id , event_type, event_timestamp from lock_mon_locks
EVENT_ID EVENT_TYPE EVENT_TIMESTAMP
-------------------- ----------------------------------------------------------
1 LOCKWAIT 2017-08-01-15.43.57.377451
2 LOCKTIMEOUT 2017-08-01-15.46.52.707765
3 LOCKWAIT 2017-08-01-15.48.35.924257
4 LOCKTIMEOUT 2017-08-01-15.51.32.749114
4 record(s) selected.
select participant_no, varchar(auth_id,10) as auth_id, varchar(appl_name,20) as appl_name,
varchar(table_name,12) as tabname, varchar(table_schema,12) as tabschema, lock_object_type,
participant_type, lock_status from lock_participants_mon_locks where event_type='LOCKTIMEOUT'
PARTICIPANT_NO AUTH_ID APPL_NAME TABNAME TABSCHEMA LOCK_OBJECT_TYPE PARTICIPANT_TYPE LOCK_STATUS
-------------- ---------- -------------------- ------------ ------------ ----------------------- ---------------- --------------------
1 USER23 db2bp STOCK MUSIC ROW REQUESTER 2
2 INST23 db2bp - - - OWNER -
1 USER23 db2bp STOCK MUSIC ROW REQUESTER 2
2 INST23 db2bp - - - OWNER -
4 record(s) selected.
select participant_no, effective_isolation, varchar(stmt_operation,20) as operation, varchar(stmt_text,50) as sql_text
from lock_participant_activities_mon_locks where event_type='LOCKTIMEOUT'
PARTICIPANT_NO EFFECTIVE_ISOLATION OPERATION SQL_TEXT
-------------- ------------------- -------------------- --------------------------
1 RS DML, Select (blockab select sum(qty) from music.STOCK where itemno = 10
SQL0445W Value "DML, Select (blockable)" has been truncated. SQLSTATE=01004
1 RS DML, Select (blockab select sum(qty) from music.STOCK where itemno = 10
SQL0445W Value "DML, Select (blockable)" has been truncated. SQLSTATE=01004
2 record(s) selected with 2 warning messages printed.