Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
nikb
Starting Member
5 Posts |
 Posted - 2008-01-29 : 19:24:08
|
| Hi pardon my ignorance but I wonder if someone could answer a few questions for me.I am writing a program which will be used by perhaps upto 100 users at a time. The program sits on any number of PCs and loads user specific data to a given PC according to who has logged on to windows on that PC.A number of data items loaded from the user table have to be unique as they are usernames for other systems that my program simplifies access to.So when a user logs on to my program for the first time a row is created for them in the user table (indexed by a GUID and their unique network name). The other unique fields are left blank and the user is given an opportunity to fill these details in.Before writing these details to the user's row in the 'users table' the program loads the whole user table down and checks that these items are unique before committing them to that user's row in the table.The problem of course is that if between the program downloading the user table into a local datatable, checking the values are unique and then actually writing them someone else writes the same data into their row then 2 users end up with the same data - which shouldn't be allowed. i.e. 2 users can't have the same user name for the other software.How can I solve this problem with locking? Once the user table is downloaded and in a locel datatable presumably the table is no longer locked so another user could write data to the table.I acutally think this is going to be a pretty rare occourance but I still want to try to cover all eventualities.I suspect the problem is the way my program is going about the checking.Should I use an SQL insert statement like??If exists(SELECT username from users where username=@username)BEGINRAISEERROR("Username already exists")ENDELSEBEGININSERT etcIf so I guess this will simplify my code. Is this the correct thing to do? And then just trap the errors that arise if a duplicate does arise?Also some more general questions.1)I presume 2 users simultaneously looking up data from 2 different rows in a table doesn't lock the table so one search fails? I use the code below having set up a command to run a stored procedure to search for a user by their network name.Dim lclRowRet As SqlDataReaderlclRowRet = LoadUserCommand.ExecuteReader(CommandBehavior.SingleRow)lclRowRet.Read()2) I presume writing data to my user table a row at a time will also not cause a lock. I create a command object with all the row values in and then do a command.executenonquery()As a rule I close all my connections as soon as I'm done with them.Many thanks for your help in advance.nik |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2008-01-30 : 09:45:02
|
quote:
Before writing these details to the user's row in the 'users table' the program loads the whole user table down and checks that these items are unique before committing them to that user's row in the table.
Ouch!You need to do the duplicate check on the server with something like:INSERT INTO YourTableSELECT @UserName ...WHERE NOT EXISTS( SELECT * FROM YourTable WITH (UPDLOCK) WHERE UserName = @UserName) If no rows are inserted, then you know there was a duplicate.An alternative is to make sure there is an unique constraint on the username column and just trap any exceptions in your program. |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2008-01-30 : 10:04:12
|
| Two things you described need more thoughts.First of, this is not a locking issue, which has to do with concurrent control of more than one users access/modify same row of data. In your case, each user works on his/her own row. You sound like just to implement a feature that will warn user "that user name has been taken, try gain".You do not or should not "downloading the user table into a local datatable". |
|
|
|
nikb
Starting Member
5 Posts |
Posted - 2008-01-30 : 15:36:54
|
Thanks for your help - actually Hommer you are correct I do want to issue a warning like 'that user name has been taken' because the usernames should be unique and effectively if another user has that username then they're using it when they shouldn't be.Also I missed a really vital bit of info out which is that I've encrypted the username column using a bit of encryption I wrote myself. Mostly to increase security so that anyone who gains access to the user table can't match network user names to usernames from the other program. Maybe by doing that I've over complicated things as I have to download the table to decrypt each username to compare it to the new one. I think if they weren't duplicated then Ifor's way of doing it would be better.A unique constraint sounds the way to go acutally if I choose not to encrypt the usernames.Thanks very much.  |
|
|
|
nikb
Starting Member
5 Posts |
Posted - 2008-01-30 : 17:56:15
|
| I realised that using contraints to ensure unqiue column entries means that you can't have multiple nulls - so I'll use the SQL method as suggested I think.ThanksNik |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
|
|
|
a few questions on locking in sql server 2005