Transactiom / Row Level Lock?

Question

Post reply

Transactiom / Row Level Lock?

C# Gnu

SSCertifiable

Points: 5973
More actions
November 4, 2010 at 10:13 am

#226042

Hi
can I place a row level lock on a table on a single row?
I would like to do this so that any SELECT statements that would return that row would have to wait until row is unlocked.
Thanks for any ideas/solutions
C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Any particular reason to not just let SQL Server handle the locking? It usually does an adequate job at it.

If you really want to override that, you can look at "set isolation level" for some options.

If those won't do, you'll need to build a homegrown MUTEX of some sort. Would probably revolve around a bit field that you'd set as "BeingRead" before the select and unset (update to 0) once you want to allow other reads. Of course, that can have issues if web pages are simply closed, or left open indefinitely, and so on.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

C# Gnu SSCertifiable Points: 5973 More actions · Answer 2

Hi

here is my problem:

I have a row in a table of 'word templates' :

ID LCATION

1 \\location\dir\template1.doc

2 \\location\dir\template2.doc

3 \\location\dir\template3.doc

..

There are hundreds of templates in the above table.

'Normally' a 3rd party application loads a chosen template into word, but on this occassion I need to temporarily change the LOCATION and then change it back again very soon after.

For example, I have a process that will

a/ change location to something different, eg change location 2 to \\location\dir\templateSpecial2.doc

b/ 2rd part software takes a copy of the template.

c/ Change location 2 back to \\location\dir\template2.doc

Althought the above operation is pretty quick, I want to stop any other users form reading the temporary location, this is why I am hoping to place a row level lock and making any select query wait - perhaps for one second until lock is released.

Hope that explains better ...as I dont know how to do a row-level lock

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

GSquared SSC Guru Points: 260824 More actions · Answer 3

What you're talking about aren't "locks" in the traditional database sense. What you need is a "mutex" (short for "mutual exclusion"), which is a pretty standard programming concept.

Usually the easiest way to handle one of those in a database is add a bit column to the table, something like "InUse" or "BeingRead". When a process wants to query that row, it has to check in the Where clause that the column = 0, or it skips it. Further handling can include using WaitFor loop to keep re-trying, or sending an alert, or some combination thereof. If the query successfully finds the rows it needs, it then updates them to 1 or -1 in that column. When the process is finished, it sets it back to 0.

Some things to consider are deadlock prevention, interrupted processes leaving it set to 1 indefinitely (hence alerts in the process if it has to wait too long or something like that), and so on.

You might also need to look into making sure there are no processes accessing this that utilize Snapshot Isolation or NoLock or Read Uncommitted.

You might also need to make sure that the MaxDop settings are controlled for this.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

C# Gnu SSCertifiable Points: 5973 More actions · Answer 4

Thanks but ..

Cant do MUTEX as its a third party program that queries table over which I have no control other that locking rows, if this is possible?

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

GSquared SSC Guru Points: 260824 More actions · Answer 5

Take a look at isolation levels in Books Online or on MSDN.com. That's about as close as you're going to get. Probably won't do what you need, but worth a look.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

C# Gnu SSCertifiable Points: 5973 More actions · Answer 6

Thanks

Anyone know how to lock a row?

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

Um....

GSquared (11/4/2010)
Take a look at isolation levels in Books Online or on MSDN.com. That's about as close as you're going to get. Probably won't do what you need, but worth a look.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

C# Gnu SSCertifiable Points: 5973 More actions · Answer 8

Thanks G²

Gave up on this one!

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Derrick Smith SSChampion Points: 10833 More actions · Answer 9

What you're looking to do isn't really possible without a schema change.

Adding the InUse column is a great idea, but you said you can't do that.

In essence what you're trying to do is update a row, and then only allow a certain application to read that row (while preventing others), and then updating it after the app is done..Without a code change to the app itself or the schema, there is no way for you to determine who should be able to read the row and who shouldn't.

C# Gnu SSCertifiable Points: 5973 More actions · Answer 10

Not Quite!

simply locking the row would have been fine.

This I could do in my FoxPro days, but in SQL it seems that at least a page locked.

For example if I try :

BEGIN TRANSACTION

UPDATE MyTable SET Location = 'something' WHERE KeyValue = X

-- do what I need to do now

-- then rollback

ROLLBACK TRANSACTION

The above seems to allow other connections to SELECT on entire table, and prevent update on entire table, even though there is a Primary key on 'KeyValue'.

I could be that just one page is locked, hard to tell. If I could have locked one row (for update), that would have been useful.

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Derrick Smith SSChampion Points: 10833 More actions · Answer 11

True, but then you'd need to modify the app to use read_uncommitted to be able to read the changed value if it's in mid transaction, and you said you can't do a code change because it's a 3rd party app.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 12

C# Screw (11/5/2010)
This I could do in my FoxPro days, but in SQL it seems that at least a page locked.

No, SQL locks at row, page or table depending on a large number of factors

I could be that just one page is locked, hard to tell.

Not hard to tell. SELECT from sys.dm_tran_locks

And read up on isolation levels and lock types (including intent locks)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

C# Gnu SSCertifiable Points: 5973 More actions · Answer 13

Derrick Smith (11/5/2010)
True, but then you'd need to modify the app to use read_uncommitted .

Not quite, wanted any application to wait for transaction to complete, but only if wanted to read the particular row.

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

C# Gnu SSCertifiable Points: 5973 More actions · Answer 14

GilaMonster (11/5/2010)
C# Screw (11/5/2010)
This I could do in my FoxPro days, but in SQL it seems that at least a page locked.
No, SQL locks at row, page or table depending on a large number of factors

Ok I have read a stack of stuff on read locks , but nothing relating to locking one row during a transaction.

I would like to know specifically what the 'factors' are that you mention, reason being in the example above I have a where clause on PrimaryKey which I thought might have helped toward a row lock.

Cheers

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015