Monday, October 5, 2009

Does Skip Uncommitted Inserts really work?


Recently I tried to use the new feature called Skip Uncommitted Inserts at one of my DB2 customers, so in order to activate the feature, we turned on the SKIPUNCI zparm. Unfortunately, DB2 did not behave exactly as I thought it would. Here are the results of a little experiment I've conducted, and I would like to hear your opinion - are these the results you would accept?

I created a two columns table - mytable (col1 int not null, col2 int) with row level locking and a primary key on col1.

Now, I used two sessions to run the following SQL Statements (naturally, not working with autocommit in order to hold the locks that are acquired):

Session #1:
insert into mytable values (1,1)

Session #2
insert into mytable values (2,2)

Session #1
select * from mytable with ur - returned both rows (as expected)

select * from mytable - this returned a single row with col1=1 (as expected, skipping the row that was inserted but not committed by session #2)

Session #2
select * from mytable with ur - returned both rows (as expected)

select * from mytable - this statement was blocked waiting for a lock held by Session#1. I would expect this statement to return the row with col1=2.

Session #1
commit - at this point

Session #2
select * from mytable - returned both rows (as expected)

Session #1
select * from mytable - this statement blocked waiting for a lock held by Session #2.


As you can see, it seems that the Skip Uncommitted inserts only works for the thread that performed the first insert against the table, and other threads are getting blocked, even though they shouldn't.
If you also encountered this problem, your are not alone... :-)

till next time


No comments:

Post a Comment