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 |
|
adweigert
Starting Member
22 Posts |
 Posted - 2003-09-30 : 19:34:18
|
| I need a way to ensure at the database level that a certain operation is only happening at any given time for a particular process. Table locking, right?Basically I have a table of users and a table for user authorizations...On login, I need to check the authorizations table to see if there is a current active authorization. If there is I need to de-activate it and insert a new record for activation for this user. So I have a stored procedure (work in progress) ... are my assumptions correct? will it lock the record for the user trying to login in the users table thus locking any further accesses to it until the transaction is finished?CREATE TABLE dbo.UserAuthorizations ( UserAuthorizationID UNIQUEIDENTIFIER NOT NULL, UserID UNIQUEIDENTIFIER NOT NULL, ApplicationID UNIQUEIDENTIFIER NOT NULL, DateCreated DATETIME NOT NULL, DateLastAuthorized DATETIME NOT NULL, IsValid BIT NOT NULL, CONSTRAINT [UserAuthorizations.PK] PRIMARY KEY CLUSTERED (UserAuthorizationID), CONSTRAINT [UserAuthorizations.UserID.FK] FOREIGN KEY (UserID) REFERENCES dbo.Users(UserID), CONSTRAINT [UserAuthorizations.ApplicationID.FK] FOREIGN KEY (ApplicationID) REFERENCES dbo.Applications(ApplicationID),)CREATE PROCEDURE dbo.LoginUser (@applicationID UNIQUEIDENTIFIER, @username VARCHAR(32), @password VARCHAR(16)) AS SET NOCOUNT ON DECLARE @error INT, @rowcount INT DECLARE @userID UNIQUEIDENTIFIER, @key UNIQUEIDENTIFIER IF @username IS NULL RAISERROR('Argument null: @username.', 16, 1) WITH SETERROR ELSE IF @password IS NULL RAISERROR('Argument null: @password.', 16, 2) WITH SETERROR ELSE IF @applicationID IS NULL RAISERROR('Argument null: @applicationID.', 16, 3) WITH SETERROR ELSE IF NOT EXISTS(SELECT * FROM dbo.Applications WHERE ApplicationID = @applicationID) RAISERROR('The application is not maintained by this system.', 16, 4) WITH SETERROR ELSE IF NOT EXISTS(SELECT * FROM dbo.Users WHERE Name = @username) RAISERROR('The user name "%s" does not exist.', 16, 5, @username) WITH SETERROR ELSE IF NOT EXISTS(SELECT * FROM dbo.Users WHERE Name = @username AND Password = @password) RAISERROR('The password is incorrect for the user "%s".', 16, 6, @username) WITH SETERROR IF @@ERROR <> 0 RETURN -1 SET @key = NEWID() SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION SELECT @userID = UserID FROM dbo.Users WHERE Name = @username IF EXISTS(SELECT * FROM dbo.UserAuthorizations WHERE UserID = @userID AND IsValid = 1) BEGIN UPDATE dbo.UserAuthorizations SET IsValid = 0 WHERE UserID = @userID SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT IF @error <> 0 OR @rowcount < 1 BEGIN ROLLBACK TRANSACTION RAISERROR('Could not reset the current authorization.', 16, 7) WITH SETERROR RETURN -1 END END INSERT INTO dbo.UserAuthorizations (UserAuthorizationID, UserID, ApplicationID, DateCreated, DateLastAuthorized, IsValid) VALUES (@key, @userID, @applicationID, GETDATE(), GETDATE(), 1) SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT IF @error <> 0 OR @rowcount <> 1 BEGIN ROLLBACK TRANSACTION RAISERROR('LoginUser failed for an unknown reason.', 16, 8) WITH SETERROR RETURN -1 END COMMIT TRANSACTION SELECT @key AS UserAuthroizationID, @userID AS UserID RETURN 0GO |
|
|
adweigert
Starting Member
22 Posts |
Posted - 2003-09-30 : 20:31:42
|
| Ok, I think I have it ... playing with locks ... i wanted the single user row to get locked because that should block any other incoming login requests ... (i use NOLOCK in all the other users selects to ignore this lock) ... here's the end result ... i successfully logged in as two different users without blocking issues ... but when i tried to login as the same user the second request lbocked until the first finished ...CREATE PROCEDURE dbo.LoginUser (@applicationID UNIQUEIDENTIFIER, @name VARCHAR(32), @password VARCHAR(16)) AS SET NOCOUNT ON DECLARE @error INT, @rowcount INT DECLARE @userID UNIQUEIDENTIFIER, @key UNIQUEIDENTIFIER IF @name IS NULL RAISERROR('Argument null: @name.', 16, 1) WITH SETERROR ELSE IF @password IS NULL RAISERROR('Argument null: @password.', 16, 2) WITH SETERROR ELSE IF @applicationID IS NULL RAISERROR('Argument null: @applicationID.', 16, 3) WITH SETERROR ELSE IF NOT EXISTS(SELECT * FROM dbo.Applications WHERE ApplicationID = @applicationID) RAISERROR('The application is not maintained by this system.', 16, 4) WITH SETERROR ELSE IF NOT EXISTS(SELECT * FROM dbo.Users WITH (NOLOCK) WHERE Name = @name) RAISERROR('The user name "%s" does not exist.', 16, 5, @name) WITH SETERROR ELSE IF NOT EXISTS(SELECT * FROM dbo.Users WITH (NOLOCK) WHERE Name = @name AND Password = @password) RAISERROR('The password is incorrect for the user "%s".', 16, 6, @name) WITH SETERROR IF @@ERROR <> 0 RETURN -1 SET @key = NEWID() SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION SELECT @userID = UserID FROM dbo.Users WITH (ROWLOCK) WHERE Name = @name IF EXISTS(SELECT * FROM dbo.UserAuthorizations WITH (NOLOCK) WHERE UserID = @userID AND IsValid = 1) BEGIN UPDATE dbo.UserAuthorizations SET IsValid = 0 WHERE UserID = @userID SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT IF @error <> 0 OR @rowcount < 1 BEGIN ROLLBACK TRANSACTION RAISERROR('Could not reset the current authorization.', 16, 7) WITH SETERROR RETURN -1 END END INSERT INTO dbo.UserAuthorizations (UserAuthorizationID, UserID, ApplicationID, DateCreated, DateLastAuthorized, IsValid) VALUES (@key, @userID, @applicationID, GETDATE(), GETDATE(), 1) SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT IF @error <> 0 OR @rowcount <> 1 BEGIN ROLLBACK TRANSACTION RAISERROR('LoginUser failed for an unknown reason.', 16, 8) WITH SETERROR RETURN -1 END WAITFOR DELAY '00:00:20' COMMIT TRANSACTION SELECT @key AS UserAuthroizationID, @userID AS UserID RETURN 0GO |
 |
|
|
|
|
|
|
|
Something like thread locking...