[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

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

Hello,

I need to connect to my SQL server 2008 database using my ASP script. I am not using stored procedures.

I would like to do a SQL Select command to select a value from a table then depending on the value I then need to do an Insert command to insert a new row. It is not relevant what the Select and Insert commands actually do as regards my question.

I would like to know how I can lock the table while I do the Select and Insert. During this period I DO NOT want the table to be read, updated or inserted. After my insert command I would like to unlock the table.
0
mike99c
Asked:
mike99c
  • 9
  • 6
1 Solution
 
pritamduttCommented:
Specify with parameter

Example

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

Open in new window


Read More @ http://msdn.microsoft.com/en-us/library/ms177634.aspx
0
 
pritamduttCommented:
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,
0
 
jkofteCommented:
you should use transaction. check this link: http://msdn.microsoft.com/en-us/library/ms188929.aspx
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
pritamduttCommented:
0
 
mike99cAuthor Commented:
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

Open in new window

0
 
pritamduttCommented:
Okie let me ask is there any thing that would stop you from clubbing these two statements within a BEGIN.. END clause?
0
 
mike99cAuthor Commented:
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

Open in new window

0
 
pritamduttCommented:
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
0
 
mike99cAuthor Commented:
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.
0
 
pritamduttCommented:
Here is the solution for your requirement:

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

Open 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,
0
 
pritamduttCommented:
Any updates?
Has your issue been resolved?
0
 
mike99cAuthor Commented:
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.
0
 
pritamduttCommented:
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

Open in new window

and then perform query
select MyId from MyTable where GuiDColumn = <pass guid>

Open 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>

Open in new window



This should now finally resolve your query.

Regards,
0
 
mike99cAuthor Commented:
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.
0
 
pritamduttCommented:
Yes that is what precisely what I have explained above. Let me try to explain:

The following sql statement will do the following
1. Create a new MyID  MAX(src.myid)+1 which is one higher than current.

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

Open in new window


2. Now since this query is a single statement, you don't have the option to know the newly generated MyID. So, what we did was passed a unique-id, which is generated by your web application.

3. Now, when you query (given below) for MyId using the application generated web application, you will know the new MyId.

select MyId from MyTable where GuiDColumn = <pass guid>

Open in new window


Now you can use this MyId reference in your other tables.

Hope I have been able to help!

Regards,
0
 
mike99cAuthor Commented:
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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now