Re: Locking & concurrency - best practices

On Jan 14, 2008, at 3:54 PM, andy wrote:

> In our program we wrote the locking into the program, and created a
> modulelock table like:
>
> create table moduelock(
>   userid int,
>   module int,
>   primary key (userid, module)
> )
>
> The program then locks things before it uses them... but we also
> have pretty low contention for modules.
>
> A lock is:
> begin
> insert into modulelock...
> commit;
>
> if commit ok, then go ahead.  When we are done, delete from
> modulelock where ...

 From what I can tell, this kind of roll-your-own application level
locking system is exactly what advisory locks are for.  Search the
archives for the last couple of weeks as I remember someone posting
some really helpful functions to assist in using advisory locks.

Erik Jones

DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com