Friday, February 12, 2016

SELECTs Do Not Block UPDATEs

I went on my first DBA course in 1997 and the lecturer there explained that readers do not block writers in an Oracle database. I had an issue recently which appeared to contradict this so I have reproduced it below in an Oracle 11.2.0.1 database.
 
I noticed in OEM that there was some issue in the database. As usual, click on the images to enlarge them and bring them into focus if you need to:


I looked at the Blocking Sessions screen and saw that Andrew was blocking Fred:


I clicked on the link to see the SQL which Fred was running:


I clicked on the link to see the SQL which Andrew was running:


So it seemed that: 

SELECT COUNT(*)
FROM DBA_TABLES
 
was blocking:
 
update andrew.tab1
set col1 = 2
where col1 = 1
 
As you might have guessed, I set this test up on purpose and this is what really happened. Andrew created a table:
 
SQL> conn andrew/reid
Connected.
SQL> create table tab1 (col1 number)
  2  /
 
Table created.
 
SQL>
 
He then allowed Fred to update it:
 
SQL> grant update on tab1 to fred
  2  /
 
Grant succeeded.
 
SQL>
 
He added a row to the table, saved it and updated it, setting up a lock in the process:
 
SQL> insert into tab1
  2  values (1)
  3  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> update tab1
  2  set col1 = 2
  3  where col1 = 1
  4  /
 
1 row updated.
 
SQL>
 
Finally, he counted the rows in DBA_TABLES again and again:
 
SQL> declare
  2    row_count number;
  3  begin
  4    while (1=1) loop
  5    select count(*) into row_count
  6    from dba_tables;
  7    end loop;
  8  end;
  9  /
 
Fred then came along and tried to update the same row in TAB1 but had to wait:
 
SQL> conn fred/bloggs
Connected.
SQL> update andrew.tab1
  2  set col1 = 2
  3  where col1 = 1
  4  /
 
So, what is the point of this example?
 
If you look at OEM’s Blocking Sessions screen and see a user blocking another user, the SQL shown against the blocking user is the SQL he/she is currently running. This may or may not be the cause of the lock which is holding up the user(s) underneath.

2 comments:

Anonymous said...

Nice example Andrew! This same thing had confused me before. Seeing the SELECT as the offending statement is definitely misleading.

Restarting said...

Pardon me if this sounds insane,
But that could have been because 'andrew' never 'Committed' his update and that's what's locking the table update by 'fred' !