VB.NET 2003, SQL Svr 2k5, Dataset + Rowlock

[Dominic Belley]

Hi,

I use VB.NET 2003 & SQL Server 2005.
I will try to make it simple.


I have a winform which has a textbox "Member No".
When the user enters a value and clicks the Search button, I call a stored
proc (SELECT) and fill a dataset with a dataadapter.

Then about 20 to 25 other textboxes in that winform get populated because
they are bound to that dataset.

After that is done, the user takes the time to make changes on the screen
and clicks on the SAVE button to save.

That calls the updatecommand of the dataadapter which is another stored
proc. (UPDATE)

--

That all works fine.

BUT ..

I need to add locking features to that to avoid having 2 users modifying the
same "member data" at the same time.

--
I read stuff on many website, but its never very clear and I can't find a
good simple snippet of code.
--

Here is what I have to do ..

1- Make my SELECT with my stored proc
2- ... let the user do his changes while the row is locked to others ...
3- Make my UPDATE with my stored proc
4- Unlock the data to others

....

I tried many things .. it doesn't work.

Does anyone have a clear example of this ? Using VB.NET .. a dataadaptor
with a dataset ..

Or should I stop using a dataset to complete these operation? ..

I so need your help on this.

Thanks a lot !!

 

[Guest]

Dominic,

Are you currently using optimistic locking and you find that insufficient?
Or are you currently not using any locking?

I would definitely try optimistic locking before turning to the pessimistic
locking that you are asking about.

Kerry Moorman

 

[Dominic Belley]

Pessimistic, optimistic .. how do you define that ? I used to see these
terms in VB6 and recordsets.. but ADO.NET is what I use.
----

This is the code I use to select my data for my test.:

Dim sTrans As SqlClient.SqlTransaction
Dim sqlReader As SqlClient.SqlDataReader
Dim sCommand As New SqlClient.SqlCommand

Try

sTrans = _myDataBase.SQLConnection.BeginTransaction()
sCommand.Connection = _myDataBase.SQLConnection
sCommand.Transaction = sTrans

sCommand.CommandText = "Select memnopermis, memnom from Membre
with (holdlock, rowlock) where memnopermis = '3'"
sqlReader = sCommand.ExecuteReader()

Do While sqlReader.Read() = True
txtPermis.Text = sqlReader.GetInt32(0).ToString
txtNoMembre.Text = txtPermis.Text
txtNom.Text = sqlReader.GetString(1).ToString()
Loop
sqlReader.Close()

Catch ex As Exception

sTrans.Rollback()

End Try

---------

'This is my update statement that tries to save the selected record
sCommand.CommandText = "update membre set memnom = 'toto2' where
memnopermis ='3'"
sCommand.ExecuteNonQuery()

-----------

If I start 2 instances of that application they both read the data using the
code above. Great.
If in the 2nd application I try to save the record selected, it doesn't work
... it falls into a timeout because the 1st application is locking the data
... That's fair.

BUT ... Is there a way to know if the data is locked by another user when I
select it ?

Modifying a member profil can take up to 10 minutes .. i don't want to have
a user selecting the data .. doing all the changes and realizing when its
time to save that he can't because that member data is locked and that he
will have to refresh to redo he work before saving .. and many he will get
the same situation on 2nd save ..

NO !!

When I select a row, I lock it ... but how do I know when selecting a row
that it is locked already and I might not be able to save?

Thanks.

 

[Guest]

Dominic,

Sorry, I misunderstood your original question?

In this case, have you tried using WITH (UPDLOCK) on your Select statement
instead of WITH (HOLDLOCK)?

Kerry Moorman

 

[Dominic Belley]

Oh !!
Thanks a lot.

I tried with the UPDLOCK. If I join this with a NOWAIT, I instantly get my
error message telling me its locked. That's great.

One more thing I tested is the ROWLOCK keyword. I didn't use it and its
still locked only the row I wanted. It this keyword really important?

------
select memnopermis, memnom
from membre with (UPDLOCK,ROWLOCK,NOWAIT)
where memnopermis = '4'
-------
or
-------
select memnopermis, memnom
from membre with (UPDLOCK,NOWAIT)
where memnopermis = '4'
-------

it act the same.

 

[Guest]

Dominic,

I think that SQL Server starts out with ROWLOCK as the default and then
moves to other locking schemes as needed.

For code that only updates a single row based on the primary key, I don't
think it matters whether you specify the ROWLOCK hint or not.

Kerry Moorman