LOCKING TYPE - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

LOCKING TYPE

Author

Topic

ilimax
Posting Yak Master

164 Posts

Posted - 2008-03-12 : 07:53:43

I have this simple stored procedure. I read about locking but I am confused which lock and how I need to use. (UPDLOCK, HOLDLOCK,etc...)

I want to lock SELECT statement, so nobody can not read till I update with new number.
Does anybody have idea which locking I need here and how I need to use locking here.

CREATE PROCEDURE NextID
@reci int OUTPUT,
@rbP int
AS
DECLARE @rb int
SET NOCOUNT ON
BEGIN
SELECT @reci = [LastNumberUsed] FROM [SupNumbers] WHERE NumberID = @rbP
SET @rb = @reci + 1
UPDATE [SupNumbers] SET [LastNumberUsed] = @rb WHERE [NumberID] = @rbP
END
GO

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-12 : 07:56:36

Do it in a single UPDATE statement.

UPDATE SupNumbers
set LastNumberUsed = LastNumberUsed + 1
Where [NumberID] = @rbP

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ilimax
Posting Yak Master

164 Posts

Posted - 2008-03-12 : 08:02:28

Good idea. Thanks.
But where is my SELECT statement ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 08:04:14

quote:
Originally posted by harsh_athalye

Do it in a single UPDATE statement.

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-12 : 08:06:58

[code]UPDATE SupNumbers
set @reci = LastNumberUsed = LastNumberUsed + 1
Where [NumberID] = @rbP[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ilimax
Posting Yak Master

164 Posts

Posted - 2008-03-12 : 08:11:09

I never think I can make on this way. Thanks.
Tell me, am I safe on this way for reading two users on same time. I want to make sure, I have uniq ID returned

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-12 : 08:13:47

Update statement places exclusive lock on the table, so there is no way at a time multiple users can run this update statement and also no user will be able to see the value, unless using READ UNCOMMITTED isolation level or NOLOCK hint.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ilimax
Posting Yak Master

164 Posts

Posted - 2008-03-12 : 08:14:33

Thank you very much

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 08:18:36

What happens if @rbp is not found?
Should the code automatically add a new entry?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 08:23:01

[code]CREATE PROCEDURE dbo.NextID
(
@reci int OUTPUT,
@rbP int
)
AS

SET NOCOUNT ON

UPDATE SupNumbers
SET @reci = LastNumberUsed = LastNumberUsed + 1
WHERE NumberID = @rbP

IF @@ROWCOUNT = 0
BEGIN
INSERT SupNumbers
(
LastNumberUsed,
NumberID
)
VALUES (
1,
@rbp
)

SET @reci = 1
END[/code]

E 12°55'05.25"
N 56°04'39.16"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-12 : 08:28:06

quote:

Originally posted by Peso

CREATE PROCEDURE dbo.NextID
(
	@reci int OUTPUT,
	@rbP int
)
AS

SET NOCOUNT ON

BEGIN TRANSACTION

UPDATE	SupNumbers
SET	@reci = LastNumberUsed = LastNumberUsed + 1
WHERE	NumberID = @rbP

IF @@ERROR <> 0
Begin
-- Error Handling stuff
End

IF @@ROWCOUNT = 0
	BEGIN
		INSERT	SupNumbers
			(
				LastNumberUsed,
				NumberID
			)
		VALUES	(
				1,
				@rbp
			)

                IF @@ERROR <> 0
                Begin
                -- Error Handling stuff
                End


		SET	@reci = 1
	END

COMMIT TRANSACTION

E 12°55'05.25"
N 56°04'39.16"

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ilimax
Posting Yak Master

164 Posts

Posted - 2008-03-13 : 14:13:27

Can you tell me how important are these two last posts?

@rbp is static number from one cell in row which is never changed. So, I do not see how could be not found.

ilimax
Posting Yak Master

164 Posts

Posted - 2008-03-21 : 14:35:12

For some reason, I had today that record was read but not updated with +1.
So, looks like this works 'SET @reci = LastNumberUsed'
But this part did not work 'LastNumberUsed = LastNumberUsed + 1'. Record was read half hour later then first one and they were same, becuase first time was not updated with +1

ilimax
Posting Yak Master

164 Posts

Posted - 2008-03-21 : 14:48:03

This is my whole procedure
CREATE PROCEDURE NextID
@reci int OUTPUT,
@rbP int
AS
SET NOCOUNT ON
BEGIN
UPDATE [SupNumbers] SET @reci =[LastNumberUsed] = [LastNumberUsed]+1 WHERE [NumberID] = @rbP
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources