Tutorials to .com

Tutorials to .com » Database » Sqlserver » On the lock SQLSERVER

On the lock SQLSERVER

Print View , by: iSee ,Total views: 13 ,Word Count: 783 ,Date: Mon, 24 Aug 2009 Time: 10:07 AM

On the lock as we embark on SQL SERVER database, add, modify, delete, query time, if we are not faced with multiple users to also keep the stand-alone processing time,
Basically, we need not consider the general database, table locking, and deadlock of the type of situation, but if we are faced with a multi-user network environment for parallel processing when the table locking issues we need a more careful analysis and consideration, Otherwise, he has brought us trouble on the self-evident, and
I put the following in this matter on the problems encountered and solutions to share with everyone.
Also in my development process of such a thing:
Two users while preserving the additional data, we began to be handled this way the procedure
cn.BeginTrans
cn.Execute "insert into tableA ....."
Set rs = cn.Execute ( "select count (*) from tableA where ...")
If rs.RecordCount> 0 Then
Table A field A can not re -
cn.RollbackTrans
Else
cn.CommitTrans
End If

When the SQL SERVER in the implementation of INSERT command, if we do not add any parameters, the default application for a IX lock the database to the table A
At this time we have to analyze the above procedure, when the first user to perform cn.Execute "insert into tableA ....." Connection
Applied to the database, an IX lock to the table A, at the same time when the second user to perform cn.Execute "insert into tableA ....." Connection also to databases successfully applied for an IX lock to the table A, but the When the implementation of the
Set rs = cn.Execute ( "select count (*) from tableA where ...")
This is a time when there will be problems occur, we assume that the first step in the implementation of the first user, due to a database SELECT command will need to apply for a
S-lock to the table A, but because this time the table already exists an IX lock A, and belongs to another connection he had to wait on this. Followed by the implementation of the second user is also
Set rs = cn.Execute ( "select count (*) from tableA where ...")
He would also apply for an S-lock to the database to the table A, this time late in the application data will automatically end the connection IX lock at the same time rolling back the transaction this way, for our applications is a big failure.

Solution 1, set the parameters so that we can read the data did not submit data,

cn.BeginTrans
cn.Execute "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
cn.Execute "insert into tableA ....."
Set rs = cn.Execute ( "select count (*) from tableA where ...")
If rs.RecordCount> 0 Then
Table A field A can not re -
cn.RollbackTrans
Else
cn.CommitTrans
End If
cn.Execute "SET TRANSACTION ISOLATION LEVEL READ COMMITTED"

The solution 2, set the INSERT command parameters with (tablock),

cn.BeginTrans
cn.Execute "insert into tableA with (tablock) ....."
Set rs = cn.Execute ( "select count (*) from tableA where ...")
If rs.RecordCount> 0 Then
Table A field A can not re -
cn.RollbackTrans
Else
cn.CommitTrans
End If

The solution is added a useless Lock table,

cn.BeginTrans
cn.Execute "update tmpLockTable set FieldLock = 1"
cn.Execute "insert into tableA with (tablock) ....."
Set rs = cn.Execute ( "select count (*) from tableA where ...")
If rs.RecordCount> 0 Then
Table A field A can not re -
cn.RollbackTrans
Else
cn.CommitTrans
End If


Microsoft SQL Server Tutorial Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.