| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: locking / commit / select for update problem
('binary' encoding is not supported, stored as-is)
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.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jun 15 2006 - 10:41:57 CDT
 |
 |