Row Locking - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Row Locking

Author

Topic

BeamBrain
Starting Member

10 Posts

Posted - 2009-04-28 : 09:52:29

Hi,

I have a simple table with 10 cols and 300K rows. It is NOT joined to anything but it is used my 1000s of concurrent users.

I have SQL statement thus:

SELECT ID
FROM t_table
WHERE ID = x

and then

UPDATE t_table
SET Name = 'yyy'
WHERE ID = x

OK nothing special. Only thing is i want to be able to LOCK these statements so that nothing can happen to ID x until BOTH statements have exceuted.

If i do...

SELECT ID
FROM t_table
WITH (ROWLOCK)
WHERE ID = x

and then

UPDATE t_table
WITH (ROWLOCK)
SET Name = 'yyy'
WHERE ID = x

i'm concerened that something could happen BETWEEN these 2 statements ie. as soon as the first statement finishes it will be released THEN the first sql statement could be called again BEFORE the update has occurred.

I hope this is clear and thnaks in advance for any help.

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-04-28 : 09:54:47

Put both statements in a transaction?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-28 : 09:57:14

Or use SNAPSHOT ISOLATION LEVEL.

E 12°55'05.63"
N 56°04'39.26"

BeamBrain
Starting Member

10 Posts

Posted - 2009-04-28 : 10:38:37

Great thanks. Forgive my lack of understanding here but i am using ASP and recordsets so the code is more like this...

OpenDataConnection()

set rsID = CreateObject("ADODB.recordset")
searchPhrase = "SELECT ID
FROM t_table
WITH (ROWLOCK)
WHERE Name = 'xxx'"

rsID.Open searchPhrase, oConn
iID = rsID(0)
rsID.close
set rsID = nothing

CloseDataConnection()

OpenDataConnection()

oConn.BeginTrans
set cm = CreateObject("ADODB.Command")
set cm.ActiveConnection = oConn

cm.CommandText = "UPDATE t_table
WITH (ROWLOCK)
SET Name = 'yyy'
WHERE ID = " & iID

cm.execute
oConn.commitTrans
set cm = nothing

CloseDataConnection()

How would this code look if i put them both in a transaction or use SNAPSHOT ISOLATION LEVEL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources