Home » SQL & PL/SQL » SQL & PL/SQL » How to prevent Lock in MERGE or Update?
| How to prevent Lock in MERGE or Update? [message #146924] |
Mon, 14 November 2005 11:13  |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Hi,
I am using MERGE command and also Update command for one of my job and Users are also updating through application.
How can prevent the lock in this application or in my code because it's hanging for ever and we have to kill it although
if happens in week end then it more difficult.
My code is foolowing:
BEGIN
MERGE INTO INVENTORY T
USING (select INVENTORY_ID,STOCK from INVENTORY_LOAD) S
ON (S.INVENTORY_ID = T.INVENTORY_ID)
WHEN MATCHED THEN
UPDATE
SET T.VERSION = '0',
T.CAT_ID = S.INVENTORY_ID,
T.STATUS = '1' ,
T.CREATION_DATE = SYSDATE,
T.STOCK = S.STOCK
WHEN NOT MATCHED THEN
INSERT
( T.INVENTORY_ID,T.VERSION,T.CAT_ID, T.STATUS,T.CREATION_DATE, T.STOCK)
VALUES
( CASE etl.merge_counter(etl.c_inserting) WHEN 0 THEN S.INVENTORY_ID END,
'0',S.INVENTORY_ID, '1',SYSDATE, S.STOCK);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows merged.');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(etl.get_merge_insert_count) || ' rows inserted.');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(etl.get_merge_update_count( SQL%ROWCOUNT )) || ' rows updated.');
etl.reset_counters;
commit;
END;
How can I prevent locking if soomebody is updating through application as I can't prevent them so I have to stop
my code to do update when somebody is updating through application?
Please let me know if you want to ny details.
User is trying to update through application using following code.
UPDATE INVENTORY
SET STOCK=:1
WHERE INVENTORY_ID=:2
thanks,
|
|
|
|
| Re: How to prevent Lock in MERGE or Update? [message #146925 is a reply to message #146924] |
Mon, 14 November 2005 11:26  |
dmitry.nikiforov
Messages: 723
Registered: March 2005
|
Senior Member |
|
|
The assignment of exclusive row lock is the standard Oracle
mechanism. To prevent the user waiting for the resourse release
you can check in your code is the resource busy or not using
SELECT ... FOR UPDATE NOWAIT.
For example something like:
1-th session:
SQL> UPDATE emp set ename = ename WHERE ename = 'SMITH';
1 row updated.
2-th session:
SQL> DECLARE
2 check_ number(1);
3 LOCK_EXC EXCEPTION;
4 PRAGMA EXCEPTION_INIT(LOCK_EXC,-54);
5 BEGIN
6
7 SELECT 1 INTO check_
8 FROM EMP
9 WHERE ENAME = 'SMITH' FOR UPDATE NOWAIT;
10
11 UPDATE EMP
12 SET SAL=SAL
13 WHERE ename = 'SMITH';
14
15 EXCEPTION
16 WHEN LOCK_EXC THEN
17 DBMS_OUTPUT.PUT_LINE('Resource is busy !');
18 END;
19 /
Resource is busy !
PL/SQL procedure successfully completed.
Rgds.
|
|
|
|
| Re: How to prevent Lock in MERGE or Update? [message #146926 is a reply to message #146925] |
Mon, 14 November 2005 11:34 |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Thanks for quick response.
So I can put in MERGE too like? Is it MERGE can handle the LOCK_EXC EXCEPTION if so How?
BEGIN
MERGE INTO INVENTORY T
USING (select INVENTORY_ID,STOCK from INVENTORY_LOAD FOR UPDATE NOWAIT) S
ON (S.INVENTORY_ID = T.INVENTORY_ID)
WHEN MATCHED THEN
UPDATE
SET T.VERSION = '0',
T.CAT_ID = S.INVENTORY_ID,
T.STATUS = '1' ,
T.CREATION_DATE = SYSDATE,
T.STOCK = S.STOCK
WHEN NOT MATCHED THEN
INSERT
( T.INVENTORY_ID,T.VERSION,T.CAT_ID, T.STATUS,T.CREATION_DATE, T.STOCK)
VALUES
( CASE etl.merge_counter(etl.c_inserting) WHEN 0 THEN S.INVENTORY_ID END,
'0',S.INVENTORY_ID, '1',SYSDATE, S.STOCK);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows merged.');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(etl.get_merge_insert_count) || ' rows inserted.');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(etl.get_merge_update_count( SQL%ROWCOUNT )) || ' rows updated.');
etl.reset_counters;
commit;
END;
|
|
|
|
| Re: How to prevent Lock in MERGE or Update? [message #146947 is a reply to message #146926] |
Mon, 14 November 2005 18:27 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
No, there is no way to avoid locked rows in any DML - that means INSERT, UPDATE, DELETE, and MERGE. The only way to stop your MERGE statement getting locked out is to lock every row first with SELECT ... FOR UPDATE as suggested by dmitry.nikiforov. If you DO attempt to lock every row this way before submitting your MERGE, then you cannot skip over locked rows. ie. You are back in the position of having to wait for others to finish.
What you are doing is wrong. You should not have a batch process running concurrently with an OLTP application.
If a batch job MUST co-exist with an OLTP application, it must act like a robot user. Every change must be made transactionally, committing after each transaction. ie. the same way as a user. Ideally, the batch process would ideally use the same code modules as the OLTP users to manipulate tables as well.
If you cannot cope with transactional performance, then you have to separate your databases and then perform 2-way replication. However, if users and batch processes are updating the same rows, then you will get replication conflicts.
_____________
Ross Leishman
|
|
|
|
| Re: How to prevent Lock in MERGE or Update? [message #147084 is a reply to message #146947] |
Tue, 15 November 2005 13:17 |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
No, there is no way to avoid locked rows in any DML - that means INSERT, UPDATE, DELETE, and MERGE.
The only way to stop your MERGE statement getting locked out is to lock every row first with SELECT ... FOR UPDATE
as suggested by dmitry.nikiforov. If you DO attempt to lock every row this way before submitting your MERGE,
then you cannot skip over locked rows. ie. You are back in the position of having to wait for others to finish.
What you are doing is wrong. You should not have a batch process running concurrently with an OLTP application.
If a batch job MUST co-exist with an OLTP application, it must act like a robot user.
Every change must be made transactionally, committing after each transaction. ie. the same way as a user.
Ideally, the batch process would ideally use the same code modules as the OLTP users to manipulate tables as well.
If you cannot cope with transactional performance, then you have to separate your databases and then perform 2-way
replication. However, if users and batch processes are updating the same rows, then you will get replication conflicts.
You are right that there is no way to avoid locked rows in any DML.
But How can I use SELECT ... FOR UPDATE in MERGE, I tried like...
USING (select INVENTORY_ID,STOCK from INVENTORY_LOAD FOR UPDATE NOWAIT) S ==> see here
ERROR at line 4:
ORA-06550: line 3, column 68:
PL/SQL: ORA-00907: missing right parenthesis
ORA-06550: line 2, column 11:
PL/SQL: SQL Statement ignored
CODE
======
BEGIN
MERGE INTO INVENTORY T
USING (select INVENTORY_ID,STOCK from INVENTORY_LOAD FOR UPDATE NOWAIT) S ==> see here it gives me an error..ON (S.INVENTORY_ID = T.INVENTORY_ID)
WHEN MATCHED THEN
UPDATE
SET T.VERSION = '0',
T.CAT_ID = S.INVENTORY_ID,
T.STATUS = '1' ,
T.CREATION_DATE = SYSDATE,
T.STOCK = S.STOCK
WHEN NOT MATCHED THEN
INSERT
( T.INVENTORY_ID,T.VERSION,T.CAT_ID, T.STATUS,T.CREATION_DATE, T.STOCK)
VALUES
( CASE etl.merge_counter(etl.c_inserting) WHEN 0 THEN S.INVENTORY_ID END,
'0',S.INVENTORY_ID, '1',SYSDATE, S.STOCK);
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) || ' rows merged.');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(etl.get_merge_insert_count) || ' rows inserted.');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(etl.get_merge_update_count( SQL%ROWCOUNT )) || ' rows updated.');
etl.reset_counters;
commit;
END;
Thanks
|
|
|
|
| Re: How to prevent Lock in MERGE or Update? [message #147115 is a reply to message #147084] |
Tue, 15 November 2005 18:08 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The FOR UPDATE clause can only be used as part of a SELECT statement. This means that if you want to lock a row in order to update or delete it, you need to run 2 statements: a SELECT..FOR UPDATE to lock it, and an UPDATE/DELETE to follow.
The same logic applies to a MERGE. You first have to find all the matching rows, lock them individually, then run the MERGE. Only then can you be sure that the MERGE will not be locked out.
This kind of defeats the purpose of using MERGE, which packages multiple actions into a single statement. If you have to lock every row anyway, then there hardly seems any point to a MERGE.
I'd look into either getting the other users off the database, or trying the transactional Robot concept.
_____________
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Tue Apr 29 06:53:42 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
