Why doesn’t SELECT release IS lock in DB2

I came across this question when analyzing the following application snapshot:

 
Application Snapshot

Application handle                         = 56710
Application status                         = UOW Waiting      <<<<<<<<<<< UOW Waiting means DB2 server is waiting for application submit the next statement, does NOT indicate the current transaction has committed.
Status change time                         = 02/26/2017 12:30:01.381390
Application code page                      = 1208
Application country/region code            = 0
...
Connection request start timestamp         = 02/26/2017 01:01:27.323817
Connect request completion timestamp       = 02/26/2017 01:01:27.325649
Application idle time                      = 4 days 19 hours 30 minutes  <<<<<<<<<<< This application has been idle for more than 4 days
CONNECT Authorization ID                   = CMS
Client login ID                            = CMS
...
Number of SQL requests since last commit   = 136         <<<<<<<<<<<<<<<<<<
Commit statements                          = 59228
...
Most recent operation                      = Fetch           <<<<<<<<<<<<<<< 
Cursor name                                = SQL_CURSH200C21
Most recent operation start timestamp      = 02/26/2017 12:30:00.280759
Most recent operation stop timestamp       = 02/26/2017 12:30:01.381390
Agents associated with the application     = 1
Number of hash joins                       = 679
...
Blocking cursor                            = YES
Dynamic SQL statement text:                           <<<<<<<<<<<<<<<<<<<<<<<
SELECT ...

Q & A:
Q: Whether the SELECT statement was executing or not?
If it was executing, why the Application status is “UOW Waiting”?
If not, why doesn’t it release the IS table lock?

A: It was not executing. It didn’t release IS table lock simply because the statement didn’t commit.

Any transaction needs to be properly terminated — and that applies to selects as well. Consider running transactionally a “select a from b where c > 12” and then “select a from b where c <= 12”; within a transaction the DB has to guarantee that every a gets returned exactly once either from the first or second select, not both (assuming c is never null;-). Without transactionality, some a’s might fall between the cracks or be returned twice if their corresponding c was changed by a different transaction, and that’s just not ACID!-)

So when you do not need separate SELECT queries to be transactional wrt each other, tell the DB! And the way you tell, is by terminating the transaction after each select (normally commit is what you use for the purpose, though I guess you could, indifferently, choose to use rollback here;-).

Every DB2 query (as per general DB theory) runs transactionally (you may set a mode where each transaction is auto-committed after each query, but choosing explicit termination has advantages).

Here is the test scenario.

$ db2 "create table test(c1 int)"
DB20000I  The SQL command completed successfully.
$ db2 "insert into test values(1),(2),(3)"
DB20000I  The SQL command completed successfully.
$ db2pd -d sample -lock   ===///No locks yet

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:02:30 -- Date 2017-03-03-08.23.57.803708

Locks:
Address            TranHdl    Lockname                   Type           Mode Sts Owner      Dur HoldCount  Att        ReleaseFlg rrIID

$ db2 +c “DECLARE curs CURSOR FOR SELECT * from test” ===/// +c means switch off auto commit
DB20000I The SQL command completed successfully.
$ db2 +c “open curs”
DB20000I The SQL command completed successfully.

$ db2pd -d sample -lock  ===///Here we got the IS lock

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:03:10 -- Date 2017-03-03-08.24.37.789754

Locks:
Address            TranHdl    Lockname                   Type           Mode Sts Owner      Dur HoldCount  Att        ReleaseFlg rrIID
0x07000000413A1680 3          000000010000000100014E00D6 VarLock        ..S  G   3          1   0          0x00000000 0x40000000 0
0x07000000413A6C00 3          414141414166416415C78BFEC1 PlanLock       ..S  G   3          1   0          0x00000000 0x40000000 0
0x07000000413A2900 3          00030005000000000000000054 TableLock      .IS  G   3          1   0          0x00003000 0x40000000 0

$ db2 +c “fetch curs”

C1
———–
1

1 record(s) selected.

$ db2pd -d sample -lock

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:03:22 -- Date 2017-03-03-08.24.49.433441

Locks:
Address            TranHdl    Lockname                   Type           Mode Sts Owner      Dur HoldCount  Att        ReleaseFlg rrIID
0x07000000413A1680 3          000000010000000100014E00D6 VarLock        ..S  G   3          1   0          0x00000000 0x40000000 0
0x07000000413A6C00 3          414141414166416415C78BFEC1 PlanLock       ..S  G   3          1   0          0x00000000 0x40000000 0
0x07000000413A2900 3          00030005000000000000000054 TableLock      .IS  G   3          1   0          0x00003000 0x40000000 0

===/// check out application snapshot, Number of SQL requests since last commit = 2 > 0 means current transaction has not committed

$ db2 get snapshot for application APPLID  *LOCAL.ad920634.170303132127


Number of SQL requests since last commit   = 2
Commit statements                          = 5
Rollback statements                        = 0
Dynamic SQL statements attempted           = 6
Static SQL statements attempted            = 5

Most recent operation                      = Fetch
Cursor name                                =
Most recent operation start timestamp      =
Most recent operation stop timestamp       =
Agents associated with the application     = 1
Number of hash joins                       = 0
Number of hash loops                       = 0
Number of hash join overflows              = 0
Number of small hash join overflows        = 0
Number of OLAP functions                   = 0
Number of OLAP function overflows          = 0

Buffer pool temporary xda physical reads   = 0
Blocking cursor                            = YES
Dynamic SQL statement text:
SELECT * from test

===///Check snapshot again after snapshot, Number of SQL requests since last commit = 0

$ db2 commit
DB20000I  The SQL command completed successfully.

$ db2pd -d samle -lock    ===/// IS lock is gone

Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:07:37 -- Date 2017-03-03-08.29.04.673148

Locks:
Address            TranHdl    Lockname                   Type           Mode Sts Owner      Dur HoldCount  Att        ReleaseFlg rrIID


$ db2 get snapshot for application APPLID  *LOCAL.ad920634.170303132127

Number of SQL requests since last commit   = 0
Commit statements                          = 6
Rollback statements                        = 0
Dynamic SQL statements attempted           = 6

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s