We are running a 184.108.40.206 database where we get this behavior
tomorrow will run it on 10.1.0.2 to see what it does there
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:
First post with a big HMMMMMMMMMMMMM ? value
If anyone know how this can happen please do tell
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
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
| 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 =======================================================================
-- 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;
========================================================================= 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 ================================================ ================================
Other sessions will correctly not see the row for this IPRA_ID.
The strange behavior starts after we change the data and commit.
SET assignee_id = 'USER10' WHERE ipra_id = '1';
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
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.
-- Vincent Verpoort
,.-~`"'~-.,_,.-~`"'~-.,_,.-~`"'~-.,_,.-~`"'~-.,_,.-~`"'~-.,_ Communiceren is begrepen worden ^*<-._,.->*^*<-._,.->*^*<-._,.->*^*<-._,.->*^*<-._,.->*^*<-.