Prev: getting property values of a fulltext catelog
Next: SQLCMD NUMERIC FORMAT ERROR
From: Meir on 24 Mar 2010 11:23
Hello,
I have a Users table with 2 fields, UserName and Visible. The same UserName
can exist many times with Visible = 0 but just one record can exist for the
same UserName with visible = 1.

In my Stored Procedure before inserting a new visible user I check the user
doesn't exist in the table with visible = 1, but that is not enough, there
are cases when the Stored Procedure is called in the same moment with the
same parameters and there is duplicated data.

I can't define a Unique constraint because UserName with visible = 0 is not
unique, what would be the best way to approach this problem.

Thanks
Meir


CREATE TABLE [dbo].[Users](
[UserId] [int] NOT NULL,
[UserName] [nchar](10) NOT NULL,
[Visible] [bit] NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserId] ASC
)
From: Sylvain Lafontaine on 24 Mar 2010 12:23
Personally, I would use a XLOCK along a SERIALIAZABLE hints in order to
exclusively lock but I'm not really sure of what you're doing exactly. To
avoid any deadlock, you must be careful to make all your selections at the
beginning of the SP and in exactly the same order. (Not only for this SP
but also for other SP that could use the same locks on the same table(s).
Something like:

Begin Transaction

If Not Exists (Select * from Users with (xlock, serializable) where UserName
= @UserName
And Visible = 1)
Begin
Insert into User (UserName, Visible) Values (@UserName, 1)
End
Else
Begin
-- Don't know what you want to do here. --
End

Commit


Even if you test for Visible=1, both the records with Visible=0 and
Visible=1 will be locked. Finally, if you have other transactions with
ReadCommitted and that you would want to block them too, see:

http://support.microsoft.com/kb/324417

http://sqlblog.com/blogs/louis_davidson/archive/2006/12/13/does-xlock-always-prevent-reads-by-others.aspx

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


"Meir" <Meir(a)discussions.microsoft.com> wrote in message
news:BBB87B74-5D12-4BC9-8B29-FCC9D560A129(a)microsoft.com...
> Hello,
> I have a Users table with 2 fields, UserName and Visible. The same
> UserName
> can exist many times with Visible = 0 but just one record can exist for
> the
> same UserName with visible = 1.
>
> In my Stored Procedure before inserting a new visible user I check the
> user
> doesn't exist in the table with visible = 1, but that is not enough, there
> are cases when the Stored Procedure is called in the same moment with the
> same parameters and there is duplicated data.
>
> I can't define a Unique constraint because UserName with visible = 0 is
> not
> unique, what would be the best way to approach this problem.
>
> Thanks
> Meir
>
>
> CREATE TABLE [dbo].[Users](
> [UserId] [int] NOT NULL,
> [UserName] [nchar](10) NOT NULL,
> [Visible] [bit] NOT NULL,
> CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
> (
> [UserId] ASC
> )


From: --CELKO-- on 24 Mar 2010 12:48
Do this in the DDL and not in code.

CREATE TABLE Users
(user_id INTEGER NOT NULL,
user_version INTEGER DEFAULT 1 NOT NULL
CHECK (user_version > 0),
PRIMARY KEY (user_id, user_version)
user_name NCHAR(10) NOT NULL,
etc.);

Now use this VIEW for the most recent row in the Users table. Much
easier than assembly language style bit flags.

CREATE VIEW CurrentUsers -- updatable!
AS
SELECT U1.user_id, etc.
FROM Users AS U1
WHERE user_version
= (SELECT MAX(U2.user_version)
FROM Users AS U2
WHERE U1.user_id = U2.user_id);

The procedures to maintain this model are easy and you should not have
any trouble writing them.

 | 
Pages: 1
Prev: getting property values of a fulltext catelog
Next: SQLCMD NUMERIC FORMAT ERROR