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.