SQL select for update cursor

The cursor select operation will not perform any lock settings on the row being processed, so that other sessions connected to the database can change the selected data, using the for update clause, A mutex lock is added to the corresponding row of the previous active set returned by open. These locks prevent other sessions from modifying the rows in the active set. Until the entire transaction is committed.

Example:

Declare cur cursor for select * from [Table]

For update of [Table. Col]

 

Open cur

While @ fetch_status = 0

Begin

Update [Table] Set [Table. Col] While current of cur

End

Close cur

Deallocate cur

Lock:

Update, insert, delete, select... for update will lock the corresponding row.

Only one transaction can lock the corresponding row. That is to say, if one row has been locked, it cannot be locked by other transactions.

The lock is generated by statement but ended by transaction (commit, rollback). That is to say, after an SQL statement is completed, the lock still exists and will be release only after commit/rollback.

SELECT.... FOR UPDATE [OF cols] [NOWAIT];OF colsSELECT cols FROM tables [WHERE...] FOR UPDATE [OF cols] [NOWAIT];

The previous for update is omitted. Let's talk about the.

Transaction A runs select a. object_name, A. object_id from wwm2 A, wwm3 B2 where B. Status = 'valid' and A. object_id = B. object_id3 * for update of A. Status

Transaction B can perform DML operations on the corresponding row of wwm3 in Table B, but cannot perform DML operations on the corresponding row of wwm2 in table.

Take a look.

Transaction A runs select a. object_name, A. object_id from wwm2 A, wwm3 B2 where B. Status = 'valid' and A. object_id = B. object_id3 * for update of B. Status

Transaction B can perform DML operations on the corresponding row of wwm2 in Table A, but cannot perform DML operations on the corresponding row of wwm3 in table B.

That is to say, the lock is still a row, but if of is not added, it will lock all involved tables. After adding of, it will only lock the table where the words.

Nowait (If you must use for update, I suggest adding Nowait)

When a lock conflict exists, an error is prompted and the statement is terminated instead of waiting there. The returned error is "ORA-00054: Resource busy and acquire with Nowait specified"

In addition, the following usage is also recommended and should be considered as appropriate.

FOR UPDATE WAIT 5

After 5 seconds, a message is displayed:

ORA-30006: resource busy; acquire with WAIT timeout expiredFOR UPDATE NOWAIT SKIP LOCKED;

no rows selectedTABLE LOCKSLOCK TABLE table(s) IN EXCLUSIVE MODE [NOWAIT];

The lock is also released at the end of transaction.

Deadlock:

transaction a lock rowA , then transaction b lock rowBthen transaction a tries to lock rowB, and transaction b tries to lock rowA

That is to say, both transactions attempt to lock each other's locked row and are waiting for the other to release its lock. This will cause a deadlock. In addition, deadlock also has a 600 prompt.