Thursday, May 28, 2009

Resuming transmission...

I've been busy lately: preparing papers and presentations for the upcoming ODTUG, presenting at Hotsos-revisited, and presenting at the Dutch DBA-symposium. All spare time went into these activities, and the Helsinki blog just had to wait in line. But now I intend to resume transmission over here again.

A couple of weeks ago I received following comment on this blog:
toon,

Suppose we have a registration form on our web site, and the web guys want to check if the username supplied in the form is already in use.

This would be a stored procedure in my UI-api, right ? (one in parameter and a return value to represent true or false)

The quick answer was, of course, yes. But then I started thinking about how I would implement this. We would like to have a robust solution, and for this there are a few things you need to be aware of.
  1. There is some time in between the ‘in-use check’ and the final ‘user registration is executed’ moment. Other users could be checking for the same username during this time.
  2. We are dealing with (stateless) browser users. They can just stop their “user registration” process by closing the browser or moving on to another url.
  3. You need to cater for concurrency.
After some careful thinking here is the outline of a “start” solution as I would have designed it.

Obviously there would be some users table that holds already registered (i.e. in-use) usernames.
create table web_user
(userid number not null
,username varchar2(30) not null check(username = upper(username))
,...more columns...
,primary key (userid)
,unique(username)
);
In addition to that I would introduce a table to hold “in-use check” calls.
create table web_user_inuse_check
(sessionid varchar2(...) not null
,username varchar2(30) not null check(username = upper(username))
,checktime date not null
,primary key (sessionid)
,unique (username)
);
The meaning of this table would be: “username [username] was found to be not in use when checked by session [sessionid] at [checktime].” The sessionid would likely map to the value of the http sesssion cookie that is maintained on the middle tier for every connected browser.

This design would have an additional constraint as follows (specified as an SQL assertion):
create assertion no_overlap as
check(not exists (select username from web_user
intersect
select username from web_user_inuse_check));
Which would be implemented separately using triggers on both tables (not shown in this post).

Next, two procedures would be involved: one for the “inuse” check, and one for the actual creation of a “not-yet-used” username. (in pseudo code)
procedure p_check_already_used
(p_username in varchar2
,p_sessionid in varchar2
,p_inuse out varchar2) as
begin
--
-- Serialize calls to this procedure.
--
Appropriate dbms_lock call to serialize *per username*.
Will raise exception if lock could not be acquired.
--
-- Possible scenarios:
-- 1) username already in use.
-- 2) username not in use and also no inuse_check available.
-- 3) username not in use and inuse_check available by other session.
-- 3a) inuse_check is more than 15 minutes ago.
-- 3b) inuse_check is 15 minutes or less ago.
-- 4) username not in use and inuse_check available by same session.
--
If [scenario 1]
Then p_inuse := ‘YES’
Elsif [scenario 2]
Then create (using merge on sessionid) inuse_check row for this session;
P_inuse := ‘NO’;
Elsif [scenario 3a]
Then delete inuse_check row of other session;
Create (merge) inuse_check row for this session;
P_inuse := ‘NO’;
Elsif [scenario 3b]
Then p_inuse := ‘YES’;
Elsif [scenario 4]
Then P_inuse := ‘NO’;
End if;
--
-- Commit transaction (also releases dbms_lock lock).
--
Commit;
--
Exception when “lock could not be acquired”
--
P_inuse := ‘YES’;
--
end;
And finally the user-creation procedure.
procedure p_create_user
(p_sessionid in varchar2
,p_username in varchar2
,other user data) as
Begin
--
If “Our (sessionid!) inuse_check row has disappeared”
Then -- It took us more than 15 minutes to fill in the form, and
-- some other session has claimed this username now.
--
-- Quickly check if we can claim it again.
--
If p_check_already_used = ‘YES’
Then error
End if;
End if;
--
-- Now register our user.
--
Delete our inuse_check row;
Insert web_user row;
Commit;
--
End;
I know this may seem an overly complex solution. And I would probably check with the customer first whether dealing with the “concurrency scenarios” is really necessary or not.

Final remark: I would put both procedures inside a package (BL-code), and create two stub procedures on top of the package (UI API’s) which would be granted (execute) to the schema used by the yafet to connect to the DBMS.