Understanding LOCKING In Database

index_compression

CONCURRENT access is accessing same data by multiple users at the same time.

SHARED lock is acquired when user tries to read data.

EXCLUSIVE lock is acquired when user tries to modify data.

Data concurrency means that many users can access data at the same time.

Data consistency means that each user sees a consistent view of the data, including visible changes made by the user’s own transactions and transactions of other users.

Locking Types :-

Pessimistic Locking :-

The first user who accesses the data with the purpose of updating locks the data until completing the update.Other users can read the data but cannot write.

Disadvantage Of Pessimistic Locking :-

• The Lockout – An application user selects a record for update, and then leaves for lunch without finishing or aborting the transaction. All other users that need to update that record are forced to wait until the user returns and completes the transaction, or until the DBA kills the offending transaction and releases the lock.

• The Deadlock – Users A and B are both updating the database at the same time. User A locks a record and then attempt to acquire a lock held by user B – who is waiting to obtain a lock held by user A.Both transactions go into an infinite wait state – the so-called deadly embrace or deadlock.

Optimistic Locking :-

All users have read access to the data. When a user attempts to write a change, the application checks to ensure the data has not changed since the last read.

LOST/BURIED UPDATE :-

It’s the end of the year and Scott has done well. King, his manager, decides to give him a well-earned $300 pay rise. Meanwhile Human Resources are also using the Employee system, giving everybody a 5% annual salary adjustment….

--KING’s Session
SQL> SELECT sal FROM emp WHERE empno = 7788;

       SAL
----------
      3000
--HR’s Session
SQL> SELECT sal FROM emp WHERE empno = 7788;

       SAL
----------
      3000

SQL> UPDATE emp SET sal = 3150 WHERE empno = 7788;

1 row updated.

SQL> COMMIT;

Commit complete.
--KING’s Session
SQL> UPDATE emp SET sal = 3300 WHERE empno = 7788;

1 row updated.

SQL> COMMIT;

Commit complete.

The change in HR’s session has been wiped out as shown below –

SQL> SELECT sal FROM emp WHERE empno = 7788;

       SAL
----------
      3300

To avoid this,the concurrency should always be enforced at the trigger level.Though it can be enforced using PL/SQL interface and although it can work,but it can be easily defeated by the casual user with access to a SQL prompt and a rudimentary understanding of SQL.

SQL> ALTER TABLE emp ADD tcn INTEGER;

Table altered.

SQL> UPDATE emp SET tcn = DBMS_UTILITY.GET_TIME;

14 rows updated.

SQL> SELECT * FROM emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        TCN
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5000                    10  393258664
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30  393258664
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10  393258664
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20  393258664
      7788 SCOTT      ANALYST         7566 19-APR-87       3300                    20  393258664
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20  393258664
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20  393258664
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30  393258664
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30  393258664
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30  393258664
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30  393258664
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20  393258664
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30  393258664
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10  393258664

14 rows selected.

SQL> ALTER TABLE emp MODIFY tcn NOT NULL;

Table altered.

SQL> CREATE OR REPLACE TRIGGER emp_bitrg
  2  BEFORE INSERT ON emp FOR EACH ROW
  3  BEGIN
  4   :NEW.tcn := DBMS_UTILITY.GET_TIME;
  5  END;
  6  /

Trigger created.

SQL> CREATE OR REPLACE TRIGGER emp_butrg
  2  BEFORE UPDATE ON emp FOR EACH ROW
  3  BEGIN
  4    IF(:NEW.tcn != :OLD.tcn + 1) THEN
  5       RAISE_APPLICATION_ERROR(-20000, 'Concurrency Failure');
  6    END IF;
  7
  8   :NEW.tcn := DBMS_UTILITY.GET_TIME;
  9  END;
 10  /
Trigger created.
--KING’s Session
SQL> SELECT sal,tcn FROM emp WHERE empno = 7788;

       SAL        TCN
---------- ----------
      3000  393346381
--HR’s Session
SQL> SELECT sal,tcn FROM emp WHERE empno = 7788;

       SAL        TCN
---------- ----------
      3000  393346381

SQL> UPDATE emp SET sal = 3150,tcn = 393346382 WHERE empno = 7788;

1 row updated.

SQL> COMMIT;

Commit complete.
--KING’s Session
SQL> UPDATE emp SET sal = 3300,tcn = 393346382 WHERE empno = 7788;
UPDATE emp SET sal = 3300,tcn = 393346382 WHERE empno = 7788
       *
ERROR at line 1:
ORA-20000: Concurrency Failure
ORA-06512: at "COURSE.EMP_BUTRG", line 3
ORA-04088: error during execution of trigger 'COURSE.EMP_BUTRG'

WorkAround :-

--KING’s Session
SQL> SELECT sal,tcn FROM emp WHERE empno = 7788;

       SAL        TCN
---------- ----------
      3150  393361393

SQL> UPDATE emp SET sal = 3300,tcn = 393361394 WHERE empno = 7788;

1 row updated.

SQL> COMMIT;

Commit complete.

Optimistic Read Locking :-

As with optimistic lock, the optimistic read lock ensures that the object has not changed before writing a change. However, the optimistic read lock also forces a read of any related tables that contribute information to the object.

No Locking :-

The application does not verify that data is current.

Lock Escalation :-

Lock escalation occurs when numerous locks are held at one level of granularity (for example, rows) and a database raises the locks to a higher level of granularity (for example, table). For example, if a single user locks many rows in a table, some databases automatically escalate the user’s row locks to a single table. The number of locks is reduced, but the restrictiveness of what is being locked is increased.

Oracle never escalates locks. Lock escalation greatly increases the likelihood of deadlocks. Imagine the situation where the system is trying to escalate locks on behalf of transaction T1 but cannot because of the locks held by transaction T2. A deadlock is created if transaction T2 also requires lock escalation of the same data before it can proceed.

Types Of LOCKS :-

DML locks (data locks) —> DML locks protect data. For example, table locks lock entire tables, row locks lock selected rows.

DDL locks (dictionary locks) —> DDL locks protect the structure of schema objects—for example, the definitions of tables and views.

Internal locks and latches —> Internal locks and latches protect internal database structures such as datafiles. Internal locks and latches are entirely automatic.

LOCK TABLE tables IN lock_mode MODE [ WAIT [, integer] | NOWAIT ];

This statement allows you to lock a table in the specified lock mode.By doing this, you can share or deny access to that table while you perform operations against it.

WAIT :-

It specifies that the database will wait (up to a certain number of seconds as specified by integer) to acquire a DML lock.

NOWAIT :-

If you specify the NOWAIT keyword, the database does not wait for the lock if the table has already been locked by another user, and instead reports an error. If you leave out the NOWAIT keyword, the database waits until the table is available (and there is no set limit on how long the database will wait). Locking a table never stops other users from querying or reading the table.Specify NOWAIT if you want the database to return control to you immediately if the specified table, partition, or table subpartition is already locked by another user. In this case, the database returns a message indicating that the table, partition, or subpartition is already locked by another user.If you omit this clause, then the database waits until the table is available, locks it, and returns control to you.

Lock_mode :-

Row Share Table Locks (RS) :-

A row share table lock (also sometimes called a subshare table lock, SS) indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. A row share table lock is automatically acquired for a table when one of the following SQL statements is run:

SELECT … FROM table … FOR UPDATE OF … ;

LOCK TABLE table IN ROW SHARE MODE;

A row share table lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.

Permitted Operations : A row share table lock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, other transactions can obtain simultaneous row share, row exclusive, share, and share row exclusive table locks for the same table.

Prohibited Operations : A row share table lock held by a transaction prevents other transactions from exclusive write access to the same table using only the following statement:

LOCK TABLE table IN EXCLUSIVE MODE;

Row Exclusive Table Locks (RX) :-

A row exclusive table lock (also called a subexclusive table lock, SX) generally indicates that the transaction holding the lock has made one or more updates to rows in the table. A row exclusive table lock is acquired automatically for a table modified by the following types of statements:

INSERT INTO table … ;

UPDATE table … ;

DELETE FROM table … ;

LOCK TABLE table IN ROW EXCLUSIVE MODE;

A row exclusive table lock is slightly more restrictive than a row share table lock.

Permitted Operations : A row exclusive table lock held by a transaction allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, row exclusive table locks allow multiple transactions to obtain simultaneous row exclusive and row share table locks for the same table.

Prohibited Operations : A row exclusive table lock held by a transaction prevents other transactions from manually locking the table for exclusive reading or writing. Therefore, other transactions cannot concurrently lock the table using the following statements:

LOCK TABLE table IN SHARE MODE;

LOCK TABLE table IN SHARE EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

Share Table Locks (S) :-

A share table lock is acquired automatically for the table specified in the following statement:

LOCK TABLE table IN SHARE MODE;

Permitted Operations : A share table lock held by a transaction allows other transactions only to query the table, to lock specific rows with SELECT … FOR UPDATE, or to run LOCK TABLE … IN SHARE MODE statements successfully. No updates are allowed by other transactions. Multiple transactions can hold share table locks for the same table concurrently. In this case, no transaction can update the table (even if a transaction holds row locks as the result of a SELECT statement with the FOR UPDATE clause). Therefore, a transaction that has a share table lock can update the table only if no other transactions also have a share table lock on the same table.

Prohibited Operations : A share table lock held by a transaction prevents other transactions from modifying the same table and from executing the following statements:

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

LOCK TABLE table IN ROW EXCLUSIVE MODE;

Share Row Exclusive Table Locks (SRX) :-

A share row exclusive table lock (also sometimes called a share-subexclusive table lock, SSX) is more restrictive than a share table lock. A share row exclusive table lock is acquired for a table as follows:

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

Permitted Operations : Only one transaction at a time can acquire a share row exclusive table lock on a given table. A share row exclusive table lock held by a transaction allows other transactions to query or lock specific rows using SELECT with the FOR UPDATE clause, but not to update the table.

Prohibited Operations : A share row exclusive table lock held by a transaction prevents other transactions from obtaining row exclusive table locks and modifying the same table. A share row exclusive table lock also prohibits other transactions from obtaining share, share row exclusive, and exclusive table locks, which prevents other transactions from executing the following statements:

LOCK TABLE table IN SHARE MODE;

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

LOCK TABLE table IN ROW EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

Exclusive Table Locks (X) :-

An exclusive table lock is the most restrictive mode of table lock, allowing the transaction that holds the lock exclusive write access to the table. An exclusive table lock is acquired for a table as follows:

LOCK TABLE table IN EXCLUSIVE MODE;

Permitted Operations : Only one transaction can obtain an exclusive table lock for a table. An exclusive table lock permits other transactions only to query the table.

Prohibited Operations : An exclusive table lock held by a transaction prohibits other transactions from performing any type of DML statement or placing any type of lock on the table.

Row Locks (TX) :::

A row lock, also called a TX lock, is a lock on a single row of table. A transaction acquires a row lock for each row modified by an INSERT, UPDATE, DELETE, MERGE, or SELECT … FOR UPDATE statement. The row lock exists until the transaction commits or rollback.

Row locks primarily serve as a queuing mechanism to prevent two transactions from modifying the same row. The database always locks a modified row in exclusive mode so that other transactions cannot modify the row until the transaction holding the lock commits or rollback. Row locking provides the finest grain locking possible and so provides the best possible concurrency and throughput.

Table Locks (TM) :::

If a transaction obtains a lock for a row, then the transaction also acquires a lock for the table containing the row.

Table locks perform concurrency control for simultaneous DDL operations so that a table is not dropped/altered in the middle of a DML operation, for example. When Oracle issues a DDL or DML statement on a table, a table lock is then acquired. As a rule, table locks do not affect concurrency of DML operations. Locks can be acquired at both the table and sub-partition level with partitioned tables in Oracle.

A transaction acquires a table lock when a table is modified in the following DML statements: INSERT, UPDATE, DELETE, SELECT with the FOR UPDATE clause, and LOCK TABLE. These DML operations require table locks for two purposes: to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction.

Any table lock prevents the acquisition of an exclusive DDL lock on the same table, and thereby prevents DDL operations that require such locks. For example, a table cannot be altered or dropped if an uncommitted transaction holds a table lock for it.

Scenario ::: 1

Session - 1 :-
SELECT * FROM emp FOR UPDATE; --Row Exclusive Lock Acquired On Entire EMP Table & Displays The Result.

Session - 2 :-
SELECT * FROM emp; --Due To Oracle Read Consistency Feature, All Rows Are Readable But Not Updatable.

SELECT * FROM DBA_DML_LOCKS; --Row Exclusive Lock Acquired On Entire EMP Table Due To Session - 1.

Scenario ::: 2

Session - 1 :-
SELECT * FROM emp FOR UPDATE; --Row Exclusive Lock Acquired On Entire EMP Table & Displays The Result.

Session - 2 :-
SELECT * FROM emp FOR UPDATE; --Row Exclusive Lock Acquired On Entire EMP Table & the session gets HANG.
 
SELECT * FROM DBA_DML_LOCKS; -- 2 Row Exclusive Lock Acquired On EMP Table Due To Session – 1 & Session - 2.

Session - 3 :-
SELECT * FROM emp; --Due To Oracle Read Consistency Feature, All Rows Are Readable But Not Updatable.

Session – 1 :-
COMMIT;

SELECT * FROM DBA_DML_LOCKS; --Row Exclusive Lock For Session – 1 Gets Released And Now We Have 1 Row Exclusive Lock Due To Session - 2.

Scenario ::: 3

Session - 1 :-
SELECT * FROM emp FOR UPDATE NOWAIT; --Row Exclusive Lock Acquired On Entire EMP Table & Displays The Result.

Session - 2 :-
SELECT * FROM emp FOR UPDATE; --Row Exclusive Lock Acquired On Entire EMP Table & the session gets HANG.
 
SELECT * FROM DBA_DML_LOCKS; -- 2 Row Exclusive Lock Acquired On EMP Table Due To Session – 1 & Session - 2.

Session - 3 :-
SELECT * FROM emp; --Due To Oracle Read Consistency Feature, All Rows Are Readable But Not Updatable.

Session – 1 :-
COMMIT;

SELECT * FROM DBA_DML_LOCKS; --Row Exclusive Lock For Session – 1 Gets Released And Now We Have 1 Row Exclusive Lock Due To Session - 2.

Scenario ::: 4

Session - 1 :-
SELECT * FROM emp FOR UPDATE NOWAIT; --Row Exclusive Lock Acquired On Entire EMP Table & Displays The Result.

Session - 2 :-
SELECT * FROM emp FOR UPDATE NOWAIT; --Below Error Generated :

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
 
SELECT * FROM DBA_DML_LOCKS; -- 1 Row Exclusive Lock Acquired On EMP Table Due To Session - 1.

Session - 3 :-
SELECT * FROM emp; --Due To Oracle Read Consistency Feature, All Rows Are Readable But Not Updatable.

 When Normal SELECT statement is fired,no locks are placed on the selected rows.

 With SELECT…FOR UPDATE statement, the database automatically obtains row-level locks on all the rows identified by the SELECT statement.No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT—but other sessions can still read the data.

--Row Exclusive Lock On EMP Table.
SQL> SELECT ename,
  2         empno,
  3         sal,
  4         hiredate
  5    FROM emp
  6   WHERE comm IS NOT NULL
  7  FOR UPDATE;

ENAME           EMPNO        SAL HIREDATE
---------- ---------- ---------- ---------
ALLEN            7499       1600 20-FEB-81
WARD             7521       1250 22-FEB-81
MARTIN           7654       1250 28-SEP-81
TURNER           7844       1500 08-SEP-81

SQL> SELECT ename,
  2         empno,
  3         sal,
  4         hiredate
  5        FROM emp
  6       WHERE comm IS NOT NULL
  7  FOR UPDATE OF ename;

ENAME           EMPNO        SAL HIREDATE
---------- ---------- ---------- ---------
ALLEN            7499       1600 20-FEB-81
WARD             7521       1250 22-FEB-81
MARTIN           7654       1250 28-SEP-81
TURNER           7844       1500 08-SEP-81
--Row Exclusive Lock On DEPT Table.
SQL> SELECT e.empno,
  2         e.ename,
  3         e.job,
  4         e.deptno
  5        FROM emp e, dept d
  6       WHERE e.deptno = d.deptno
  7  FOR UPDATE OF d.loc;

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7782 CLARK      MANAGER           10
      7934 MILLER     CLERK             10
      7839 KING       PRESIDENT         10
      7902 FORD       ANALYST           20
      7788 SCOTT      ANALYST           20
      7566 JONES      MANAGER           20
      7369 SMITH      CLERK             20
      7876 ADAMS      CLERK             20
      7521 WARD       SALESMAN          30
      7654 MARTIN     SALESMAN          30
      7844 TURNER     SALESMAN          30
      7900 JAMES      CLERK             30
      7499 ALLEN      SALESMAN          30
      7698 BLAKE      MANAGER           30

14 rows selected.

--Row Exclusive Lock On EMP Table.
SQL> SELECT e.empno,
  2         e.ename,
  3         e.job,
  4         e.deptno
  5        FROM emp e, dept d
  6       WHERE e.deptno = d.deptno
  7  FOR UPDATE OF e.sal;

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7839 KING       PRESIDENT         10
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7566 JONES      MANAGER           20
      7788 SCOTT      ANALYST           20
      7902 FORD       ANALYST           20
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7654 MARTIN     SALESMAN          30
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20
      7900 JAMES      CLERK             30
      7934 MILLER     CLERK             10

14 rows selected.
--Row Exclusive Lock On EMP & DEPT Table.
SQL> SELECT e.empno,
  2         e.ename,
  3         e.job,
  4         e.deptno
  5    FROM emp e, dept d
  6   WHERE e.deptno = d.deptno
  7  FOR UPDATE;

     EMPNO ENAME      JOB           DEPTNO
---------- ---------- --------- ----------
      7839 KING       PRESIDENT         10
      7698 BLAKE      MANAGER           30
      7782 CLARK      MANAGER           10
      7566 JONES      MANAGER           20
      7788 SCOTT      ANALYST           20
      7902 FORD       ANALYST           20
      7369 SMITH      CLERK             20
      7499 ALLEN      SALESMAN          30
      7521 WARD       SALESMAN          30
      7654 MARTIN     SALESMAN          30
      7844 TURNER     SALESMAN          30
      7876 ADAMS      CLERK             20
      7900 JAMES      CLERK             30
      7934 MILLER     CLERK             10

14 rows selected.

If you plan on updating or deleting records that have been referenced by a SELECT FOR UPDATE statement, you can use the WHERE CURRENT OF statement.

WHERE CURRENT OF clause for both UPDATE and DELETE statements inside a cursor allows you to easily make changes to the most recently fetched row of data.

WHERE CURRENT OF is used in a table that has no primary key and works ONLY with SELECT FOR UPDATE or SELECT FOR UPDATE NOWAIT query.

As soon as a cursor with a FOR UPDATE clause is opened, all rows identified in the result set of the cursor are locked and remain locked until your session ends or your code explicitly issues either a COMMIT or a ROLLBACK. When either of these occurs,the locks on the rows are released. As a result, you cannot execute another FETCH against a FOR UPDATE cursor after a COMMIT or ROLLBACK. You will have lost your position in the cursor.

If you ever need to execute a COMMIT or ROLLBACK as you FETCH records from a SELECT FOR UPDATE cursor, you should include code (such as a loop EXIT or other conditional logic) to halt any further fetches from the cursor.

SQL> CREATE TABLE test_emp
  2  (
  3    name  VARCHAR2(30 BYTE),
  4    salary NUMBER
  5  );

Table created.

SQL> INSERT ALL
  2  INTO test_emp (name,salary) VALUES ('AAA',1000)
  3  INTO test_emp (name,salary) VALUES ('BBB',2000)
  4  INTO test_emp (name,salary) VALUES ('CCC',3000)
  5  INTO test_emp (name,salary) VALUES ('AAA',4000)
  6  INTO test_emp (name,salary) VALUES ('EEE',5000)
  7  INTO test_emp (name,salary) VALUES ('BBB',6000)
  8  SELECT * FROM DUAL;

6 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM test_emp;

NAME                               SALARY
------------------------------ ----------
AAA                                  1000
BBB                                  2000
CCC                                  3000
AAA                                  4000
EEE                                  5000
BBB                                  6000

SQL> DECLARE
  2     CURSOR cur_emp IS
  3          SELECT * FROM test_emp;
  4  BEGIN
  5        FOR rec IN cur_emp
  6       LOOP
  7          UPDATE test_emp SET salary = salary + 1000
  8             WHERE name = rec.name;
  9       END LOOP;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM test_emp;

NAME                               SALARY
------------------------------ ----------
AAA                                  3000
BBB                                  4000
CCC                                  4000
AAA                                  6000
EEE                                  6000
BBB                                  8000

SQL> DELETE FROM test_emp;

6 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> INSERT ALL
  2  INTO test_emp (name,salary) VALUES ('AAA',1000)
  3  INTO test_emp (name,salary) VALUES ('BBB',2000)
  4  INTO test_emp (name,salary) VALUES ('CCC',3000)
  5  INTO test_emp (name,salary) VALUES ('AAA',4000)
  6  INTO test_emp (name,salary) VALUES ('EEE',5000)
  7  INTO test_emp (name,salary) VALUES ('BBB',6000)
  8  SELECT * FROM DUAL;

6 rows created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM test_emp;

NAME                               SALARY
------------------------------ ----------
AAA                                  1000
BBB                                  2000
CCC                                  3000
AAA                                  4000
EEE                                  5000
BBB                                  6000

SQL> DECLARE
  2     CURSOR cur_emp IS
  3          SELECT * FROM test_emp FOR UPDATE;
  4  BEGIN
  5        FOR rec IN cur_emp
  6       LOOP
  7          UPDATE test_emp SET salary = salary + 1000
  8             WHERE CURRENT OF cur_emp;
  9       END LOOP;
 10  END;
 11  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM test_emp;

NAME                               SALARY
------------------------------ ----------
AAA                                  2000
BBB                                  3000
CCC                                  4000
AAA                                  5000
EEE                                  6000
BBB                                  7000

SQL> DECLARE
  2     CURSOR cur_emp IS
  3          SELECT * FROM test_emp FOR UPDATE;
  4  BEGIN
  5        FOR rec IN cur_emp
  6       LOOP
  7          UPDATE test_emp SET salary = salary + 1000
  8             WHERE CURRENT OF cur_emp;
  9                COMMIT;
 10       END LOOP;
 11  END;
 12  /
DECLARE
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 5

SQL> SELECT * FROM test_emp;

NAME                               SALARY
------------------------------ ----------
AAA                                  3000
BBB                                  3000
CCC                                  4000
AAA                                  5000
EEE                                  6000
BBB                                  7000

SQL> DECLARE
  2     CURSOR cur_emp IS
  3          SELECT * FROM test_emp FOR UPDATE;
  4  BEGIN
  5        FOR rec IN cur_emp
  6       LOOP
  7          UPDATE test_emp SET salary = salary + 1000
  8             WHERE CURRENT OF cur_emp;
  9                COMMIT;
 10                       EXIT;
 11       END LOOP;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM test_emp;

NAME                               SALARY
------------------------------ ----------
AAA                                  4000
BBB                                  3000
CCC                                  4000
AAA                                  5000
EEE                                  6000
BBB                                  7000

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

Leave a Reply

Your email address will not be published. Required fields are marked *