locking / commit / select for update problem

From: Vincent verpoort <vincent.verpoort_at_gmail.com>
Date: Thu, 15 Jun 2006 16:24:49 +0200
Message-ID: <9b68098b0606150724r1ec97acl7e1f3a210e981743@mail.gmail.com>

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.

-- 
Ron van Zijl <rzijl_at_corp.home.nl> <rzijl_at_corp.home.nl>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 15 2006 - 09:24:49 CDT