Agfdhyk

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;