Skip to Main Content
  • Questions
  • Locking issue on self created Counter scheme in our HMIS Application

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ali Noor.

Asked: October 19, 2020 - 11:55 am UTC

Answered by: Connor McDonald - Last updated: October 29, 2020 - 2:47 am UTC

Category: PL/SQL - Version: Oracle database 12c

Viewed 100+ times

You Asked

Dear Team
We had recently upgraded our Application counter scheme ( Previously using oracle sequences) to our own created procedure to generate Next number/counter due to our requirement to generate seperate counters for our different Hospital Campuses. Every year we have to reset all sequences because our scheme is using year based counter values.

Now the issue is locking problem on our Physician Encounter where we have to generate 7-8 different counters on the same time and our procedure sometimes get locked and does not return any counter value due to locking issue

Our application user may loss data to No Counter return

We have two options of Row update, wait 1 sec and Nowait, sample code is mentioned below. can you please guide us on method close to Oracle sequence so that we can update code and overcome this locking issue. Also Please guide do you recommend to use FOR UPDATE without wait or NOWAIT option in SQL ?

    IF NVL(P_LOCK_WAIT, 'N') = 'Y' THEN
      SELECT ROWID
        INTO P_ROWID
        FROM COUNTERS.SYSTEM_COUNTERS_VALUES T
       WHERE T.COUNTER_ID = P_COUNTER_ID
         AND T.SERIAL_NO = P_SERIAL_NO
         FOR UPDATE WAIT 1;
    ELSE
      SELECT ROWID
        INTO P_ROWID
        FROM COUNTERS.SYSTEM_COUNTERS_VALUES T
       WHERE T.COUNTER_ID = P_COUNTER_ID
         AND T.SERIAL_NO = P_SERIAL_NO
         FOR UPDATE NOWAIT;
    END IF


and we said...

How many different campuses do you have ?

Could you not just have a sequence per campus? True you might need a little dynamic SQL to get the right sequence value, but that seems a far easier solution to implement

and you rated our response

  (4 ratings)

Reviews

Self created counter scheme

October 27, 2020 - 8:39 am UTC

Reviewer: Ali Noor from Lahore, Pakistan

Thanks for your comments

We have almost 350 plus different sites and locations. 3 Major campuses and almost 300 + collection points that are using our online application

Sequence for each campus and collection point will not be possible to manage.

The main issue we are facing is while we are loading lot of data using batches.
We have replaced NOWAIT to WAIT 2 seconds on all of our counters scheme, that helps a bit but it does not eliminate at all. Actually we want to mirror our counters like Oracle Sequence where User cannot experience wait and NO VALUE RETURN issue due to locking

I hope you understand our problem and suggest us better
Connor McDonald

Followup  

October 28, 2020 - 3:27 am UTC

If you really want gap-free arbitrary named sequences then you could do something like this:

SQL> create table seqs
  2    ( name   varchar2(100),
  3      constraint seqs_pk primary key ( name )
  4    )
  5  organization index
  6  /

Table created.

SQL>
SQL> insert into seqs
  2  select 'SEQ'||rownum
  3  from dual
  4  connect by level <= 5;

5 rows created.

SQL>
SQL> create table seqs_numbers
  2    ( name   varchar2(100),
  3      num    number(10),
  4      state  varchar2(10),
  5      constraint seqs_numbers_pk primary key ( name,num ),
  6      constraint seqs_numbers_fk foreign key ( name) references seqs ( name ) on delete cascade,
  7      constraint seqs_numbers_ck check ( state in ('free','used'))
  8    )
  9  organization index
 10  /

Table created.

SQL>
SQL> insert into seqs_numbers
  2  select s.name, num, 'free'
  3  from   seqs s,
  4         ( select level num from dual
  5           connect by level <= 1000 );

5000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create or replace
  2  function get_seq(p_name varchar2) return number is
  3    l_seq number;
  4    cursor next_seq is
  5      select num
  6      from   seqs_numbers
  7      where  name = p_name
  8      order by num
  9      for update skip locked;
 10  begin
 11    open next_seq;
 12    fetch next_seq into l_seq;
 13    close next_seq;
 14    update seqs_numbers
 15    set    state = 'used'
 16    where  name = p_name
 17    and    num = l_seq;
 18
 19    return l_seq;
 20  end;
 21  /

Function created.



As you request a sequence number you'll have just that number locked until you commit, but other sessions can request a value from the same sequence and they'll get a new number. If the first session fails/rolls back etc, then that number will go back into the pool of available numbers.

Note that this means its possible for sequences to be used slightly out of order.

Autonomous transaction

October 27, 2020 - 4:03 pm UTC

Reviewer: Kilson Araujo from Santos, SP - Brazil

Ali Noor,

You need to encapsulate your sequence generator code in an autonomous transaction function/package and commit the update as soon as possible to keep the row lock at minimum.

Also, as you said this is being used in a batch program, you may want to introduce some code to allow to cache sequence values and reduce updates on the table holding the last value, ie:

FUNCTION getnextvalue (p_seq_name IN VARCHAR2(30), p_increment IN NUMBER) RETURN NUMBER IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_nextval NUMBER;
BEGIN
UPDATE sequence_table SET nextval = nextval + p_increment WHERE seq_name = p_seq_name
RETURNING nextval into v_nextval;
COMMIT;
RETURN v_nextval;
END getnextvalue;

(this is a very simple example and I have not tested. Just to give you an idea)

Let's say you know your batch program will use at least 100 sequence values. Then call GetNextValue(seqname, 100) and the function will increment the sequence value by 100. Now you can safely use the range of 100 values that were just incremented.

This approach avoided 99 updates (thus row lock) on the table that maintains the sequence.

Hope it helps.

- Kilson Araujo
Connor McDonald

Followup  

October 28, 2020 - 2:59 am UTC

Yes but now you have potential gaps because I might request a sequence and then roll back the main transaction (eg it might fail for some reason).

And if you allow gaps, then you may as well put sequences back in the mix.

I'd argue they could be managed

October 28, 2020 - 1:34 pm UTC

Reviewer: Paul from Canada

I didn't see a "gap free" requirement.
Do the different sites use different users to access the application?
Could you use 350 sequences. you only create them once,
reset them annually. That can be scripted.

If the sites all connect with different users when creating that user create a synonym for their respective sequence so the code remains the same and you don't need anything to retrieve the name, otherwise some dynamic SQL to find it.

Far easier overall than trying to mess with locking.
Sequence maintenance happens once a year, locking happens ALL the time. You will lose more time waiting on locks than you will running the reset values once a year.



Connor McDonald

Followup  

October 29, 2020 - 2:46 am UTC

I agree. I've never understood the hesitance about sequences, gaps etc etc

Maintenance free + no locking

October 28, 2020 - 5:09 pm UTC

Reviewer: Kilson Araujo from Santos, SP - Brazil

I didn't see any "gap free" requirement either.

Yes, I'm in favor to use sequences whenever possible.

I was assuming that the sequence identifier generator would require some sort of logic behind its generation (ie. year + campus + location + sequence).

Although creating 350+ sequences is no big deal, it would require system maintenance when new locations are created and also to reset it early.

The autonomous transaction pl/sql package would allow him to introduce complex logic on the identifier generator, while addressing the lock issue.

Here is a sample code that would generate a sequence based on year, campus, location and would require no yearly maintenance nor when new locations are created:

SQL>
SQL> create table x
  2    ( seq_year            number,
  3      campus_id           number,
  4      location_id         number,
  5      current_value       number,
  6      constraint x_pk primary key ( seq_year, campus_id, location_id )
  7  )
  8  organization index
  9  /

Table created.

SQL>
SQL> -- create the package spec
SQL> CREATE OR REPLACE PACKAGE x_pkg AS
  2     FUNCTION getnextvalue (p_seq_year IN number, p_campus_id IN number, p_location_id IN number, p_increment IN NUMBER) RETURN NUMBER;
  3  END x_pkg;
  4  /

Package created.

SQL> -- create the package body
SQL> CREATE OR REPLACE PACKAGE BODY x_pkg AS
  2    FUNCTION getnextvalue(p_seq_year IN number, p_campus_id IN number, p_location_id IN number, p_increment IN NUMBER) RETURN NUMBER IS
  3      PRAGMA AUTONOMOUS_TRANSACTION;
  4
  5      v_nextval NUMBER;
  6    BEGIN
  7      UPDATE x SET current_value = current_value + p_increment
  8      WHERE seq_year  = p_seq_year
  9      AND   campus_id = p_campus_id
 10      AND   location_id = p_location_id
 11      RETURNING current_value into v_nextval;
 12
 13      -- if no year, campus, collection location found, then insert
 14      IF SQL%ROWCOUNT = 0 THEN
 15        INSERT INTO x (seq_year, campus_id, location_id, current_value)
 16        VALUES (p_seq_year, p_campus_id, p_location_id, 1);
 17        v_nextval := 1;
 18      END IF;
 19      COMMIT;
 20      RETURN v_nextval;
 21    END getnextvalue;
 22  END x_pkg;
 23  /

Package body created.

SQL>
SQL> select x_pkg.getnextvalue(2020, 1, 1, 1) from dual connect by level <= 10
  2  /

X_PKG.GETNEXTVALUE(2020,1,1,1)
------------------------------
                             1
                             2
                             3
                             4
                             5
                             6
                             7
                             8
                             9
                            10

10 rows selected.

SQL>

- Kilson Araujo

Connor McDonald

Followup  

October 29, 2020 - 2:47 am UTC

nice stuff

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database