12c HCC Row-Level Locking

In Oracle Database 12c we can find many new and shiny things… So many that we can miss the little good things really easy. I think this one, is one of them.

Previously I made a post “All About HCC“, describing how HCC is working and some of the issues that we can hit while using it. Well 12c is resolving another one of them.
Now we have Hybrid Columnar Compression – Row-level Locking! Great, but it’s not for free 😉 we need Advanced Compression in order to use it.
Let’s try it out.

First I’m creating HCC table named hccrll without the magic clause “row level locking”:

--table - CU lock:
SQL> create table hccrll(a,b) compress for query as select rownum,rownum+1 from dual connect by rownum <= 100;

Table created.

SQL>

Now I’m starting a simple pl/sql block which is just updating one row in the base transaction and another row in an autonomous one:

--test proc
begin 
--update row in this transaction
update hccrll set b=b-1 where a=9;
dbms_lock.sleep(1);
--update row in another transaction
execute immediate('
declare 
pragma autonomous_transaction;
begin 
	update hccrll set b=b-1 where a=10;
	dbms_lock.sleep(32);
	commit;
end;
');
commit;
end;
/

And the result is:

--result:
 17  /
begin
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 5
ORA-06512: at line 6


SQL>

Seems it works perfectly.

Let’s see what would happen with a new table including the magic clause:

 --table - row lock:
SQL> drop table hccrll purge;

Table dropped.

SQL> create table hccrll(a,b)  compress for query row level locking as select rownum,rownum+1 from dual connect by rownum <= 100;

Table created.

SQL>

Starting the procedure and while its running:

--check for locks:
select s.username,l.sid,l.type,l.lmode,l.ctime
from v$lock l, v$session s 
where l.sid=s.sid
and s.username='X';

--result:
SQL> /

USERNAME                  SID TY      LMODE      CTIME
------------------ ---------- -- ---------- ----------
X                         140 TM          3          8
X                         140 TM          3          9
X                         140 TX          6          9
X                         140 TX          6          8
X                         140 AE          4       1297

SQL>

And the result:

--result:
 17  /

PL/SQL procedure successfully completed.

SQL>

So, we have row-level locking for HCC. Cool.

Have fun,
Kovachev

Advertisements

6 thoughts on “12c HCC Row-Level Locking

  1. Thanks, I will need to create a “row level locking” segment as default “no row level locking” does not show this information in column compress_for. I will create a real table and check this column.

    I’ll come back if necessary.
    Thanks for this quick answer.

    1. Hi,
      There is no “no row level locking” as this is the default behavior. It will flag “% row level locking” if you create a table with such, otherwise will be only the compression level.

      Regards

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s