Re: HowTo - create user defined sequence with proper serialization

From: <p.santos000_at_gmail.com>
Date: 29 Aug 2006 12:06:59 -0700
Message-ID: <1156878419.827642.84650@74g2000cwt.googlegroups.com>

Steve Howard wrote:
> p.santos000_at_gmail.com wrote:
> > The key thing is that if in the event that there are 2
> > people creating groups for the same customer .. it's important that one
> > blocks the other so that they both don't pull back the same value.
>
> If you use the FOR UPDATE, they shouldn't, depending on your function,
> of course. A SELECT FOR UPDATE cursor isn't even executed until the
> user with the first lock commits or performs a rollback of his
> transaction. You can test this by the following...
>
> create a table with one column, and insert one row with value of 1 into
> it and commit
> issue a select for update of that one row
> update the row to 2
> in a second session, issue a select for update against the table
> commit in the first session
> the second session should immediate get back one row with 2 as the
> value.
>
> That way, your function would be guaranteed to see and more importantly
> update, values in the table that would not be subject to "phantom"
> application updates (changing while you are assuming they are not).
>
> Regards,
>
> Steve

I just tested the FOR UPDATE and it didn't work properly. I'm not sure I followed
your example?

Here is what I'm working with.

CREATE TABLE GROUPS_T1
(ACCOUNT_ID NUMBER,

 GROUP_ID      NUMBER,
 NAME             VARCHAR2(40),
 FUNCTION      VARCHAR2(1),
 MASK             NUMBER)

/

insert into GROUPS_T1 VALUES(1,1,'GROUP 1','F',1);
insert into GROUPS_T1 VALUES(1,2,'GROUP 2','F',2);
insert into GROUPS_T1 VALUES(1,3,'GROUP 3','F',4);

commit;

/*** MY FUNCTION ***/ CREATE OR REPLACE FUNCTION GET_MASK (AID NUMBER) RETURN NUMBER IS
i NUMBER := 0;
two_power_i NUMBER := power(2,i);
curr_mask groups_t1.mask%TYPE;
CURSOR masksCursor IS
SELECT mask FROM groups_t1 WHERE account_id = aid ORDER BY mask
FOR UPDATE; BEGIN
OPEN masksCursor;
LOOP
FETCH masksCursor INTO curr_mask;
EXIT WHEN curr_mask != two_power_i OR masksCursor %NOTFOUND; i := i + 1;
two_power_i := power(2,i);
END LOOP;
CLOSE masksCursor;
RETURN two_power_i;
END;
/

/**** MY TEST PROCEDURE ***/
CREATE OR REPLACE PROCEDURE INSERT_GROUP

( p_accountid   IN GROUPS_T1.ACCOUNT_ID%TYPE,
  p_groupid     IN GROUPS_T1.GROUP_ID%TYPE,
  p_mask       OUT GROUPS_T1.MASK%TYPE

)

IS
l_mask GROUPS_T1.MASK%TYPE;

BEGIN l_mask:= get_mask(p_accountid);

INSERT INTO GROUPS_t1 VALUES(p_accountid,p_groupid,'TEST GROUPS','F',l_mask)
returning mask into p_mask;

END;
/

When I call the above procedure from session 1 ..before I commit it will
block a session 2, but in both sessions, the returned value is the same and it shouldn't be.

? Received on Tue Aug 29 2006 - 14:06:59 CDT