How to use Optimistic locking in my Package [message #673136] |
Thu, 08 November 2018 13:51 |
|
petar97
Messages: 120 Registered: October 2018
|
Senior 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 |
John Watson
Messages: 8931 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 #673142 is a reply to message #673136] |
Thu, 08 November 2018 15:12 |
|
Michel Cadot
Messages: 68645 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 #673144 is a reply to message #673143] |
Thu, 08 November 2018 16:27 |
|
petar97
Messages: 120 Registered: October 2018
|
Senior 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 #673151 is a reply to message #673138] |
Fri, 09 November 2018 01:06 |
John Watson
Messages: 8931 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
petar97 wrote on Thu, 08 November 2018 20:19I 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 #673154 is a reply to message #673152] |
Fri, 09 November 2018 07:17 |
John Watson
Messages: 8931 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 #673158 is a reply to message #673155] |
Fri, 09 November 2018 08:10 |
|
petar97
Messages: 120 Registered: October 2018
|
Senior 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 #673165 is a reply to message #673163] |
Fri, 09 November 2018 10:22 |
|
petar97
Messages: 120 Registered: October 2018
|
Senior 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 #673178 is a reply to message #673175] |
Fri, 09 November 2018 12:19 |
John Watson
Messages: 8931 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?
|
|
|
|
|
|
|
|
|
|