Row level locking / Record 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

Development Tools

Other Development Tools

Row level locking / Record locking

Author

Topic

sksankararaman
Starting Member

1 Post

Posted - 2008-11-17 : 00:15:26

Hi all,

Greetings!!

If this issue is already resolved please provide me links. If this is cross posted in a different forum, please provide me link to the correct forum

I am using VB6 as front end and SQL Server 2005 as backend. I would like to implement (implicit) locking, and releasing of records in the application. I read the locking mechanisms in sql 2005 books on line and found it more confusing and not helping me with the required. I am looking at a simple mechanism of locking records for editing, performing the necessary updation and releasing the record lock. What is also needed is that when some one else attempts to work with those records then it should raise an error which can be trapped.

I am using RDORESULTSET for working with records. The VB6 code is given below, but nothing happens. The SET ISOLATION LEVEL is set at SQL SERVER default.

The following code.

Private Sub Command1_Click()
sql = "UPDATE AA with (rowlock,holdlock) SET A='A' WHERE A='C'"
db.BeginTrans
db.Execute sql
MsgBox "Success"
Dim str As String
sql = "SELECT * FROM AA (READPAST)"
Set rs = db.OpenResultset(sql)
If rs.EOF = False And rs.BOF = False Then
str = vbNullString
Do While rs.EOF = False
str = str & rs(0) & ","
rs.MoveNext
Loop
MsgBox str
End If
db.CommitTrans
End Sub

Well the result is very simple, all records including which ever is being edited is returned. Is the above due to "AUTOCOMMIT" feature in SQL server which is the default which commits statement by statement. How do i turn it off?

SET TRANSACTION ISOLOATION LEVEL (no idea what is meant for as all levels give me the same result)
SELECT ....... WITH HOLDLOCK, ROWLOCK, NOLOCK - does not yield the desired result

Can anybody help me in
a. SELECT / UPDATE / INSERT stataments which could lock the record
b. How to check the record whether it is locked or not
c. How to release the lock

Does locking differ from various versions of sql server 2005 or is it the same?

If this issue is already resolved please provide me links. If this is cross posted in a different forum, please provide me link to the correct forum

Urgent help please, Thanks again to every one for helping.

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 00:45:44

is this what you require?

http://msdn.microsoft.com/en-us/library/ms189823.aspx

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-11-17 : 04:22:38

Download and study the document

www.dowers.net/ftp/troubleshooting/SQL-TroubleshootingLocksdeadlocks.doc

====================================================
you realize you've made a mistake, take immediate steps to correct it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources