Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: locking / commit / select for update problem

Re: locking / commit / select for update problem

From: Vincent verpoort <vincent.verpoort_at_gmail.com>
Date: Thu, 15 Jun 2006 18:45:35 +0200
Message-ID: <9b68098b0606150945j325488a5va2e4de92e635b771@mail.gmail.com>


We are running a 9.2.0.6 database where we get this behavior

tomorrow will run it on 10.1.0.2 to see what it does there

updates coming

On 6/15/06, Riyaj Shamsudeen <rshamsud_at_jcpenney.com> wrote:
>
> 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.
>
>
>

-- 
                           Vincent Verpoort

  ,.-~`"'~-.,_,.-~`"'~-.,_,.-~`"'~-.,_,.-~`"'~-.,_,.-~`"'~-.,_
              Communiceren is begrepen worden
^*<-._,.->*^*<-._,.->*^*<-._,.->*^*<-._,.->*^*<-._,.->*^*<-.

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 15 2006 - 11:45:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US