SELECT privilege locking

You would only need to have been working with Oracle for a short while to hear the mantra “readers don’t block writers”. This has been a huge feature of Oracle for a long while now and sets it apart from many other database products. If you intend to do any application development in Oracle then it’s very important that you understand the basics of locking and blocking.

So, what object privilege is required to obtain a row lock on a table? If you were to say the manipulation commands of UPDATE and DELETE then you’d be correct of course. What about SELECT privilege? Given what you know about readers not blocking writers due to SELECT statements not taking out any locks you could be forgiven for thinking that the SELECT privilege does not permit the locking of table rows… because it does.

PL/SQL developers will be familiar with using SELECT FOR UPDATE statements. SELECT FOR UPDATE permits us to read data and flag our intention to update it later in our process. The lock prevents other sessions from modifying the data between when we read it to when we update it.

For example, consider a typical bank account withdrawal. The process might be to read the current account balance, confirm sufficient funds exist for the withdrawal, create the necessary withdrawal transaction and update the account balance. The process will probably start off with a SELECT FOR UPDATE on the account to obtain the balance and prevent another transaction from updating the balance whilst our withdrawal is being performed.

So, back to our table privileges and row locking. In order to run a SELECT FOR UPDATE command the only privilege required on the table is the SELECT privilege. A quick demonstration will illustrate this. First we need two user accounts, named DATA_OWNER and DATA_READER for this example:

CREATE USER data_owner
   IDENTIFIED BY data_owner
   DEFAULT TABLESPACE users
   QUOTA UNLIMITED ON users
/

CREATE USER data_reader
   IDENTIFIED BY data_reader
/

GRANT CREATE SESSION TO data_owner
/
GRANT CREATE TABLE TO data_owner
/
GRANT CREATE SESSION TO data_reader
/

Within the DATA_OWNER account we create a simple table with just 1 row and grant SELECT on the table to DATA_READER:

CREATE TABLE lock_test
   (id NUMBER(10))
/

INSERT INTO lock_test
   VALUES (1)
/

COMMIT
/

GRANT SELECT ON lock_test TO data_reader
/

Now, from the DATA_READER account, we run:

DECLARE
   CURSOR cur
   IS
   SELECT *
   FROM   data_owner.lock_test
   FOR UPDATE;
BEGIN
   OPEN cur;
END;
/

When we run the above within SQL*Plus we get back the typical message of:

PL/SQL procedure successfully completed.

which means that our DATA_READER session has obtained locks on the rows of the LOCK_TEST table. If we were to start a session in DATA_OWNER and attempt to update the row in LOCK_TEST using:

UPDATE lock_test
SET    id = 2
WHERE  id = 1
/

we would find our session blocked. Only when our DATA_READER session ends its transaction by issuing a COMMIT or ROLLBACK will its locks on the table be released and our DATA_OWNER update statement completes.

Further to the row level locking that SELECT FOR UPDATE provides, it’s also possible to issue a LOCK TABLE command when SELECT privilege has been granted, e.g.:

LOCK TABLE data_owner.lock_test IN EXCLUSIVE MODE

So, we’ve seen that only having SELECT privilege on a table permits us to lock rows. What are the consequences of this? One of the most obvious impacts is that it permits a some sort of denial of service attack if an unauthorised person were to gain access to the database that had SELECT access on critical data. This might sound far-fetched but many organisations I have worked at have permitted staff liberal read access to production databases via standard “read only” type accounts. The passwords for these accounts have been widely known throughout the organisation. Realistically though, such an attack would be short lived as DBAs are typically watchful and would promptly kill any session attempting such an attack.

There are however other means of issuing a SELECT FOR UPDATE command and not realising that it’s been done. Users of Quest TOAD for Oracle might not realise it but editing table data from within the data grid in the Schema Browser results in TOAD issuing a SELECT FOR UPDATE on each row as it is edited. Going back to our DATA_OWNER / DATA_READER example, if we were to browse the data and edit it in place, as shown below:

we can then see the locks that have been taken by running the following from an SQL*Plus session:

COLUMN session_id      FORMAT 99999
COLUMN lock_type       FORMAT A15
COLUMN lock_id1        FORMAT A10
COLUMN lock_id2        FORMAT A10
COLUMN blocking_others FORMAT A20
COLUMN mode_held       FORMAT A10

SELECT session_id
,      lock_type
,      mode_held
,      lock_id1
,      lock_id2
,      blocking_others
FROM   dba_locks
WHERE  session_id IN (SELECT sid
                      FROM   v$session
                      WHERE  username = 'DATA_READER')
/

In my test this produced the following listing:

SESSION_ID LOCK_TYPE       MODE_HELD  LOCK_ID1   LOCK_ID2   BLOCKING_OTHERS
---------- --------------- ---------- ---------- ---------- --------------------
         8 AE              Share      100        0          Not Blocking
       133 AE              Share      100        0          Not Blocking
       197 AE              Share      100        0          Not Blocking
       197 Transaction     Exclusive  131075     1413       Not Blocking
       197 DML             Row-X (SX) 77857      0          Not Blocking

The last two rows in the above listing show that Oracle has started a transaction and that a row level lock has been taken. These locks are currently not blocking anything. If we were to run the DATA_OWNER update statement from our previous example it would be blocked and a rerun the above query would result in:

SESSION_ID LOCK_TYPE       MODE_HELD  LOCK_ID1   LOCK_ID2   BLOCKING_OTHERS
---------- --------------- ---------- ---------- ---------- --------------------
         8 AE              Share      100        0          Not Blocking
       133 AE              Share      100        0          Not Blocking
       197 AE              Share      100        0          Not Blocking
       197 Transaction     Exclusive  131075     1413       Blocking
       197 DML             Row-X (SX) 77857      0          Not Blocking

So, now we see that our TOAD session is blocking the other session update that we’re trying to perform against our table. If we hit ROLLBACK in TOAD then the locks go away.

So, Quest TOAD performs a SELECT FOR UPDATE on the rows that a user edits within the table data grid. This is a pessimistic locking mechanism. The SELECT FOR UPDATE is needed to ensure that from the point in time the user indicated they wanted to edit the row to the point that TOAD issues the UPDATE itself that the row is not modified by another session.

So, can the SELECT FOR UPDATE be avoided and yet avoid a lost update scenario? Repeating the test using Oracle SQL Developer we can see that it takes a different approach. Editing the column value in the data grid shows up as follows:

All seems to be okay until a COMMIT is issued, then we get the following error message:

UPDATE "DATA_OWNER"."LOCK_TEST" SET ID = '2' WHERE ROWID = 'AAATAhAAEAAAADnAAA' AND ORA_ROWSCN = '2538737'
ORA-01031: insufficient privileges

We can see that our update has failed due to insufficient privileges, which is exactly what we would expect. Looking at the update itself though we can see that SQL Developer has accessed the row via the ROWID and also added in a clause that checks the ORA_ROWSCN. So, the strategy here is that if the row has been modified since SQL Developer read the details into the data grid then the UPDATE would fail as the row SCN would have been changed by any subsequent update to the row we’re accessing. This is an optimistic locking strategy.

As an aside, this is the error that SQL Developer would report if another session had modified the row between when it was read to when SQL Developer attempted to change it:

UPDATE "DATA_OWNER"."LOCK_TEST" SET ID = '2' WHERE ROWID = 'AAATAhAAEAAAADnAAA' AND ORA_ROWSCN = '2538737'

One error saving changes to table "DATA_OWNER"."LOCK_TEST":
Row 1: Data got committed in another/same session, cannot update row.

So, in summary, the only privilege required to lock table rows (or the table itself, via LOCK TABLE) is SELECT privilege. Before granting wholesale SELECT access to your production database to everyone in your organisation, you might like to consider the impact of a rogue lock on the system. While it takes a little knowledge to know how to deliberately obtain a lock it might be that your database client applications do this as part of editing operations unbeknownst to the user.

UPDATE 30 Sept 2015: In Oracle 12.1.0.2 provides READ privilege that grants a user the ability to read table data without being able to to lock it.

PL/SQL Function-based Indexes

Creating indexes on expressions using in-built SQL functions can be a very useful approach for a solving certain problems. For example, an index can be created to enforce uniqueness on a subset of rows or for solving an important, unanticipated query that contains complex logic.

When creating function-based indexes we are not limited to using the in-built SQL functions but can use PL/SQL functions too, which is a feature that I prefer to avoid and consequently forget about. This post looks at some of the basics associated with PL/SQL function-based indexes, noting a couple of concerns you need to be aware of.

To start with, let’s create a table for the following examples:

CREATE TABLE plsql_fbi
   (id     NUMBER(10)   NOT NULL
   ,cat    VARCHAR2(10) NOT NULL)
/

ALTER TABLE plsql_fbi
   ADD CONSTRAINT plsql_fbi_chk1
   CHECK (UPPER(cat) = cat)
/   

INSERT INTO plsql_fbi
   (id, cat)
SELECT ROWNUM
,      dbms_random.string('U',10)
FROM   dual
CONNECT BY ROWNUM <= 100
/

EXEC dbms_stats.gather_table_stats ('','plsql_fbi')

So, we now have a table named PLSQL_FBI, which contains 2 columns and 100 rows. The CAT column is character-based and each entry is randomly generated 10 upper case character strings. Now assume we have a requirement to filter the data in the table according to the last character of the CAT column, i.e.:

SELECT *
FROM   plsql_fbi
WHERE  SUBSTR(cat,-1,1) = 'C'
ORDER  BY
       cat

We could certainly create a function-based index without involving a PL/SQL function, as shown below:

CREATE INDEX plsql_fbi_ix1
ON plsql_fbi (SUBSTR(cat,-1,1))

However, for this exercise we will adopt a “developer mentality” and instead choose to:

CREATE OR REPLACE FUNCTION plsql_fbi_cat (p_cat IN plsql_fbi.cat%TYPE)
RETURN VARCHAR2
DETERMINISTIC
AS
BEGIN
   RETURN SUBSTR(p_cat,1,1);
END plsql_fbi_cat;
/

CREATE INDEX plsql_fbi_ix1
ON plsql_fbi (plsql_fbi_cat(cat))
/

We’ve now created a PL/SQL function that accepts a string and returns the last character of that string. We then create an index on our table using that function. A key thing to note in the definition of the function above is the DETERMINISTIC keyword, which basically means for any given input the function output will yeild the same result. Many functions are deterministic but if DETERMINISTIC isn’t specified then Oracle will prevent you from using the function for index creation and return the following error instead:

SQL>CREATE INDEX plsql_fbi_ix1
  2  ON plsql_fbi (plsql_fbi_cat(cat))
  3  /
ON plsql_fbi (plsql_fbi_cat(cat))
              *
ERROR at line 2:
ORA-30553: The function is not deterministic

Certainly this what will happen in Oracle 11g release 2. I seem to recall that in earlier versions of Oracle the DETERMINISTIC keyword was not required but I may be wrong. What is important to note is that even though the function has been declared as DETERMINISTIC Oracle does not actually verify that the function is in fact deterministic. This means that the following functoin which is decidedly non-deterministic will be assumed to be deterministic and could be used to create a function-based index:

CREATE OR REPLACE FUNCTION plsql_fbi_cat (p_cat IN plsql_fbi.cat%TYPE)
RETURN VARCHAR2
DETERMINISTIC
AS
BEGIN
   RETURN dbms_random.string('U',1);
END plsql_fbi_cat;

Okay, back to our example, we now have our PL/SQL function and an index on our table that utilises that function. A simple test, using autotrace, will show us whether Oracle will use it or not:

SET AUTOTRACE ON

SELECT *
FROM   plsql_fbi
WHERE  plsql_fbi_cat(cat) = 'C'
ORDER  BY
       cat
/

SET AUTOTRACE OFF

which gives us:

SQL>SELECT *
  2  FROM   plsql_fbi
  3  WHERE  plsql_fbi_cat(cat) = 'C'
  4  ORDER  BY
  5         cat
  6  /

        ID CAT
---------- ----------
        18 FSAHXSSXCC
        68 IEMPRBPTCC
        56 JGDDOSKQYC


Execution Plan
----------------------------------------------------------
Plan hash value: 3845859686

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |     1 |    14 |     2  (50)| 00:00:01 |
|   1 |  SORT ORDER BY               |               |     1 |    14 |     2  (50)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| PLSQL_FBI     |     1 |    14 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | PLSQL_FBI_IX1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("DEVELOPER"."PLSQL_FBI_CAT"("CAT")='C')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        680  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

From the above results, we have 3 rows in that table that end in the character C (since the data was randomly generated, you will get different results). The execution plan shows that Oracle chose to use the index to select the relevant rows, which is exactly what we would hope for.

So far so good. We’ve eliminated what could be an expensive calculation against our data and stored the calculated values in our index so we can quickly look up data based on that calculation efficiently. Any hidden dangers with doing this?

Well, what happens if our function changes, e.g. we change the calculation so that we extract the first character from the input string instead of the last:

CREATE OR REPLACE FUNCTION plsql_fbi_cat (p_cat IN plsql_fbi.cat%TYPE)
RETURN VARCHAR2
DETERMINISTIC
AS
BEGIN
   RETURN SUBSTR(p_cat,1,1);
END plsql_fbi_cat;

Now we rerun our query and we get the following result:

SQL>SELECT *
  2  FROM   plsql_fbi
  3  WHERE  plsql_fbi_cat(cat) = 'C'
  4  ORDER  BY
  5         cat
  6  /

        ID CAT
---------- ----------
        18 FSAHXSSXCC
        68 IEMPRBPTCC
        56 JGDDOSKQYC

Clearly this result is incorrect. We’ve changed our function but the query is still returning the same results as the pre-modified function. When we recompiled our PL/SQL function our index that is based on that function was not updated. This might come as a surprise to some but that’s just the way it is. In order to get the index to reflect the revised calculation we need to rebuild the index:

SQL>ALTER INDEX plsql_fbi_ix1 REBUILD
  2  /

Index altered.

SQL>SELECT *
  2  FROM   plsql_fbi
  3  WHERE  plsql_fbi_cat(cat) = 'C'
  4  ORDER  BY
  5         cat
  6  /

        ID CAT
---------- ----------
        19 CMBJCOCCPD
        62 CRUDSSFRRO

This time we get the results that we would expect from the revised function. What would happen if we forget to rebuild our index and data in the table changed:

SQL>INSERT INTO plsql_fbi
  2  VALUES (200,'ACDC')
  3  /

1 row created.

SQL>
SQL>INSERT INTO plsql_fbi
  2  VALUES (201,'CDCA')
  3  /

1 row created.

SQL>COMMIT
  2  /

Commit complete.

SQL>SELECT *
  2  FROM   plsql_fbi
  3  WHERE  plsql_fbi_cat(cat) = 'C'
  4  ORDER  BY
  5         cat
  6  /

        ID CAT
---------- ----------
       201 CDCA
        18 FSAHXSSXCC
        68 IEMPRBPTCC
        56 JGDDOSKQYC

Whoa, that’s completely inconsistent! What we now have are entries in the index where some have been calculated using the initial version of the PL/SQL function and some with the modified version. So, the recommendation here would be that if a function used by an index is modified then the index should immediately be rebuilt. This might sound quite simple and obvious but in a complex environment where function calls might be nested one within another then it can be hard to work out the precise impact of any single code change. My recommendation would be that if you decide to use a PL/SQL function for an index then keep the function logic self-contained and don’t start calling other PL/SQL functions. Generating incorrect results from a query is probably the worst type of error that can occur within a database application so it is best to avoid scenarios that can lead to this.

What happens if we modify the function and it ends up invalid through some typo?

SQL>CREATE OR REPLACE FUNCTION plsql_fbi_cat (p_cat IN plsql_fbi.cat%TYPE)
  2  RETURN VARCHAR2
  3  DETERMINISTIC
  4  AS
  5  BEGIN
  6     RETURN xSUBSTR(p_cat,-1,1);
  7  END plsql_fbi_cat;
  8  /

Warning: Function created with compilation errors.

SQL>SELECT *
  2  FROM   plsql_fbi
  3  WHERE  plsql_fbi_cat(cat) = 'C'
  4  ORDER  BY
  5         cat
  6  /
FROM   plsql_fbi
   *
ERROR at line 2:
ORA-06575: Package or function PLSQL_FBI_CAT is in an invalid state

Again, probably no surprises here. If the function goes invalid then we get an error when we try to run our SELECT query. Things change a bit though if our function exists within a package:

SQL>CREATE OR REPLACE PACKAGE pkg_plsql_fbi
  2  AS
  3
  4  FUNCTION cat (p_cat IN plsql_fbi.cat%TYPE)
  5  RETURN VARCHAR2
  6  DETERMINISTIC;
  7
  8  END pkg_plsql_fbi;
  9  /

Package created.

SQL>
SQL>CREATE OR REPLACE PACKAGE BODY pkg_plsql_fbi
  2  AS
  3
  4  FUNCTION cat (p_cat IN plsql_fbi.cat%TYPE)
  5  RETURN VARCHAR2
  6  DETERMINISTIC
  7  AS
  8  BEGIN
  9     RETURN SUBSTR(p_cat,-1,1);
 10  END cat;
 11
 12  END pkg_plsql_fbi;
 13  /

Package body created.

SQL>
SQL>DROP INDEX plsql_fbi_ix1
  2  /

Index dropped.

SQL>
SQL>CREATE INDEX plsql_fbi_ix1
  2     ON plsql_fbi (pkg_plsql_fbi.cat(cat))
  3  /

Index created.

SQL>
SQL>SELECT *
  2  FROM   plsql_fbi
  3  WHERE  pkg_plsql_fbi.cat(cat) = 'C'
  4  ORDER  BY
  5         cat
  6  /

        ID CAT
---------- ----------
       200 ACDC
        18 FSAHXSSXCC
        68 IEMPRBPTCC
        56 JGDDOSKQYC

SQL>
SQL>DROP PACKAGE BODY pkg_plsql_fbi
  2  /

Package body dropped.

SQL>
SQL>SELECT *
  2  FROM   plsql_fbi
  3  WHERE  pkg_plsql_fbi.cat(cat) = 'C'
  4  ORDER  BY
  5         cat
  6  /

        ID CAT
---------- ----------
       200 ACDC
        18 FSAHXSSXCC
        68 IEMPRBPTCC
        56 JGDDOSKQYC

So long as the package specification remain valid then we can continue to run SELECT queries. This makes sense as Oracle is not actually executing the query but using the pre-calculated values that exist in the index. Naturally, if we tell Oracle to ignore our index and force it to execute the function then we get:

SQL>SELECT /*+ FULL (plsql_fbi) */
  2         *
  3  FROM   plsql_fbi
  4  WHERE  pkg_plsql_fbi.cat(cat) = 'C'
  5  ORDER  BY
  6         cat
  7  /
SELECT /*+ FULL (plsql_fbi) */
*
ERROR at line 1:
ORA-04067: not executed, package body "DEVELOPER.PKG_PLSQL_FBI" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "DEVELOPER.PKG_PLSQL_FBI"
ORA-06512: at line 1

Likewise, if we attempt to modify the data we will generate an error as Oracle has to actually execute the function in order to update the index:

SQL>INSERT INTO plsql_fbi
  2  VALUES (203,'ABCD')
  3  /
INSERT INTO plsql_fbi
*
ERROR at line 1:
ORA-04067: not executed, package body "DEVELOPER.PKG_PLSQL_FBI" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "DEVELOPER.PKG_PLSQL_FBI"
ORA-06512: at line 1

So, when using PL/SQL function-based indexes the things to keep in mind are:

• Ensure the function you use is deterministic. Despite the DETERMINISTIC keyword it is up to you to ensure this is true.
• If you modify the function used by the function-based index then you will need to rebuild the index. I would go sofar as to say you should be disabling the index prior to making any change to the function.