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.
Mark Hoxey 