Odtnhj

I implemented an optimistic lock with a trigger, how can I do it without a trigger within procedure?

I reading this post Implementic Optimistic Locking

But I do not understand how I will implement it in the procedure.

I tried this, but I don't get what I expected

SQL> CREATE OR REPLACE PACKAGE BODY account_api AS
 2 PROCEDURE upd_account
 3 (p_acc_id accounts.acc_id%type
 4 , p_acc_name accounts.acc_name%type
 5 , p_acc_amount accounts.acc_amount%type
 6 , p_acc_date accounts.acc_date%type
 7 , p_acc_version accounts.acc_version%type
 8 )
 9 IS
 10 BEGIN
 11 UPDATE accounts
 12 set acc_name = acc_name
 13 , acc_amount = acc_amount
 14 , acc_date = acc_date
 15 , acc_version = acc_version + 1
 16 where acc_id = p_acc_id
 17 and acc_version = p_acc_version;
 18 if(SQL%ROWCOUNT = 0)
 19 THEN
 20 RAISE_APPLICATION_ERROR( -20001, 'Oops, the row has changed since you read it.' );
 21 END IF;
 22 END;
 23 end account_api;
 24 /

SQL> begin
 2 account_api.upd_account(1, 'user12', 1200, sysdate, '11-NOV-18 06.10.01.660948 AM');
 3 end;
 4 /
PL/SQL procedure successfully completed.

I'm trying the code with the same timestamp and it's done now

 SQL> begin
 2 account_api.upd_account(1, 'user1', 1200, sysdate, '11-NOV-18 06.10.01.660948 AM');
 3 end;
 4 /
 PL/SQL procedure successfully completed.

The idea is that you would have first selected a record (through a select query) and then decide to update it. The procedure you quoted is correct. It requires that you pass it the value of acc_version as last argument. You have that value from the record you queried.

This is a kind of contract you have to follow: you need to query that acc_version and then pass it to the procedure for the update you want to make. After each update, you have to re-query what the current value of acc_version is if you still need to make more updates.

The app_version field needs to be a number (not a date). It is intended to reflect the version of the record, like version 1, 2, 3, ... which can be seen as the number of updates that have been done on that particular record.

The procedure will make the update on the condition that this value in the record was not updated in the mean time (by some other update). It uses a simple where clause for that check.

If the update does not update anything, then it means the record did not satisfy this condition any more (and was altered). In that case the exception is raised.

But if the acc_version is still as you passed it to the procedure, the update statement will indeed update the targeted record. At the same time the update statement increments acc_version. This will prevent other clients who had already queried this record before this update, to make updates. They will need to re-query the record to get the right value of acc_version, and try again.

This is what I wanted to achieve

 PROCEDURE upd_account
 ( p_acc_id accounts.acc_id%type
 , p_acc_name accounts.acc_name%type
 , p_acc_amount accounts.acc_amount%type
 , p_acc_date accounts.acc_date%type
 , p_version accounts.version%type
 )
 IS
 BEGIN
 UPDATE accounts
 set acc_name = p_acc_name
 , acc_amount = p_acc_amount
 , acc_date = p_acc_date
 , version = p_version + 1
 where acc_id = p_acc_id
 and version = p_version;
 DBMS_OUTPUT.PUT_LINE ('Number of updated records: ' || TO_CHAR(SQL%ROWCOUNT));
 if(SQL%ROWCOUNT = 0)
 THEN
 RAISE_APPLICATION_ERROR( -20001, 'Oops, the row has changed since you read it.' );
 END IF;
 END;