Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: locks and foreign keys

    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.

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    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
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by sathyaram_s
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  6. #6
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by J Petruk
    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
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •