How to lock a SQL server 2008 table while I do a Select followed by Insert command

Specify with parameter

Example

USE AdventureWorks2008R2 ;
GO
BEGIN TRAN
SELECT COUNT(*) 
FROM HumanResources.Employee WITH (TABLOCK, HOLDLOCK) ;

Select allOpen in new window

Read More @ http://msdn.microsoft.com/en-us/library/ms177634.aspx

The following kind of locks are available in SQL Server which you can apply with SELECT, INSERT, UPDATE, and DELETE statements:
HOLDLOCK: Hold a shared lock until completion of the transaction instead of releasing the lock as soon as the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE.
NOLOCK: Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
PAGLOCK: Use page locks where a single table lock would usually be taken.
READCOMMITTED: Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server 2000 operates at this isolation level.
READPAST: Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
READUNCOMMITTED: Equivalent to NOLOCK.
REPEATABLEREAD: Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.
ROWLOCK: Use row-level locks instead of the coarser-grained page- and table-level locks.
SERIALIZABLE: Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.
TABLOCK: Use a table lock instead of the finer-grained row- or page-level locks. SQL Server holds this lock until the end of the statement. However, if you also specify HOLDLOCK, the lock is held until the end of the transaction.
TABLOCKX: Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.
UPDLOCK: Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.
XLOCK: Use an exclusive lock that will be held until the end of the transaction on all data processed by the statement. This lock can be specified with either PAGLOCK or TABLOCK, in which case the exclusive lock applies to the appropriate level of granularity.

Regards,

you should use transaction. check this link: http://msdn.microsoft.com/en-us/library/ms188929.aspx

Find more information on Locks @ http://msdn.microsoft.com/en-us/library/ms187373.aspx

Hello,

Perhaps it is better if I explain using an actual example. Attached are 2 SQL statements. I want the lock to occur at the Select statement and unlock to occur after the insert.

' First do the Select statement. I want the lock to happen here
'
SqlString = "SELECT TOP 1 MyTable.ID " &_
            "FROM MyTable " &_
            "Order By MyTable.ID Desc"  

Set rs = oConn.Execute(SqlString)
IF NOT rs.EOF
   NewID = rs("ID") + 1
END IF
rs.Close
set rs=nothing

' Now do the Insert. I want the table unlocked when this is complete
'
SqlString = "INSERT INTO MyTable " &_ 
            "   (MyTable.ID) " &_
            "VALUES " &_
            "   (" & NewID & ")"

oConn.Execute SqlString

' So at this point the table should be unlocked

Select allOpen in new window

Okie let me ask is there any thing that would stop you from clubbing these two statements within a BEGIN.. END clause?

I don't know of anything to stop me surrounding the statements by BEGIN and END.

Can you pleae confirm how the syntax would work? I have attached how I understand it should be.

Are you saying that the BEGIN TRAN would actually lock the table I am selecting from and inserting into?

SqlString = "BEGIN TRAN"
oConn.Execute SqlString

' My SELECT and INSERT statments

SqlString = "END TRAN"
oConn.Execute SqlString

Select allOpen in new window

No, you would not able to specify the similar types of locks but looking at the objective of generating a new id, the chances are very remote of having a duplicate id generated.


The highest level of locking is to SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

it specifies the following

Specifies the following:

Statements cannot read data that has been modified but not yet committed by other transactions.

No other transactions can modify data that has been read by the current transaction until the current transaction completes.

Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.




Read more @ http://msdn.microsoft.com/en-us/library/ms189857.aspx
http://msdn.microsoft.com/en-us/library/ms173763.aspx

OK using my example in which I SELECT the Top ID, my concern is that another process could do a select statement at the same time and read the same ID. Hence both process could insert the same ID value. Surely there is a danger of this happening.

Here is the solution for your requirement:

insert into MyTable WITH( TABLOCKX ) (MyId)  select MAX(src.myid)+1  from MyTable as src

Select allOpen in new window

This will ensure the entire activity happens in single transaction and allows you lock the table....


Hope this should address your concern.

Regards,

Any updates?
Has your issue been resolved?

I will be trying it out for real today and get back to you. Initially I am not sure how I am able to read the ID. It appears to work out the top number and insert it. But I also need to read the value as well.

If you need to read this generated id as well and you may have thousands of users possibly trying to use this command.

Then the possible solution is to add a GUID column in your table and then your statement changes to something like

insert into MyTable WITH( TABLOCKX ) (MyId, GuiIDColumn)  select MAX(src.myid)+1, <pass guid>  from MyTable as src

Select allOpen in new window

and then perform query

select MyId from MyTable where GuiDColumn = <pass guid>

Select allOpen in new window

Code for generating GUID in ASP

<%@LANGUAGE="VBSCRIPT"%>
<HTML>
<BODY>
<%

Response.Write "<P>GUID = " & CreateWindowsGUID()

Function CreateWindowsGUID()
  CreateWindowsGUID = CreateGUID(8) & "-" & _
    CreateGUID(4) & "-" & _
    CreateGUID(4) & "-" & _
    CreateGUID(4) & "-" & _
    CreateGUID(12)
End Function

Function CreateGUID(tmpLength)
  Randomize Timer
  Dim tmpCounter,tmpGUID
  Const strValid = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
  For tmpCounter = 1 To tmpLength
    tmpGUID = tmpGUID & Mid(strValid, Int(Rnd(1) * Len(strValid)) + 1, 1)
  Next
  CreateGUID = tmpGUID
End Function
%>
</BODY>
</HTML>

Select allOpen in new window

This should now finally resolve your query.

Regards,

Sorry I think you may have misunderstood my query.

What I want is to create a row in a table but set the ID to be one higher than the current highest ID and then be able to read this ID.

So for example if MyTable has many rows and the highest ID value is 56, I want to be able to create a new row with ID 57. I then want to read this ID at the same time so I can create other table rows in different tales that reference this ID.

Ok I see what you are saying. So basically I need another temporary column in the table which I can use as a unique key to get to the generated ID. I will give this a try and let you know.