tag:blogger.com,1999:blog-18371456.post5704564225150102034..comments2024-03-26T00:07:20.400-05:00Comments on The Db2 Portal Blog: DB2 Locking, Part 10: Know Your ISOLATION LevelsCraig S. Mullinshttp://www.blogger.com/profile/17077237739217901780noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-18371456.post-42132567497742045662015-09-22T11:49:31.216-05:002015-09-22T11:49:31.216-05:00The difference between RR and RS is subtle. With R...The difference between RR and RS is subtle. With RR no other program can modify the data until the unit of work is complete. With RS, other processes can insert values but not modify existing values.<br />Craig S. Mullinshttps://www.blogger.com/profile/17077237739217901780noreply@blogger.comtag:blogger.com,1999:blog-18371456.post-23447878920062177712015-09-22T10:45:17.481-05:002015-09-22T10:45:17.481-05:00Really helpful article! I have a question on the b...Really helpful article! I have a question on the behavior of DML statements when issued explicitly with an isolation level. For example, what would be the differences in locking behavior between the statements (using sample database):<br /><br />UPDATE ORG SET MANAGER = 15 WHERE DEPTNUMB = 20 WITH RR<br /><br />AND <br /><br />UPDATE ORG SET MANAGER = 15 WHERE DEPTNUMB = 20 WITH RS<br /><br />I ran the LIST APPLICATIONS SHOW DETAIL command to get the application id for my current session which is 1915. I have turned the autocommit to off so that I can see the locks held by this session<br /><br />When I run GET SNAPSHOT FOR LOCKS FOR APPLICATION AGENTID 1915, I can see the various locks held by this statement on the ORG table. <br /><br />List Of Locks<br /> Lock Name = 0x02000E00060000000000000052<br /> Lock Attributes = 0x00000000<br /> Release Flags = 0x40000000<br /> Lock Count = 1<br /> Hold Count = 0<br /> Lock Object Name = 6<br /> Object Type = Row<br /> Tablespace Name = USERSPACE1<br /> Table Schema = RUPAMBHA<br /> Table Name = ORG<br /> Mode = X<br /><br /> Lock Name = 0x4141414141664164FE8BC714C1<br /> Lock Attributes = 0x00000000<br /> Release Flags = 0x40000000<br /> Lock Count = 1<br /> Hold Count = 0<br /> Lock Object Name = 0<br /> Object Type = Internal Plan Lock<br /> Mode = S<br /><br /> Lock Name = 0x02000E00000000000000000054<br /> Lock Attributes = 0x00000000<br /> Release Flags = 0x40000000<br /> Lock Count = 1<br /> Hold Count = 0<br /> Lock Object Name = 14<br /> Object Type = Table<br /> Tablespace Name = USERSPACE1<br /> Table Schema = RUPAMBHA<br /> Table Name = ORG<br /> Mode = SIX<br /><br />Then I rollback this statement to release the locks and follow the same process for the second update statement (UPDATE ORG SET MANAGER = 15 WHERE DEPTNUMB = 20 WITH RS) which returns<br /><br />List Of Locks<br /> Lock Name = 0x02000E00060000000000000052<br /> Lock Attributes = 0x00000000<br /> Release Flags = 0x40000000<br /> Lock Count = 1<br /> Hold Count = 0<br /> Lock Object Name = 6<br /> Object Type = Row<br /> Tablespace Name = USERSPACE1<br /> Table Schema = RUPAMBHA<br /> Table Name = ORG<br /> Mode = X<br /><br /> Lock Name = 0x4141414141664164FE8BC714C1<br /> Lock Attributes = 0x00000000<br /> Release Flags = 0x40000000<br /> Lock Count = 1<br /> Hold Count = 0<br /> Lock Object Name = 0<br /> Object Type = Internal Plan Lock<br /> Mode = S<br /><br /> Lock Name = 0x02000E00000000000000000054<br /> Lock Attributes = 0x00000000<br /> Release Flags = 0x40000000<br /> Lock Count = 1<br /> Hold Count = 0<br /> Lock Object Name = 14<br /> Object Type = Table<br /> Tablespace Name = USERSPACE1<br /> Table Schema = RUPAMBHA<br /> Table Name = ORG<br /> Mode = IX<br /><br />The only difference I can see is that the table lock for RR is Share Exclusive while that for RS update is Intent Exclusive. Is this the only change?omniscienthttps://www.blogger.com/profile/07011478886445828956noreply@blogger.comtag:blogger.com,1999:blog-18371456.post-23998137793359022872014-11-17T13:09:26.592-06:002014-11-17T13:09:26.592-06:00You are correct. CS is the current default. At one...You are correct. CS is the current default. At one point, several releases ago, RR was the default option... at least it was for DB2 on z/OS.Craig S. Mullinshttps://www.blogger.com/profile/17077237739217901780noreply@blogger.comtag:blogger.com,1999:blog-18371456.post-70667296665645339502014-11-16T07:08:56.186-06:002014-11-16T07:08:56.186-06:00Repeatable READ is not the default isolation level...Repeatable READ is not the default isolation level. It is Cursor Stability (CS). Refer below:<br />http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.perf.doc/doc/c0004121.htmlAnonymousnoreply@blogger.com