Home » SQL & PL/SQL » SQL & PL/SQL » How to use Optimistic locking in my Package (Oracle 12c Release 2)
icon5.gif  How to use Optimistic locking in my Package [message #673136] Thu, 08 November 2018 13:51 Go to next message
petar97
Messages: 32
Registered: October 2018
Member
How to use optimistic locking in Procedure upd_account, or I should use it in some other way?

I can not find anything about consistent read. Is it associated with optimistic locking? how to use consistent read? (or to make a new question)

I am sorry if I did not explain well.

I'm a beginner in Oracle

I am work in oracle 12c release 2

Thank you advance

I have package body and it look like this:
CREATE OR REPLACE PACKAGE BODY account_api AS

        ...

        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
        )
         IS
       BEGIN
           UPDATE accounts
            set acc_name = p_acc_name
              , acc_amount = p_acc_amount
              , acc_date   = p_acc_date
           WHERE acc_id = p_acc_id;
       COMMIT;
      END;
       PROCEDURE del_accounts
          (p_acc_id     accounts.acc_id%type)
       IS  
        BEGIN 
        DELETE FROM accounts WHERE acc_id = p_acc_id;
        COMMIT;
        EXCEPTION
          WHEN OTHERS THEN
          ROLLBACK;
          RAISE;
       END;
            FUNCTION get_amount
          (p_acc_id    accounts.acc_id%type)
       return Number is res number;
       begin
         select acc_amount into res
         from  accounts where acc_id = p_acc_id;
          return res;
      end;
      FUNCTION get_date
          (p_acc_id    accounts.acc_id%type)
       RETURN date IS res1 date;
      BEGIN
          SELECT acc_date INTO res1
          FROM accounts WHERE acc_id = p_acc_id;
          RETURN res1;
      end;
      end account_api;
      /

[Updated on: Thu, 08 November 2018 14:08]

Report message to a moderator

Re: How to use Optimistic locking in my Package [message #673137 is a reply to message #673136] Thu, 08 November 2018 14:04 Go to previous messageGo to next message
John Watson
Messages: 7664
Registered: January 2010
Location: Global Village
Senior Member
My comment? Don't worry about "optimistic locking", just let Uncle Oracle manage it. However, you should not be committing in those procedures, the commit should be done (or not) by the process that calls the procedure. For example, what if I am in the middle of some complicated operation, I call your procedure, and it goes and commits everything I've done! No thank you.
Re: How to use Optimistic locking in my Package [message #673138 is a reply to message #673137] Thu, 08 November 2018 14:19 Go to previous messageGo to next message
petar97
Messages: 32
Registered: October 2018
Member
I do this as an exercise, in the task it is written that I pay attention to multiple use by the user, that more users can change values for the same acc_id.
and to look optimistic vs pessimistic locking and consistent read

[Updated on: Thu, 08 November 2018 14:21]

Report message to a moderator

Re: How to use Optimistic locking in my Package [message #673139 is a reply to message #673138] Thu, 08 November 2018 14:36 Go to previous messageGo to next message
petar97
Messages: 32
Registered: October 2018
Member
Locking it end with a code inside procedure or in some other way?
Re: How to use Optimistic locking in my Package [message #673140 is a reply to message #673136] Thu, 08 November 2018 15:07 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Sun, 28 October 2018 20:53

Quote:
2/ Do not commit inside a procedure: this is the caller that knows if it wants to commit or not, not the procedure (above all when you sometimes commit and sometimes not)
Re: How to use Optimistic locking in my Package [message #673141 is a reply to message #673136] Thu, 08 November 2018 15:09 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

BlackSwan wrote on Sun, 28 October 2018 20:47

http://www.orafaq.com/wiki/WHEN_OTHERS
Re: How to use Optimistic locking in my Package [message #673142 is a reply to message #673136] Thu, 08 November 2018 15:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
how to use consistent read?
You have nothing to do, this is the default in Oracle.

EdStevens wrote on Thu, 01 November 2018 12:47
...
- *Read a chapter a day from the Concepts Manual*.
...
- *When you have finished reading the Concepts Manual, do it again*.

Give a man a fish and he eats for a day. Teach a man to fish and he feeds himself for a lifetime.

=================================

[Updated on: Thu, 08 November 2018 15:12]

Report message to a moderator

Re: How to use Optimistic locking in my Package [message #673143 is a reply to message #673139] Thu, 08 November 2018 15:14 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
petar97 wrote on Thu, 08 November 2018 21:36
Locking it end with a code inside procedure or in some other way?
There is nothing specific to "end". The code ends with its last line. What do you mean by this?

Re: How to use Optimistic locking in my Package [message #673144 is a reply to message #673143] Thu, 08 November 2018 16:27 Go to previous messageGo to next message
petar97
Messages: 32
Registered: October 2018
Member
I tried this as well but report a mistake

I added 1 row in table which is timestamp
create table accounts (
    	acc_id 		number,
    	acc_name 	varchar2(32),
    	acc_amount	number,
    	acc_date 	date,
    	acc_lock_id 	timestamp not null
    );
And my procedure look like this now
There is some mistake here, and I do not know if this is the right way
   PROCEDURE read_account(
    	       p_acc_id      accounts.acc_id%type
            )
    	is
    	begin
    	    SET NOCOUNT ON
    	      SELECT
    		acc_id
    	      , acc_name
    	      , acc_amount
    	      , acc_date
    	      , CONVERT(BIGINT, acc_lock_id) AS acc_lock_id
    		FROM
    			accounts
    		WHERE
    			acc_id = p_acc_id
    	end;
    	        
            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_acc_lock_id accounts.acc_lock_id%type
            )
             IS
           BEGIN
    	   SET NOCOUNT ON
               UPDATE accounts
         		set acc_name = p_acc_name
         		  , acc_amount = p_acc_amount
         		  , acc_date   = p_acc_date
               WHERE acc_id = p_acc_id;
    	   AND
    		acc_lock_id =  p_acc_lock_id accounts.acc_lock_id%type
          END;
Re: How to use Optimistic locking in my Package [message #673145 is a reply to message #673144] Thu, 08 November 2018 17:25 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
>I tried this as well but report a mistake
MISTAKE?
What mistake?
I don't see any mistake.

SHOW us what you do & how Oracle responds using COPY & PASTE

>acc_lock_id = p_acc_lock_id accounts.acc_lock_id%type
not as above, but as below
acc_lock_id = p_acc_lock_id accounts.acc_lock_id
Re: How to use Optimistic locking in my Package [message #673150 is a reply to message #673144] Fri, 09 November 2018 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
SET NOCOUNT ON
This is not PL/SQL.
Where did you find this?

Re: How to use Optimistic locking in my Package [message #673151 is a reply to message #673138] Fri, 09 November 2018 01:06 Go to previous messageGo to next message
John Watson
Messages: 7664
Registered: January 2010
Location: Global Village
Senior Member
petar97 wrote on Thu, 08 November 2018 20:19
I do this as an exercise, in the task it is written that I pay attention to multiple use by the user, that more users can change values for the same acc_id.
and to look optimistic vs pessimistic locking and consistent read
You teacher may be wanting you to use a structure such as SELECT FOR UPDATE or perhaps SET TRANSACTION READ ONLY or SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to implement the various ANSI isolation levels. Have you read about them yet? For example,
https://www.oracle.com/technetwork/testcontent/o65asktom-082389.html
Re: How to use Optimistic locking in my Package [message #673152 is a reply to message #673151] Fri, 09 November 2018 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think he has to convert some Transact-SQL code to PL/SQL but is not skilled to do it and doesn't actually want to learn PL/SQL but us to do his job as his previous topics and their ignored advises show it.

Re: How to use Optimistic locking in my Package [message #673153 is a reply to message #673152] Fri, 09 November 2018 07:10 Go to previous messageGo to next message
petar97
Messages: 32
Registered: October 2018
Member
Excuse me for "set nocount on" I copied the code by mistake.

when this is corrected whether the code is good?

Re: How to use Optimistic locking in my Package [message #673154 is a reply to message #673152] Fri, 09 November 2018 07:17 Go to previous messageGo to next message
John Watson
Messages: 7664
Registered: January 2010
Location: Global Village
Senior Member
@mc Yes, I see that SET NOCOUNT ON and the CONVERT(BIGINT) thing are from SQL Server.

@op, Why did you not say that you are re-writing SQL Server code into PL/SQL? Apart from that, you have to do a bit of work yourself. I know that many SQL Server people find the idea of "work" rather unpleasant, but there is no alternative. Try to compile your code and see what errors you get. I can assure you that you are going to get some.
Re: How to use Optimistic locking in my Package [message #673155 is a reply to message #673153] Fri, 09 November 2018 07:22 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
petar97 wrote on Fri, 09 November 2018 05:10
Excuse me for "set nocount on" I copied the code by mistake.

when this is corrected whether the code is good?

which metric measures "good"?

How do we know what the correct results should be?
Re: How to use Optimistic locking in my Package [message #673158 is a reply to message #673155] Fri, 09 November 2018 08:10 Go to previous messageGo to next message
petar97
Messages: 32
Registered: October 2018
Member
I know the difference between "Optimistic vs Pessimistic locking", but I do not know how to use the optimistic lock on the procedure.

I can not find anything on the Internet, and that's why I asked the question here

My procedure without optimistic locking look like this:

CREATE OR REPLACE PACKAGE BODY account_api AS

        ...

      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
        )
      IS
      BEGIN
           UPDATE accounts
            set acc_name = p_acc_name
              , acc_amount = p_acc_amount
              , acc_date   = p_acc_date
           WHERE acc_id = p_acc_id;
       COMMIT;
      END;
      ...

      end account_api;
      /
And I need to use optimistic locking I do not know how to do it, and I can not find anywhere.

If you know how to do this, please write to me, or send me a link where it is explained


My english is bad, sorry if you don't understand me.

[Updated on: Fri, 09 November 2018 08:13]

Report message to a moderator

Re: How to use Optimistic locking in my Package [message #673159 is a reply to message #673158] Fri, 09 November 2018 08:21 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
I do not understand what exactly you mean by "optimistic locking".
I do not understand what problem "optimistic locking" would solve for you?

I do not know what answer you desire?

Other sessions won't see DML changes made by UPDATE statement until after COMMIT has been executed.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5771117722373
Re: How to use Optimistic locking in my Package [message #673160 is a reply to message #673159] Fri, 09 November 2018 08:33 Go to previous messageGo to next message
petar97
Messages: 32
Registered: October 2018
Member
In the assignment writes: I need to pay attention to the possibility of multi-user use and look for information about "pesimistic vs. optimistic locking", and "consistent read".
Maybe I did not understand what I should do

I do not know how to use "optimistic locking"

as explained here: http://www.blackwasp.co.uk/OptimisticLocking.aspx but this is not pl / sql

[Updated on: Fri, 09 November 2018 08:34]

Report message to a moderator

Re: How to use Optimistic locking in my Package [message #673161 is a reply to message #673160] Fri, 09 November 2018 09:10 Go to previous messageGo to next message
cookiemonster
Messages: 13336
Registered: September 2008
Location: Rainy Manchester
Senior Member
Maybe you should have googled "optimistic locking oracle".
There's an example here that would work in oracle
Re: How to use Optimistic locking in my Package [message #673163 is a reply to message #673160] Fri, 09 November 2018 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

These are SQL Server concerns, in Oracle readers do not block readers and readers do not block writers so there is no need of such stuff; as John said "just let Uncle Oracle manage it".

All your conceptual issues are answered in (once again) Database Concepts.

[Updated on: Fri, 09 November 2018 10:22]

Report message to a moderator

Re: How to use Optimistic locking in my Package [message #673165 is a reply to message #673163] Fri, 09 November 2018 10:22 Go to previous messageGo to next message
petar97
Messages: 32
Registered: October 2018
Member
I created package body like this
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  /

This is in this case here https://stackoverflow.com/questions/41006941/implementing-optimistic-locking-in-oracle
Is this what I was looking for?

Re: How to use Optimistic locking in my Package [message #673166 is a reply to message #673165] Fri, 09 November 2018 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No unless you read it before and pk can be updated but you don't need to read it before if you want to update, just update it.

[Updated on: Fri, 09 November 2018 10:25]

Report message to a moderator

Re: How to use Optimistic locking in my Package [message #673167 is a reply to message #673165] Fri, 09 November 2018 10:23 Go to previous messageGo to next message
gazzag
Messages: 1042
Registered: November 2010
Location: Bristol, UK
Senior Member
As others have stated, you don't need to worry about it.
Re: How to use Optimistic locking in my Package [message #673169 is a reply to message #673167] Fri, 09 November 2018 11:12 Go to previous messageGo to next message
petar97
Messages: 32
Registered: October 2018
Member
When I'm going to update the table I get this error now

SQL> begin
2 account_api.upd_account(1, 'username', 2000, sysdate, CURRENT_TIMESTAMP);
3 end;
4 /
begin
*
ERROR at line 1:
ORA-20001: Oops, the row has changed since you read it.
ORA-06512: at "PETAR1.ACCOUNT_API", line 20
ORA-06512: at line 2

[Updated on: Fri, 09 November 2018 11:15]

Report message to a moderator

Re: How to use Optimistic locking in my Package [message #673170 is a reply to message #673169] Fri, 09 November 2018 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Because account 1 with version CURRENT_TIMESTAMP does not exist.
Your code is most likely plain wrong.

Re: How to use Optimistic locking in my Package [message #673172 is a reply to message #673170] Fri, 09 November 2018 11:27 Go to previous messageGo to next message
petar97
Messages: 32
Registered: October 2018
Member
I first inserted the data in the table

SQL> insert into accounts
2 values (acc_seq.nextval, 'user123', 14444, sysdate, CURRENT_TIMESTAMP);

user have id 1
Re: How to use Optimistic locking in my Package [message #673173 is a reply to message #673172] Fri, 09 November 2018 11:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I advise you to read the documentation about CURRENT_TIMESTAMP.

Re: How to use Optimistic locking in my Package [message #673174 is a reply to message #673172] Fri, 09 November 2018 11:30 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
petar97 wrote on Fri, 09 November 2018 09:27
I first inserted the data in the table

SQL> insert into accounts
2 values (acc_seq.nextval, 'user123', 14444, sysdate, CURRENT_TIMESTAMP);

user have id 1
That was then & this is NOW.

CURRENT_TIMESTAMP then does not equal CURRENT_TIMESTAMP now; since actual wall clock time has changed & progressed.
Re: How to use Optimistic locking in my Package [message #673175 is a reply to message #673174] Fri, 09 November 2018 11:42 Go to previous messageGo to next message
petar97
Messages: 32
Registered: October 2018
Member
then what should I enter?

And tell me if this is an optimistic locking?

I will read the documentation about CURRENT_TIMESTAMP

[Updated on: Fri, 09 November 2018 11:44]

Report message to a moderator

Re: How to use Optimistic locking in my Package [message #673176 is a reply to message #673175] Fri, 09 November 2018 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
then what should I enter?

What is the purpose of "version".

Quote:
And tell me if this is an optimistic locking?

Read the previous posts.

Quote:
I will read the documentation about CURRENT_TIMESTAMP

You should have read it BEFORE using it in the code above all if it is so important for you (but I think this "lock_id" now "version" is useless).

You try to apply SQL Server to Oracle, you can't, this is pointless.
Porting code is NOT just stupidly convert each key word of one language into another one.
The transaction implementation is COMPLETELY different between SQL Server and Oracle, the architecture of the application MUST be changed.

Re: How to use Optimistic locking in my Package [message #673177 is a reply to message #673175] Fri, 09 November 2018 12:05 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
petar97 wrote on Fri, 09 November 2018 09:42


And tell me if this is an optimistic locking?

What is the purpose of "optimistic locking"?
What problem does "optimistic locking" solve?
What are the requirements for "optimistic locking"?

Does your posted code satisfy the requirements for "optimistic locking"?
Re: How to use Optimistic locking in my Package [message #673178 is a reply to message #673175] Fri, 09 November 2018 12:19 Go to previous messageGo to next message
John Watson
Messages: 7664
Registered: January 2010
Location: Global Village
Senior Member
You said that you understand optimistic and pessimistic locking. Are you sure? Consider this example:
You go to a website and find something you want to buy. You think for a while (an assumption here is that you do think) and then click "I buy it!" the website responds with "Too late sonny, someone else has bought it already". Do you think the website is using optimistic or pessimistic locking?
Re: How to use Optimistic locking in my Package [message #673183 is a reply to message #673178] Sat, 10 November 2018 13:25 Go to previous messageGo to next message
petar97
Messages: 32
Registered: October 2018
Member
In that case, I think that the website uses optimistic locking.

I implemented an optimistic lock with the trigger
alter table accounts add TCN integer;
And UPDATE accounts set TCN = DBMS_UTILITY.GET_TIME;

And tcn can not be null
alter table accounts modify TCN not null;

I created trigger before update
SQL> create or replace trigger acc_preupdate
  2  before update on accounts
  3  for each row
  4  begin
  5  if(:NEW.TCN != :OLD.TCN+1) THEN
  6  RAISE_APPLICATION_ERROR(-20000, 'Concurrency Failure');
  7  end if;
  8  :NEW.TCN := DBMS_UTILITY.GET_TIME;
  9  END;
 10  /
And trigger before insert
SQL> create or replace trigger acc_preinsert
  2  before insert on accounts
  3  for each row
  4  begin
  5  :NEW.TCN := DBMS_UTILITY.GET_TIME;
  6  end;
Re: How to use Optimistic locking in my Package [message #673184 is a reply to message #673183] Sat, 10 November 2018 13:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I implemented an optimistic lock with the trigger

Doesn't work and is plain stupid.

[Updated on: Sat, 10 November 2018 13:31]

Report message to a moderator

Re: How to use Optimistic locking in my Package [message #673185 is a reply to message #673184] Sat, 10 November 2018 13:31 Go to previous messageGo to next message
petar97
Messages: 32
Registered: October 2018
Member
to get an error when something has changed in the table
Re: How to use Optimistic locking in my Package [message #673186 is a reply to message #673185] Sat, 10 November 2018 13:35 Go to previous messageGo to next message
petar97
Messages: 32
Registered: October 2018
Member
how it should look, that it would not be stupid
Re: How to use Optimistic locking in my Package [message #673187 is a reply to message #673186] Sat, 10 November 2018 13:39 Go to previous messageGo to next message
Michel Cadot
Messages: 65964
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You don't read what is posted, you don't follow the advises, then there is nothing you can do.
I leave this topic.
Good luck.

Re: How to use Optimistic locking in my Package [message #673188 is a reply to message #673186] Sat, 10 November 2018 14:04 Go to previous messageGo to next message
BlackSwan
Messages: 26275
Registered: January 2009
Location: SoCal
Senior Member
petar97 wrote on Sat, 10 November 2018 11:35
how it should look, that it would not be stupid
Does it work as intended & desired?

using COPY & PASTE post results showing correct results showing successful UPDATE of ACCOUNTS table.
Re: How to use Optimistic locking in my Package [message #673191 is a reply to message #673188] Sat, 10 November 2018 14:27 Go to previous messageGo to previous message
petar97
Messages: 32
Registered: October 2018
Member
this works for me, I wanted to ask if I could do it better
Previous Topic: calculate a function on whole table
Next Topic: Row generator
Goto Forum:
  


Current Time: Thu Nov 15 17:01:26 CST 2018