> db2 connect to VSISP user vsisp12

Database Connection Information

 Database server        = DB2/LINUX 9.7.0
 SQL authorization ID   = VSISP12
 Local database alias   = VSISP

> db2 attach to vsisls4 user vsisp12

Instance Attachment Information

 Instance server        = DB2/LINUX 9.7.0
 Authorization ID       = VSISP12
 Local instance alias   = VSISLS4

> db2 list applications

Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
VSISP12  db2bp          56197      134.100.11.72.6819.170512104138                                VSISP    1 

> db2 get snapshot for locks for application agentid 56197

Application Lock Snapshot

Snapshot timestamp                         = 05/12/2017 13:30:53.596938

Application handle                         = 56197
Application ID                             = 134.100.11.72.6819.170512104138
Sequence number                            = 00003
Application name                           = db2bp
CONNECT Authorization ID                   = VSISP12
Application status                         = UOW Waiting
Status change time                         = Not Collected
Application code page                      = 1208
Locks held                                 = 0
Total wait time (ms)                       = Not Collected

# set isolation level
SET [CURRENT] ISOLATION [=] {UR | CS | RR | RS | RESET}
VALUES CURRENT ISOLATION

# 4.1

a)

> VALUES CURRENT ISOLATION

<blank>

b)

CREATE TABLE OPK (ID INT, NAME VARCHAR(255));
INSERT INTO OPK (ID, NAME) VALUES 
(1, 'trump'),
(2, 'merkel'),
(3, 'walter'),
(4, 'schulz'),
(1, 'test');

c)

List Of Locks
 Lock Name                   = 0x53514C43324832307F4760B841
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal Plan Lock
 Mode                        = S

 Lock Name                   = 0x00001101000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 273
 Object Type                 = Table
 Tablespace Name             = SYSCATSPACE
 Table Schema                = SYSIBM
 Table Name                  = SYSUSERAUTH
 Mode                        = IS

d)

List Of Locks
 Lock Name                   = 0x02000F01050000000000000052
 Lock Attributes             = 0x00000000
 Release Flags               = 0x00000001
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 5
 Object Type                 = Row
 Tablespace Name             = USERSPACE1
 Table Schema                = VSISP12
 Table Name                  = OPK
 Mode                        = NS

 Lock Name                   = 0x53514C43324832307F4760B841
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal Plan Lock
 Mode                        = S

 Lock Name                   = 0x02000F01000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x00000001
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 271
 Object Type                 = Table
 Tablespace Name             = USERSPACE1
 Table Schema                = VSISP12
 Table Name                  = OPK
 Mode                        = IS

 Lock Name                   = 0x00001101000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 273
 Object Type                 = Table
 Tablespace Name             = SYSCATSPACE
 Table Schema                = SYSIBM
 Table Name                  = SYSUSERAUTH
 Mode                        = IS

# 4.2

a)

INSERT INTO OPK (ID, NAME) VALUES (5, 'hbf');

insert does happen

new row appears when querying for it

upon commit, nothing changes

b)

> db2 set isolation RR

INSERT INTO OPK (ID, NAME) VALUES (6, 'hbf');

insert doesn't finish

the new row is not appearing upon querying

List Of Locks
 Lock Name                   = 0x53514C43324832307F4760B841
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal Plan Lock
 Mode                        = S

 Lock Name                   = 0x02000F01000000000000000054
 Lock Attributes             = 0x00000010
 Release Flags               = 0x0000000F
 Lock Count                  = 2
 Hold Count                  = 0
 Lock Object Name            = 271
 Object Type                 = Table
 Tablespace Name             = USERSPACE1
 Table Schema                = VSISP12
 Table Name                  = OPK
 Mode                        = S

 Lock Name                   = 0x00001101000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 273
 Object Type                 = Table
 Tablespace Name             = SYSCATSPACE
 Table Schema                = SYSIBM
 Table Name                  = SYSUSERAUTH
 Mode                        = IS

after the commit of first transaction the content is inserted

c) 

held locks

List Of Locks
 Lock Name                   = 0x53514C43324832307F4760B841
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal Plan Lock
 Mode                        = S

 Lock Name                   = 0x02000F01000000000000000054
 Lock Attributes             = 0x00000010
 Release Flags               = 0x0000000F
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 271
 Object Type                 = Table
 Tablespace Name             = USERSPACE1
 Table Schema                = VSISP12
 Table Name                  = OPK
 Mode                        = S

 Lock Name                   = 0x00001101000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 273
 Object Type                 = Table
 Tablespace Name             = SYSCATSPACE
 Table Schema                = SYSIBM
 Table Name                  = SYSUSERAUTH
 Mode                        = IS

update doesn't finish

d)

CREATE TABLE MPK (ID INT NOT NULL PRIMARY KEY, NAME VARCHAR (255));

INSERT INTO MPK (ID, NAME) VALUES
(1, 'niendorf'),
(2, 'markt'),
(3, 'hagenbecks'),
(4, 'hbf');

update is happening

locks

List Of Locks
 Lock Name                   = 0x02001001070000000000000052
 Lock Attributes             = 0x00000010
 Release Flags               = 0x00000001
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 7
 Object Type                 = Row
 Tablespace Name             = USERSPACE1
 Table Schema                = VSISP12
 Table Name                  = MPK
 Mode                        = S

 Lock Name                   = 0x53514C43324832307F4760B841
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal Plan Lock
 Mode                        = S

 Lock Name                   = 0x00001101000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 273
 Object Type                 = Table
 Tablespace Name             = SYSCATSPACE
 Table Schema                = SYSIBM
 Table Name                  = SYSUSERAUTH
 Mode                        = IS

 Lock Name                   = 0x02001001000000000000000054
 Lock Attributes             = 0x00000010
 Release Flags               = 0x00000001
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 272
 Object Type                 = Table
 Tablespace Name             = USERSPACE1
 Table Schema                = VSISP12
 Table Name                  = MPK
 Mode                        = IS

# 4.3

a)

the update command of the first connection is completed,
the update command of the second connection (no isolation set) is stalled

locks

List Of Locks
 Lock Name                   = 0x02000F01060000000000000052
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 6
 Object Type                 = Row
 Tablespace Name             = USERSPACE1
 Table Schema                = VSISP12
 Table Name                  = OPK
 Mode                        = X

 Lock Name                   = 0x53514C43324832307F4760B841
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal Plan Lock
 Mode                        = S

 Lock Name                   = 0x02000F01000000000000000054
 Lock Attributes             = 0x00000010
 Release Flags               = 0x4000000F
 Lock Count                  = 2
 Hold Count                  = 0
 Lock Object Name            = 271
 Object Type                 = Table
 Tablespace Name             = USERSPACE1
 Table Schema                = VSISP12
 Table Name                  = OPK
 Mode                        = SIX

 Lock Name                   = 0x00001101000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 2
 Hold Count                  = 0
 Lock Object Name            = 273
 Object Type                 = Table
 Tablespace Name             = SYSCATSPACE
 Table Schema                = SYSIBM
 Table Name                  = SYSUSERAUTH
 Mode                        = IS

b)

both transactions stall

the command of the first connection succeeds
the command of the second connection is rolled back because of a deadlock

the table content doesn't change

locks

List Of Locks
 Lock Name                   = 0x02000F01060000000000000052
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 6
 Object Type                 = Row
 Tablespace Name             = USERSPACE1
 Table Schema                = VSISP12
 Table Name                  = OPK
 Mode                        = X

 Lock Name                   = 0x53514C43324832307F4760B841
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 0
 Object Type                 = Internal Plan Lock
 Mode                        = S

 Lock Name                   = 0x00001101000000000000000054
 Lock Attributes             = 0x00000000
 Release Flags               = 0x40000000
 Lock Count                  = 1
 Hold Count                  = 0
 Lock Object Name            = 273
 Object Type                 = Table
 Tablespace Name             = SYSCATSPACE
 Table Schema                = SYSIBM
 Table Name                  = SYSUSERAUTH
 Mode                        = IS

 Lock Name                   = 0x02000F01000000000000000054
 Lock Attributes             = 0x00000010
 Release Flags               = 0x4000000F
 Lock Count                  = 2
 Hold Count                  = 0
 Lock Object Name            = 271
 Object Type                 = Table
 Tablespace Name             = USERSPACE1
 Table Schema                = VSISP12
 Table Name                  = OPK
 Mode                        = SIX

after committing both transactions only the change from the first connection
is persisted
