Implementing Table or Row Locking

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

SQL Server Development (2000)

Implementing Table or Row Locking

Author

Topic

Aiby
Yak Posting Veteran

71 Posts

Posted - 2010-01-07 : 12:36:33

CREATE PROCEDURE [spSaveSales]
@intLocationID_2 Int,
@dtDate_8 SmallDateTime,
@fltAmount_9 Numeric (16,2)
@intID_1 [BigInt] Output
As
Begin
Begin Transaction
SELECT @intID_1 = Isnull( Max( intID ) + 1 , 1 ) FROM tblSales WHERE intLocationID = @intLocationID_2
INSERT INTO tblSales
(
[intID],
[dtDate],
[fltAmount],
)
VALUES
( @intID_1,
@dtDate_8,
@fltAmount_9,
)
Commit Transaction
SELECT @intID_1
End

Simultaneous call from many users from an application @intID_1 is generates duplicate value ( or same value ) for more than one user. This cause a Primary Key vialotion error.

Can I specify explicit lock for Read/Write in tblSales, some how
Note:- Due to some reason I really want to avoid the intID field as Auto Increment.

Analyst Programmer
DiB P.Ltd.
Kerala, India
Email: Aiby@hotmail.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-07 : 12:43:14

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

Aiby
Yak Posting Veteran

71 Posts

Posted - 2010-01-08 : 12:00:11

quote:
Originally posted by visakh16

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

Any way, Thanks Visakh!
I tried

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

WITH (HOLDLOCK)
WITH (XLOCK)
etc.

But Non of this helps my requirement. More than 3 or 4 Users call the Procedures same time ( at test lab), they gets dead lock error.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-31 : 14:53:26

why not just modify tblSales intID to be an identity column? problem solved...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-31 : 15:02:34

If you don't want to use identity then better use a table to manage your nummbers.
Create a sp to get the next number and use it in your insert statement.

table my_numbers:
table_name varchar(255), -- the name of the table that uses this numbers
min_number int,
max_number int,
actual_number int

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources