If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: LOCK of SELECT FOR UPDATE Statement

Hi,

I create a query user QUERY, and grant CONNECT, SELECT ANY TABLE role/privileges to it, so it can query the data of PRDT user, but cann't modify. I set the O7_DICTIONARY_ACCESSIBILITY=FALSE.

I am very surprise to find that the QUERY user can lock the PRDT data by SELECT ... FOR UPDATE statement, but it cannot submit the modification, so QUERY did not COMMIT/ROLLBACK the SELECT FOR UPDATE statement, and the lock is not released, other users cannot update the locked rows.

Does anybody know how to prevent this SELECT FOR UPDATE statement from being executed by users without INSERT/UPDATE/DELETE privileges?

An update statement acquires a special row-level lock called a "row-
exclusive" lock, which means that for the period of time the update
statement is executing, no other user in the database can view or
change the data in the row. The select for update statement, acquires a more lenient lock called the "share row" lock. This lock means that for the period of time the update statement is changing the data in the rows of the table, no other user may change that row, but users may look at the data in the row as it changes.

An update statement acquires a special row-level lock called a "row-
exclusive" lock, which means that for the period of time the update
statement is executing, no other user in the database can view or
change the data in the row. The select for update statement, acquires a more lenient lock called the "share row" lock. This lock means that for the period of time the update statement is changing the data in the rows of the table, no other user may change that row, but users may look at the data in the row as it changes.

Hi,

Thanks for your reply. But I think you make a mistake. An update statement acquires a special row-level lock called a "row-exclusive" lock, no other user in the database can change the data in the row before the transaction COMMIT or ROLLBACK, BUT other user can VIEW the data, BEFORE or AFTER the complete of the transaction.

So, you can't, I think, prevent a user with SELECT ANY TABLE to make a select for update and block other users.
I've also tried to grant the select on the table only:

SQL> revoke select any table from test;

Revoke succeeded.

SQL> conn ckmadmin/ckmadmin@oracle9i
Connected.

SQL> grant select on test to test;

Grant succeeded.

But it's exactly the same.

I've also searched in comp.databases.oracle.server and found that others have come across this issue, but no solution.

So I searched Metalink and found, in a forum, some more powerful way to lock a table:

-----------------------------
Hi. I don't know if I would call it a 'feature,' but it is not a bug and is expected behavior. Also refer to the supporting information for the GRANT command in the Oracle8i SQL Reference, Table 11-4 Object Privileges and the Operations They Authorize:

"The following table privileges authorize operations on a table. Any one of following object privileges allows the grantee to lock the table in any lock mode with the LOCK TABLE statement."

...
SELECT
...

There is an enhancement request filed (721956) to limit this capability for the SELECT statement.
------------------------------

And voila':

SQL> lock table ckmadmin.test in exclusive mode;

Table(s) Locked.

I would call this a bug ... I don't know if that enhancement request found its way to the releases > 9.0.1.

I think you need to consider the read consistency which Oracle (attempts to) deliver.

At first sight it doesn't make sense for a "select only" user to cause blocking - except when you consider that the table on which the select took place will usually be part of a FK constraint/relationship.

It *is* difficult to get one's head around it, unless you consider the difference between single select statements and transactions. In the case of transactions, read consistency should be maintained throughout the course of the entire transaction.

Where should things stop? Should Oracle continue using rollback until all possible transactions have commited? Or should it enforce rollback usage beyond a different transactions commit point until all selects (within other transactions) have been satisfied?

It's a wierd area and not one which I claim to fully understand just yet but I hope that helps!

Ur answer is, An update statement acquires a special row-level lock called a "row-exclusive" lock, no other user in the database can change the data in the row before the transaction COMMIT or ROLLBACK, BUT other user can VIEW the data, BEFORE or AFTER the complete of the transaction.

When there is an EXCLUSIVE LOCK , No other lock can be held along with it (this includes both read & write locks). This is to ensure DATA consistency.