I have a db schema with two tables related by a "foreign key"
relationship as described by the following SQL code:


CREATE TABLE T1
(
NAME VARCHAR(32) NOT NULL,
MODE INTEGER NOT NULL,
...

PRIMARY KEY( NAME )
);

CREATE TABLE T2
(
NAME VARCHAR(32) NOT NULL,
MODE INTEGER NOT NULL,
....

PRIMARY KEY( NAME )
);

ALTER TABLE T2 ADD CONSTRAINT FK_NAME
FOREIGN KEY (NAME)
REFERENCES T1(NAME)
;


The problem that I have is the following.
I issue the following SQL command:
INSERT INTO T2 set (NAME, MODE, .....) values ('name1', .....)

In order to verify the referential integrity the DB2 has to verify if in table T1 exists an entry with a name 'name1' and, if so, it then inserts the specified row in table T2. Does DB2 holds a lock on the entry in table T1 untill the insert in table T2 gets completed or he releases the lock on the row in T1 as soos as he has verified that a row is present ?
What I want to assure is that nobody is able to update a row in table T1 when an insert in table T2, involving the same row, is pending( in particular between the select on table T1 to check the referential integrity and the actual insert of the row in table T2) . Does DB2 already guarantees this ?

Thanks in advance for your help




I was wandering how DB2 manages locks on the two tables when I perform an insert on tables 2.

It probably depends on your isolation level, but in my quick test it indeed locks the row in T1 during an insert into T2...

Window 1:
db2 insert into t1 values ('test', 1);
db2 commit

Window 2:
db2 -c- "insert into t2 values ('test', 1);
-> success

Window 1:
db2 -c- "update t1 set name = 'test9' where name = 'test'"
-> hangs

Window 2:
db2 commit
-> success

Window 1:
-> SQL0531N The parent key in a parent row of relationship "PETRUK.T2.FK_NAME"
cannot be updated. SQLSTATE=23504

It does not depend on isolation level. DB2 will always hold the lock long enough to guarantee data integrity. That is why it is called referential integrity.

Jonathan, Did you do a 'snapshot for locks' in the lock-wait period ? What locks does the parent and the child table hold ?

Thanks

Sathyaram

Snapshot attached.

Oops, that didn't work...
Database Lock Snapshot

Database name = PETRUK
Database path = C:\DB2\NODE0000\SQL00002\
Input database alias = PETRUK
Locks held = 9
Applications currently connected = 2
Agents currently waiting on locks = 1
Snapshot timestamp = 04-05-2004 14:54:33.649652

Application handle = 12
Application ID = *LOCAL.DB2.011085185223
Sequence number = 0001
Application name = db2bp.exe
CONNECT Authorization ID = PETRUK
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1252
Locks held = 4
Total wait time (ms) = Not Collected

List Of Locks
Lock Name = 0x02001300040000000000000052
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 4
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = PETRUK
Table Name = T2
Mode = X

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

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

Lock Name = 0x02001300000000000000000054
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 19
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = PETRUK
Table Name = T2
Mode = IX


Application handle = 11
Application ID = *LOCAL.DB2.012305185213
Sequence number = 0007
Application name = db2bp.exe
CONNECT Authorization ID = PETRUK
Application status = Lock-wait
Status change time = Not Collected
Application code page = 1252
Locks held = 5
Total wait time (ms) = Not Collected

List Of Locks
Lock Name = 0x02001200040000000000000052
Lock Attributes = 0x00000020
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 4
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = PETRUK
Table Name = T1
Mode = X

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

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

Lock Name = 0x02001300000000000000000054
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 19
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = PETRUK
Table Name = T2
Mode = IS

Lock Name = 0x02001200000000000000000054
Lock Attributes = 0x00000000
Release Flags = 0x40000000
Lock Count = 1
Hold Count = 0
Lock Object Name = 18
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = PETRUK
Table Name = T1
Mode = IX