Non blocking updates - Part II

In the previous blogpost we saw about non blocking updates introduces in Oracle 23c, now lets explore what’s going on with this feature
 
To get this non-blocking updates to work, we have this new syntax in Oracle 23c to define the column involved in concurrent updates (column Y) as “reservable”
 
alter table t modify y reservable;
 
“reservable” is there to tell the database that rather than locking the rows/data immediately -when the transaction begins- we would like to lock the row/data at some point in time in future.
 
Lets now repeat the demo – first session do this
 
demo@FREEPDB1> @sid
 
INST_ SESSION_ID
----- ----------
1             48
 
demo@FREEPDB1> create table t(x int primary key, y number reservable);
demo@FREEPDB1> insert into t(x,y) values(1,1);
demo@FREEPDB1> commit;
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
         1          1
 
I am going to run this update from session #1
 
demo@FREEPDB1> update t set y = y+1 where x = 1;
 
1 row updated.
 
demo@FREEPDB1>
 
from session #2 do a similar update
 
demo@FREEPDB1> @sid
 
INST_ SESSION_ID
----- ----------
1             61
 
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
         1          1
 
demo@FREEPDB1> update t set y = y+1 where x = 1;
 
1 row updated.
 
demo@FREEPDB1>
 
this session also updates the same row as the session #1 and it went fine without any blocking, lets continue and commit changes in session #2 and view the changes
 
demo@FREEPDB1> commit;
 
Commit complete.
 
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
         1          2
 
demo@FREEPDB1>
 
we can see that the value of the column Y got incremented by one – due to the update command – now go back to session #1 and do commit and see the changes.
 
demo@FREEPDB1> @sid
 
INST_ SESSION_ID
----- ----------
1             48
 
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
         1          2
 
demo@FREEPDB1> commit;
 
Commit complete.
 
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
         1          3
 
demo@FREEPDB1>
 
we can see these all can work, so the real question is how this works? Do we have multiple lock on the same row? No not really. If we look into the data dictionary after a column defined as RESERVABLE, an interesting internal Journal table created by the database will be created to keep track of changes to that reservable columns.
 
demo@FREEPDB1> select table_name
  2  from user_tables
  3  where table_name like '%'||(
  4     select object_id
  5     from user_objects
  6     where object_name ='T'
  7     and object_type ='TABLE') ;
 
TABLE_NAME
------------------------------
SYS_RESERVJRNL_118070
 
This journal table is like a Queue table, if any database transactions are there to update the “reservable” column in the table, let’s not update those values instead put all these changes into the queue table and when commit happens, we will reply the changes in order from the queue to make sure we now serialize the changes without locking issues. If we describe this journal table, here is the list of columns it has
 
demo@FREEPDB1> desc SYS_RESERVJRNL_118070
 Name                                Null?    Type
 ----------------------------------- -------- -------------------------
 ORA_SAGA_ID$                                 RAW(16)
 ORA_TXN_ID$                                  RAW(8)
 ORA_STATUS$                                  CHAR(12)
 ORA_STMT_TYPE$                               CHAR(16)
 X                                   NOT NULL NUMBER(38)
 Y_OP                                         CHAR(7)
 Y_RESERVED                                   NUMBER
 
To see what is going on with these journal table, lets do some transaction and look into the entries of these journal tables.
 
demo@FREEPDB1> @sid
 
INST_ SESSION_ID
----- ----------
1             48
 
demo@FREEPDB1> update t set y = y+1 where x = 1;
 
1 row updated.
 
demo@FREEPDB1> select * from SYS_RESERVJRNL_118070 ;
 
ORA_SAGA_ID$   ORA_TXN_ID$      ORA_STATUS$  ORA_STMT_TYPE$    X Y_OP Y_RESERVED
-------------- ---------------- ------------ ---------------- -- ---- ----------
               030020004B060000 ACTIVE       UPDATE            1 +             1
 
demo@FREEPDB1>             
 
we haven’t committed those transaction, but what happens is we didn’t updated those values or even locked them, instead we took those changes and got inserted into Journal tables. So when we commit we go through this Journal table and we roll these changes to the real tables, and that is how it works.
 
So when we say multiple transactions locking the same row in Oracle 23c onwards it means we are really deferring the locks until commit time and roll them in sensible order, so it not really a concurrent locks, instead this feature was a kind of deferrable locks and replay them from a journal table and apply them in correct order.

Non blocking updates - Part I

The database uses locks to ensure that, at most, one transaction is modifying a given piece of data at any given time. Basically, locks are the mechanism that allows for concurrency. Blocking occurs when one session holds a lock on a resource that another session is requesting. As a result, the requesting session will be blocked—it will hang until the holding session gives up the locked resource.
 
In this blog post series we will see how that blocking feature got changed in Oracle 23c, and what kind of business application/needs can benefit from this change.
 
From an Oracle 21c database, I create a table and executed dml like this
 
demo@PDB1> create table t( x int primary key, y number );
demo@PDB1> insert into t(x,y) values(1,1);
demo@PDB1> commit;
demo@PDB1> update t set y = y+1 where x =1;
 
1 row updated.
 
The above transaction was not committed yet.
 
demo@PDB1> select k.*, u.object_name, l.xidusn,l.xidslot,l.xidsqn
  2  from v$lock k,
  3      user_objects u , v$locked_object l
  4  where k.id1 = u.object_id (+)
  5  and u.object_id = l.object_id (+)
  6  and k.sid = l.session_id (+)
  7  and k.sid = 620 ;
 
SID TY     ID1        ID2  LMODE REQUEST   BLOCK CON_ID OBJECT_NAM  XIDUSN    XIDSLOT     XIDSQN
--- -- ------- ---------- ------ ------- ------- ------ ---------- ------- ---------- ----------
620 TM  102210          0      3       0       0      3 T                5         21       7879
620 AE     134 3319336658      4       0       0      0
620 UL       1          0      4       0       0      3
620 TX  327701       7879      6       0       0      3
 
So we ended up with a Transaction locks (TX) – a TX lock is acquired when a transaction initiates its first change. That lock is held until the transaction perform a commit/rollback, and DML Enqueue (TM) are used to ensure that the structure of a table is not altered while you are modifying its contents. we get only one TX lock per transaction, we can get as many TM locks as the objects we modify. Here, the interesting thing is that the ID1 column for the TM lock is the object ID of the DML-locked object, so it is easy to find the object on which the lock is being held.
 
AE is an edition lock, available in Oracle 11g and above and it is part of Edition based redefinition feature.
 
Now running this update from second session – updating the same row from same table - got blocked like this
 
demo@PDB1> @sid
 
INST_ SESSION_ID
----- ----------
1             14
 
demo@PDB1> update t set y = y+1 where x =1;
 
A quick query on v$lock show this
 
demo@PDB1> select * from v$lock where sid in (14,620) order by sid;
 
  SID TY      ID1        ID2   LMODE  REQUEST    BLOCK  CON_ID
----- -- -------- ---------- ------- -------- -------- -------
   14 UL        1          0       4        0        0       3
   14 AE      134 3319336658       4        0        0       0
   14 TX   327701       7879       0        6        0       3
   14 TM   102210          0       3        0        0       3
  620 AE      134 3319336658       4        0        0       0
  620 UL        1          0       4        0        0       3
  620 TM   102210          0       3        0        0       3
  620 TX   327701       7879       6        0        1       3
 
8 rows selected.
 
We can see that the second session (session id = 14) request the Transaction lock, which was held by first session (session id 620) and that is blocking the other session.
 
However, running the same script to Oracle database 23c goes like this
 
demo@FREEPDB1> /* From session-1 did this */
demo@FREEPDB1> @sid
 
INST_ SESSION_ID
----- ----------
1            295
 
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
1               1
 
demo@FREEPDB1> update t set y = y+1 where x =1;
 
1 row updated.
 
demo@FREEPDB1> select * from v$lock where sid = 295 ;
 
   SID TY        ID1        ID2      LMODE    REQUEST   BLOCK     CON_ID
------ -- ---------- ---------- ---------- ---------- ------- ----------
   295 TX     262163       1360          6          0       0          3
   295 TM     117538          0          3          0       0          3
   295 TM     117537          0          3          0       0          3
   295 AE        138 1423580300          4          0       0          0
   295 UL          1          0          4          0       0          3
  
 
demo@FREEPDB1> /* From session-2 did this */
demo@FREEPDB1> @sid
 
INST_ SESSION_ID
----- ----------
1             60
 
demo@FREEPDB1> update t set y = y+1 where x =1;
 
1 row updated.
 
demo@FREEPDB1>
demo@FREEPDB1> select * from v$lock where sid = 60;
 
 SID TY        ID1        ID2  LMODE    REQUEST  BLOCK CON_ID
---- -- ---------- ---------- ------ ---------- ------ ------
  60 TX     524317       1560      6          0      0      3
  60 TM     117538          0      3          0      0      3
  60 TM     117537          0      3          0      0      3
  60 AE        138 1423580300      4          0      0      0
  60 UL          1          0      4          0      0      3 
 
The update from the second session was not blocked – even though it was updating the same row as it got updated from the first session – how this was even remotely possible? This seems to break the whole concept of database locking.
 
Now let us do commit from second session and go back to first session
 
demo@FREEPDB1>  --
demo@FREEPDB1>  -- Session-1
demo@FREEPDB1>  --
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
         1          2
 
Even though we have an ongoing lock in first session, this session can see now the second session (session id = 60) committed changes – which is really good – if that doesn’t happen/notified then this session will still assume the value of y = 1 then the expression y = y+1  upon execution will set y=2, and results into a lost update issues (that is changes from second session, will get overwritten)
 
Now when we commit from first session, it goes like this
 
demo@FREEPDB1>  --
demo@FREEPDB1>  -- Session-1
demo@FREEPDB1>  --
demo@FREEPDB1> commit;
 
Commit complete.
 
demo@FREEPDB1> select * from t;
 
         X          Y
---------- ----------
         1          3
 
demo@FREEPDB1>
 
and it worked well, even though the same row/value got updated from two different session – which was impossible with prior version of Oracle database.
 
So what is happening with Oracle database 23c and how the database has become efficient with handling these non-blocking updates? we will see in the next blogpost.

Schema level privilege grants

Many modern applications separate the data owning schema from application service / run-time account used to access the data. This provides for separation of duty and supports the least privileged model and lowers the risk if the accessing account is compromised. But there is only one difficulty, how to manage the list of privileges for that run time account. Further as the application schema changes over the time, how do we keep up the list current? prior to Oracle 23c, developers had only two choices
 

• Grant individual privilege on each table and view in the application schema.
• Grant “ANY” privileges.

 
The first choice is inconvenient, because we need to identity every single table / view and do individual grant to every run time or service user. We can develop a script, but it is inconvenient. It is also a sub optimal way to deal with application schema changes such as adding new tables or views, because we must now remember to make corresponding privilege grants.
 
The second choice of granting ANY privilege while convenient is sub-optimal from security angle, because with that grant you gave the user the ability to select from any table in the database. If that user account is compromised, then your entire database is exposed.
 
To address these problems, Oracle 23c introduced a new SCHEMA level grants – that is if you say GRANT SELECT ANY TABLE ON SCHEMA SCOTT TO DEMO, then the user DEMO could see all tables / views in SCOTT schema, if any new table got created in SCOTT schema then DEMO user instantly has access to that new table. No additional management is needed, and we can continue to support the least privilege security model with appropriate separation of duties.
 
First let’s create users and few tables to test.
 
demo@FREEPDB1> grant create session,create table, unlimited tablespace
  2  to app_schema
  3  identified by "app_schema";
 
Grant succeeded.
 
demo@FREEPDB1> grant create session
  2  to app_user
  3  identified by "app_user";
 
Grant succeeded.
 
demo@FREEPDB1> conn app_schema/app_schema@freepdb1
Connected.
app_schema@FREEPDB1> create table t1( name varchar2(20) );
 
Table created.
 
app_schema@FREEPDB1> insert  into t1 values ('Tom'),('Kyte');
 
2 rows created.
 
app_schema@FREEPDB1>
app_schema@FREEPDB1> create table t2( city varchar2(20) );
 
Table created.
 
app_schema@FREEPDB1> insert  into t2 values ('Minnesota'),('Chicago');
 
2 rows created.
 
app_schema@FREEPDB1> commit;
 
Commit complete.
 
 
Now connect as APP_USER and verify if you can see the tables in APP_SCHEMA user account.
 
app_user@FREEPDB1> select * from session_schema_privs;
 
no rows selected
 
app_user@FREEPDB1> select * from app_schema.t1;
select * from app_schema.t1
                         *
ERROR at line 1:
ORA-00942: table or view does not exist
 
 
app_user@FREEPDB1> select * from app_schema.t2;
select * from app_schema.t2
                         *
ERROR at line 1:
ORA-00942: table or view does not exist
 
APP_USER could not select from APP_SCHEMA tables, because the user has no privileges on the objects / schema. Now lets grant schema privilege to APP_USER, so that APP_USER will get required permissions to view the data in APP_SCHEMA tables.
 
app_user@FREEPDB1> conn app_schema/app_schema@freepdb1
Connected.
app_schema@FREEPDB1> grant select any table on schema app_schema to app_user;
 
Grant succeeded.
 
Post the schema privilege grants the APP_USER can now be able to see all the data in APP_SCHEMA.
 
app_schema@FREEPDB1> conn app_user/app_user@freepdb1
Connected.
app_user@FREEPDB1> select * from app_schema.t1;
 
NAME
------------------------------
Tom
Kyte
 
app_user@FREEPDB1> select * from app_schema.t2;
 
CITY
--------------------
Minnesota
Chicago
 
app_user@FREEPDB1> select * from session_schema_privs;
 
PRIVILEGE                                SCHEMA
---------------------------------------- ---------------
SELECT ANY TABLE                         APP_SCHEMA
 
Here comes the good part, when APP_SCHEMA add a new table APP_USER should automatically have access to new tables.
 
app_user@FREEPDB1> conn app_schema/app_schema@freepdb1
Connected.
app_schema@FREEPDB1> create table t3( country varchar2(20) );
 
Table created.
 
app_schema@FREEPDB1> insert  into t3 values ('USA'),('UK');
 
2 rows created.
 
app_schema@FREEPDB1> commit;
 
Commit complete.
 
app_schema@FREEPDB1> conn app_user/app_user@freepdb1
Connected.
app_user@FREEPDB1> select * from app_schema.t3;
 
COUNTRY
--------------------
USA
UK
 
app_user@FREEPDB1>
 
As you have seen, the new schema-level privileges make it easy to grant access to ALL of an applications data and objects, and as new objects are added to the schema there is no need to update grants for those new objects.
 
 
 

JSON Schema - Part IV

Having discussed enough about the JSON Schema in the previous blog series ( Part I, Part II, Part III ), Another common use of JSON Schema is used to describe an object, lets move on to an example of using it. Here is my EMP table that got very conventional datatypes and no JSON datatypes.
 
demo@FREEPDB1> desc emp
 Name          Null?    Type
 ------------- -------- -------------
 EMPNO         NOT NULL NUMBER(4)
 ENAME                  VARCHAR2(10)
 JOB                    VARCHAR2(9)
 MGR                    NUMBER(4)
 HIREDATE               DATE
 SAL                    NUMBER(7,2)
 COMM                   NUMBER(7,2)
 DEPTNO        NOT NULL NUMBER(2)
 
To get the JSON schema for the above table, all we need to do is make use of “describe” method in DBMS_JSON_SCHEMA package.
 
demo@FREEPDB1> select dbms_json_schema.describe('EMP') x;
 
X
--------------------------------------------------------------------------------
{
  "title" : "EMP",
  "dbObject" : "DEMO.EMP",
  "type" : "object",
  "dbObjectType" : "table",
  "properties" :
  {
    "EMPNO" :
    {
      "extendedType" : "integer",
      "sqlPrecision" : 4
    },
    "ENAME" :
    {
      "extendedType" :
      [
        "null",
        "string"
      ],
      "maxLength" : 10
    },
    "JOB" :
    {
      "extendedType" :
      [
        "null",
        "string"
      ],
      "maxLength" : 9
    },
    "MGR" :
    {
      "extendedType" :
      [
        "null",
        "integer"
      ],
      "sqlPrecision" : 4
    },
    "HIREDATE" :
    {
      "extendedType" :
      [
        "null",
        "date"
      ]
    },
    "SAL" :
    {
      "extendedType" :
      [
        "null",
        "number"
      ],
      "sqlPrecision" : 7,
      "sqlScale" : 2
    },
    "COMM" :
    {
      "extendedType" :
      [
        "null",
        "number"
      ],
      "sqlPrecision" : 7,
      "sqlScale" : 2
    },
    "DEPTNO" :
    {
      "extendedType" : "integer",
      "sqlPrecision" : 2
    }
  },
  "required" :
  [
    "EMPNO",
    "DEPTNO"
  ],
  "dbPrimaryKey" :
  [
    "EMPNO"
  ],
  "dbForeignKey" :
  [
    {
      "DEPTNO" :
      {
        "dbObject" : "DEMO.DEPT",
        "dbColumn" : "DEPTNO"
      }
    }
  ]
}
 
 
The above JSON Schema shows the object EMP is of database object type TABLE belonging to the schema DEMO, from a JSON Schema perspective it is an object, but from the relational database object perspective it is table, the dbObjectType can be different based on object type we describe, it can be a type/table/view/dualityview etc.
 
The properties in the JSON Schema maps to the column in the table we described. The EMPNO is actually an integer datatype, where as ENAME is string datatype but it can also be null, similarly all the required columns of the table (column(s) having NOT NULL constraint) are mapped to the required properties of the JSON Schema, similar to that all the key columns in the tables are mapped to dbPrimaryKey and dbForeignKey properties of the JSON Schema
 
Where does this JSON Schema really help us? It is really helpful to validate (both the structure and data typing of JSON documents) the incoming JSON documents for persistence and finally when we generate the JSON for the table EMP and if we need an API description of how the data looks like then this JSON Schema really helps us.
  
Similarly, we can make use of plsql object type for JSON Schema representation.
 
demo@FREEPDB1> create or replace type address_type
  2  as object( street varchar2(30),
  3     city varchar2(20),
  4     state varchar2(2),
  5     postal_code varchar2(6) );
  6  /
 
Type created.
 
The JSON Schema for the plsql object type is possible using.
 
demo@FREEPDB1> select json_serialize( dbms_json_schema.describe('ADDRESS_TYPE') pretty ) ;
 
JSON_SERIALIZE(DBMS_JSON_SCHEMA.DESCRIBE('ADDRESS_TYPE')PRETTY)
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{
  "title" : "ADDRESS_TYPE",
  "dbObject" : "DEMO.ADDRESS_TYPE",
  "type" : "object",
  "dbObjectType" : "type",
  "properties" :
  {
    "STREET" :
    {
      "extendedType" :
      [
        "null",
        "string"
      ],
      "maxLength" : 30
    },
    "CITY" :
    {
      "extendedType" :
      [
        "null",
        "string"
      ],
      "maxLength" : 20
    },
    "STATE" :
    {
      "extendedType" :
      [
        "null",
        "string"
      ],
      "maxLength" : 2
    },
    "POSTAL_CODE" :
    {
      "extendedType" :
      [
        "null",
        "string"
      ],
      "maxLength" : 6
    }
  }
}
 
 
The attributes of the object type maps to properties of JSON Schema document. So we can easily describe types/tables/view/dualityviews to get the JSON Schema representation of it.