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
