In MS SQL Server nolock can be used for the purpose.
Why can't we use it in Oracle and plsql?
Best Answer
SQL Server normally uses a different locking strategy to Oracle. The default strategy used by SQL Server means that selecting rows causes read locks (on rows, pages or the whole table) to be placed on them*. Hence the NOLOCK is sometimes a useful clause - although the general advice is to never use it, as it changes the semantics of isolation levels and may cause inconsistent results in the queries' output.
* (Note: that is the default. If SNAPSHOT isolation is chosen, the behaviour is different and readers do not block writers.)
In Oracle a read process will never block a write process. The following is an excerpt from 'Oracle Database Concepts 11g Release 2'. I encourage you to take a look if you are interested in how this is handled by Oracle.
The database maintains several different types of locks, depending on
the operation that acquired the lock. In general, the database uses
two types of locks: exclusive locks and share locks. Only one
exclusive lock can be obtained on a resource such as a row or a table,
but many share locks can be obtained on a single resource.
Locks affect the interaction of readers and writers. A reader is a
query of a resource, whereas a writer is a statement modifying a
resource. The following rules summarize the locking behavior of Oracle
Database for readers and writers:
•A row is locked only when modified by a writer.
When a statement updates one row, the transaction acquires a lock for
this row only. By locking table data at the row level, the database
minimizes contention for the same data. Under normal circumstances
1 the database does not escalate a row lock to the block or table
level.
•A writer of a row blocks a concurrent writer of the same row.
If one transaction is modifying a row, then a row lock prevents a
different transaction from modifying the same row simultaneously.
•A reader never blocks a writer.
Because a reader of a row does not lock it, a writer can modify this
row. The only exception is a SELECT ... FOR UPDATE statement, which is
a special type of SELECT statement that does lock the row that it is
reading.
•A writer never blocks a reader.
When a row is being changed by a writer, the database uses undo
data to provide readers with a consistent view of the row.
The table hint in the query takes precedence, as I would have expected.
Here's my test script:
CREATE TABLE t1 (a int);
INSERT INTO t1(a) SELECT TOP 1000 message_id FROM sys.messages;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM t1 WITH(NOLOCK); /* Try it with and without the hint */
EXEC sp_lock;
ROLLBACK;
With the NOLOCK hint included, no locks are taken by the SELECT statement.
In other words, setting the isolation level on the transaction makes that isolation level the default within the transaction, but that default can still be overridden by table hints, as I demonstrated.
I see no COMMIT up there in your code.
By default SQL*Plus autocommit is off.
Data changes are not visible to other sessions until they are not commited.
Best Answer
SQL Server normally uses a different locking strategy to Oracle. The default strategy used by SQL Server means that selecting rows causes read locks (on rows, pages or the whole table) to be placed on them*. Hence the
NOLOCK
is sometimes a useful clause - although the general advice is to never use it, as it changes the semantics of isolation levels and may cause inconsistent results in the queries' output.* (Note: that is the default. If
SNAPSHOT
isolation is chosen, the behaviour is different and readers do not block writers.)In Oracle a read process will never block a write process. The following is an excerpt from 'Oracle Database Concepts 11g Release 2'. I encourage you to take a look if you are interested in how this is handled by Oracle.
Data Concurrency and Consistency