lock a column for update - 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

SQL Server Development (2000)

lock a column for update

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-02 : 08:54:12

shivaram writes "table name descrition
table name :tab_seqno
seqno number(10)
seq_type varchar2(2)

the number is used by more than 30 users at same point of time for inserting the value into another table.
So a user select the number, increments the value by one and inserts second table with that value
Seqno is then updated with new incremented value.

procedeure would look like this

Select seqno from tab_seqno where seq_type ='y' for update

begin trans
update tab_seqno set seqno =20
insert into table2-----
insert into table333------
commit trans
the problem is Select seqno from tab_seqno where seq_type ='y' for update gives error.
I need to know how to lock a column for update in select statement or other alternatives
the procedure is for multiuser envirnoment"

Nazim
A custom title

1408 Posts

Posted - 2002-04-02 : 08:59:41

i had a similar problem loooong back , nr had answered it . you can follow this two methodolgy to lock it

begin tran
update tbl set @id = nextid + 1, nextid = nextid + 1
insert rec
commit tran

or

begin tran
update tbl set nextid = nextid + 1
select @id = nextid from tbl
insert rec
commit tran

--------------------------------------------------------------

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-04-02 : 10:50:26

as you've discovered bottlenecks are a pain!!!
....and in this case the bottleneck in your application is a 1-record (???) control file....

couple of suggestions for you....might be worth considering.

1. use an automatically increasing value for seqno...ie an identity field....in the subtable...(ie the one being inserted into)

2. use a multi-record file.....with each record prefixed by a different letter of the alphabet...use some algorithm to figure out which record each user needs to use/update....should minimise by a factor of 26 the contention problem.

ie...
record a, seqno=11
record b, seqno=17
record c, seqno=38
record d, seqno=7
...
...
record z, seq=36

simple, effective....subtable key prefixed by a-11 or b-17, etc...

3. put a seqno column in the user table...each user has their own sequence number....absolutly no contention/record-locking there...unless user allowed to log on twice.

all the above presuppose that the seqno has no inherant value, other than to act as an increasing count(order) of records enterred...and/or does not need to be unique across the entire system.....(except for suggestion 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources