Hi Vincent

I can't reproduce your issue. I tried in my sesisons, different session but works consistently as per your design..

Can you tell me how many rows you have with this conditionr.ipra_id_parent = 5121263 ?

DB version : 10.1.0.4

Thanks

Riyaj Shamsudeen

Vincent verpoort wrote:

Hi everyone 

First post with a big HMMMMMMMMMMMMM ? value 

If anyone know how this can happen please do tell 


Case description

We have a process that has to pick a unique IP address from a pool.

There will be multiple processes using this IP address module and
it is absolutely forbidden to give out the same IP to more than one

module. 

To prevent this we make (very dirty) use of the V$SESSION in memory

table together with SELECT .. FOR UPDATE row level locking.

In this case we will start with the following table 

[IPM_IP_RANGES]

| IPRA_ID | IPRA_ID_PARENT | SUBNET_BINARY | SUBNET_LVL | ASSIGNEE_ID |

| 1       | 8              | 101           | 1          | NULL        |
| 2       | 8              | 110           | 1          | NULL        |

| 3       | 8              | 111           | 1          | NULL        |

| 4       | 8              | 001           | 1          | NULL        |
| 5       | 8              | 010           | 1          | NULL        |

| 6       | 8              | 000           | 1          | NULL        |

| 7       | 8              | 1000          | 1          | NULL        |
| 8       | 8              | 1001          | 1          | NULL        |

-----------------------------------------------------------------------


And the transaction done in the GET_IP Module/script will be

col subnet_binary format a40
set timing off time off echo off


START TRANSACTION
=======================================================================


BEGIN

-- First blank the session module + action
DBMS_APPLICATION_INFO.SET_MODULE('IPMASSIGNMENT', NULL)


SELECT a.ipra_id ,a.subnet_binary, a.subnet_level
INTO   var_IPRA_ID, var_SUBNET_BIN, var_SUBNET_LVL

FROM   ipm_ip_ranges a
WHERE  a.ipra_id IN (select r.ipra_id
                     from ipm_ip_ranges r
                     where 
r.ipra_id_parent = 5121263
                     and r.assignee_id is null

                     and rownum < 20
                     and not exists (select 1 
                                     from   v$session s

                                     where  s.module = 'IPMASSIGNMENT'

                                     and    s.action =
to_char(r.ipra_id)
                                     and    s.username = user )

                    )
AND rownum < 2
for update skip locked;


-- Make sure that this IPRA_ID is blocked for other processes
DBMS_APPLICATION_INFO.SET_MODULE('IPMASSIGNMENT', TO_CHAR(var_IPRA_ID));


-- Do some application magic (not listed)

-- Finally update the ipm_ip_ranges table 

UPDATE IPM_IP_RANGES
SET    assignee_id = 'SOMEUSERID'
WHERE  ipra_id     = var_IPRA_ID;

COMMIT;

END;


=========================================================================
END TRANSACTION


The number 20 for the rownum in the inside query is to prevent selection
of to many rows and is basically
fixed on the maximum number of thread running + 2.


So the inside query will result in a list of max 20 ip's which are not

locked by another process. From this list
we will attempt to lock just 1 record/row using the skipped locked to
absolutely prevent any situation where a 

record is locked by the select for update but not has it's

dbms_application_info call yet.

Running the process as listed works fine. Each sql session will get a
unique ip. So we would see the following :


SELECT ... LOCKED; + the DBMS_APPLICATION_INFO call


   IPRA_ID SUBNET_BINARY                            SUBNET_LEVEL
========== ======================================== ============
         1 101                                                 1


select module, action from v$session where module = 'IPMASSIGNMENT';


MODULE                                           ACTION
================================================
================================

IPMASSIGNMENT                                    1

Other sessions will correctly not see the row for this IPRA_ID.


The strange behavior starts after we change the data and commit. 

UPDATE IPM_IP_RANGES

SET    assignee_id = 'USER10'
WHERE  ipra_id     = '1';

COMMIT;

Oracle knowledge and documentation states that after a commit all locks

are released. So while I am in the same session (no disconnect !) 

a next run of the SELECT ... FOR UPDATE statement would logically result
in a new IP. The record we just changed is excluded by the select as

it's assignee_id is no longer null and additionally also because the

DBMS_APPLICATION_INFO still lists the IPRA_ID.

So logic dictates that with the locks being released we would start
another consistent read, execute the statement and fetch another 

(non-blocked) record.


What we get is :

no rows selected

If we change the select by increasing the latter rownum predicate like :
 
SELECT a.ipra_id ,a.subnet_binary, a.subnet_level
INTO   var_IPRA_ID, var_SUBNET_BIN, var_SUBNET_LVL


FROM   ipm_ip_ranges a
WHERE  a.ipra_id IN (select r.ipra_id
                     from ipm_ip_ranges r
                     where r.ipra_id_parent = 5121263
                     and r.assignee_id is null


                     and rownum < 20
                     and not exists (select 1 
                                     from   v$session s
                                     where  s.module = 'IPMASSIGNMENT'


                                     and    s.action =
to_char(r.ipra_id)
                                     and    s.username = user )
                    )
AND rownum < 3                 -- <- This was 2


for update skip locked;

the result is 

   IPRA_ID SUBNET_BINARY                            SUBNET_LEVEL
========== ======================================== ============
         2 00001111111111111111111111111011                   32



Why ?

It appears that the SQL*Plus session buffers the resultset and does not
initiate the a new CONSISTENT read. 


  

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.