Db/2 – Locking – Using Db2CMD

Background

Wanted to see what it will take to create a blocking situation in DB/2 LUW.

Scenario

Outline

  1. Create Table
  2. Add Data
  3. Update Data
    • Setup for no commit
      • SQL
        • update command options using c off;
    • Payload
      • Update data, but do not commit
      • Update data

Processing

Create Table

CREATE TABLE "bank"."balance"
(
	  "id" int NOT NULL
	, "balance" DECIMAL(30,2) NOT NULL

)
//

ALTER TABLE "bank"."balance"
ADD CONSTRAINT "PK_BANK_BALANCE"
PRIMARY KEY
(
	"id"
)
//

COMMIT
//

Add Data

DELETE
FROM   "bank"."balance"
//

INSERT INTO "bank"."balance"
(
	  "id"
	, "balance"
)
VALUES
	  ( 1, 1 )
	, ( 2, 2)
	, ( 3, 3)
	, ( 4, 4)
	, ( 5, 5)
//

COMMIT

Update Data

Update Data -01

connect to WIDEWRLD;

update command options using c off;

SET ISOLATION LEVEL RR;

UPDATE "bank"."balance" SET "balance" = "balance" + 0.001 WHERE "id" = 5 WITH RR;

Output -01

session_01_20181223_1215PM

Update Data -02

connect to WIDEWRLD;

update command options using c off;

SET ISOLATION LEVEL RR;

UPDATE "bank"."balance" SET "balance" = "balance" + 0.001 WHERE "id" = 5 WITH RR;

Output -02

session_02_20181223_12175PM

Summary

During our updates, we set “auto commit off“, and we intentionally did not commit our session.

We opened up a new session window and tried updating the same record.

We observed that our second session never completed.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s