-
Hello,
If two sessions execute the following procedure at the same micro-second, will one of them be "willing to wait"? If not, how does one code a willing-to-wait lock?
CREATE OR REPLACE PROCEDURE
get_next_number_reference (IN_requested_type in out char, OUT_result_out in out number) AS
--
CURSOR c1 IS
SELECT big_sequence_nbr + 1
FROM number_reference
WHERE record_id = IN_requested_type FOR UPDATE;
BEGIN
OPEN C1;
FETCH C1 INTO OUT_result_out;
UPDATE number_reference
SET big_sequence_nbr = OUT_result_out
WHERE CURRENT OF c1;
CLOSE c1;
COMMIT;
END get_next_number_number;
We originally did not go with sequences because of advanced replication (but we have worked that out).
-
All locking, by default, is what you call 'willing to wait'.
If you don't want to wait for a lock, you must explicitely add NO_WAIT (or something like that) to your statement.
However, as far as sequence-replacing code goes, you should note that your COMMIT may be problematic. Obviously, you will not be able to call this in the middle of a transaction because of the COMMIT. To alleviate this, you should look into autonomous transactions.
However, if you've fixed your sequence problems, you should really go back to sequences because they are faster.
If not, you should at least use NDS instead of CURSORs because NDS is faster than CURSORs, although still slower than sequences.
HTH,
- Chris
-
Thanks Chris,
I immediately implemented the AUTONOMOUS_TRANSACTION.
The problem that we had with sequences is with Advanced Replication. We fixed that in some cases with:
Start 1 increment by 2 at site1 (odd keys)
Start 2 increment by 2 at site2 (even keys)
but order number mattered in some PK cases.
-----
But what is NDS? - I could not find in doc.
-
NDS = Native Dynamic SQL.
Actually, however, you don't need either. My bad - you don't have any dynamic SQL.
You should just use the returning clause in your case:
Code:
CREATE OR REPLACE PROCEDURE
GET_NEXT_NUMBER_REFERENCE
(
IN_REQUESTED_TYPE IN OUT CHAR,
OUT_RESULT_OUT IN OUT NUMBER
) AS
BEGIN
UPDATE
NUMBER_REFERENCE
SET
BIG_SEQUENCE_NBR = BIG_SEQUENCE_NBR + 1
WHERE
RECORD_ID = IN_REQUESTED_TYPE
RETURNING
BIG_SEQUENCE_NBR
INTO
OUT_RESULT_OUT ;
COMMIT;
END GET_NEXT_NUMBER_REFERENCE;
HTH,
- Chris
