asked on 7/18/2008

DB row lock? vb.net and sql server

I have a table with a several unique numbers per product type that I want to use to autogenerate the ID for a second table. Every time the unique number is used, I will increment it. But I want to avoid users reading the same unique number so that user1 is able to create the ID and unique number incremented - at the same time user2 read the previous unique number and won't be able to create an ID in the second table. My requirements were to use some type of record lock -- Do I need some type of record lock on the unique number? Or can you suggest a design alternative for me?

-> tableUniqueNumbers:
productType uniqueNumber
widgets 40
gadgets 25

-> tableProducts
productID productName
w38 widgetX
w39 widgetY
g23 gadgetA
g24 gadgetB

user1:
- reads uniqueNumber from tableUniqueNumbers
- uniqueNumber is used to create a record in tableProducts
- uniqueNumber is incremented
If user2 reads the uniqueNumber prior to uniqueNumber being incremented - their new record in tableProducts would try to be created with a duplicate id.

Visual Basic.NET Microsoft SQL Server Web Applications

Last Comment

starship_wars

7/30/2008

ASKER CERTIFIED SOLUTION

TheMegaLoser

7/18/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

EugeneZ

7/21/2008

you can try
READPAST hint for not updating session - due to avoid beeing blocked by update
if you will use Rowlock ( that can be blocker
more:
http://msdn.microsoft.com/en-us/library/ms187373.aspx

http://www.developerfusion.co.uk/show/1688/4/

starship_wars

7/21/2008

ASKER

I'm trying aTRANSACTION, but running into a problem that I can't debug:
conn1.open()
Dim saveTrans As SqlTransaction = conn1.BeginTransaction()
daProducts.UpdateCommand.Transaction = saveTrans
daProducts.InsertCommand.Transaction = saveTrans 'just in case it's needed?
daProducts.SelectCommand.Transaction = saveTrans ' just in case
daProducts.DeleteCommand.Transaction = saveTrans ' just in case

Then I get error on daProducts.Update(dtProducts) because of:
The transaction object is not associated with the connection object
Any idea on this?

SOLUTION

EugeneZ

7/21/2008

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

starship_wars

7/21/2008

ASKER

any other ideas? I am using .net 1.1 - can't use transactionscope

starship_wars

7/21/2008

ASKER

I got a bit further by using
saveTrans = daProducts.SelectCommand.Connection.BeginTransaction()

But I'm not sure the is correct usage. I actually have several data adapters that will each update a table - but I can't use the BeginTransaction on each data adapter.
I think if I use a transaction - I want to be able to roll back updates to all these tables though, if there is any error:
daProductA.Update(dtProductA)
daProductB.Update(dtProductB)
daProductC.Update(dtProductC)

starship_wars

7/30/2008

ASKER

I split the points I think I'll use a transaction and using the transactionscope sounds like what I really need but I can't use it because of my .net version. Thank you.