November 16, 2008 at 10:26 pm
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
November 16, 2008 at 10:57 pm
Why do you want to manage locking in the app?
SQL takes locks automatically in the required mode and with the best possible granularity for each statement, nad it will release locks automatically as soon as they aren't needed any longer.
In your example, by the time you run the select, the update has long completed. Also, the select is been run in the same session as the update and you can always view rows that you have locked.
If SQL needs to read a row and that row is locked for update, it won't skip that row and return the rest. It will wait until the lock is complete. If the lock doesn't get released, it will wait forever.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 16, 2008 at 11:23 pm
SQL Server locks and unlocks the records for you, so you don’t really have to do it in your code. The subject of locking is to long and complicated to be covered in details in a forum post, so I do recommend that you’ll read some articles about the subject and don’t relay on posts to the forum only.
When you work with the default isolation level (read committed) SQL server locks the records. For simplicity I’ll assume that there are only 2 types of locks – shared lock and exclusive lock (in reality there are more types of locks). A record can have many shared locks that were issued by many users. Each time that you issue a select statement the server uses a shared lock to lock the records. When you are trying to modify a record, the server is locking the records with an exclusive lock. Only one exclusive lock can be issued on any record. The record that has an exclusive lock can not have shared lock as well. Shared lock will be released after the server read the records that were locked (in other words after the select statement finished running, all locks will be released). Exclusive locks will be released only when the transaction completes with commit or roll back statements. If you modified the data without a transaction, then the exclusive locks will be released as soon as the server completes the statement. The locking is done when you submit the query to the server, so in your application, if you are selecting the records first, the server will use a shared lock, by the time that your client will get the record to the GUI, the select statement finishes to run, so the shared locks are released. At that time your user is editing the records on his machine using the GUI. The server will lock the records that the user updating only when the user sends the modifications to the server. In most controls when I edit the record on my screen, the editing is done only on my copy of the records, and I have to do something in order to send the modification to the server. This means that while I’m modifying the record, the server doesn’t know about it, so it won’t lock the records. Only after I move to a different record or hit an update button, the modifications are sent to the server and at that time the records are locked (for the duration of the modification only). Since most of the time the modification is very fast, the duration of the lock is very small.
I just want to emphasize that the explanation above is very simple, without many important details and is true only for read committed isolation level. As I wrote before the subject is to long and complicated to cover in a forum post. I recommend that you’ll read some articles about the subject and especially about pessimistic and optimistic locking.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply