Row Locks and Transaction

Question

Row Locks and Transaction

Narayana_17

SSC Eights!

Points: 933
More actions
March 7, 2011 at 6:03 am

#234766

Hi,
Can anyone tel How to find the Locked rows in a table .
or to determine whether a row is locked or not.
I got Stuck in Sql script where i need to use the row level locks for selected rows in a table.
I want to use Row Locks on a table in a Transaction Code
Can anyone tell how to implement/use Row level Locks on a table in a Transaction, so that other users cannot access that row untill the transaction is completed.
Begin Transaction
lock the rows
do the calculations and update other tables
release the lock
Commit Transaction
Struggling with this issue since 3 days.
Any Help will be grately appreciated.
Please help me in this.
Thanks
Lakshman

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Lakshman

Why do you want to use row locks? Is there a reason why you don't want to let SQL Server decide what locks to use, and escalate to page locks or table locks where appropriate? Are you seeing behaviour that isn't what you'd expect? If so, what?

John

Narayana_17 SSC Eights! Points: 933 More actions · Answer 2

Hi John

i have a mastertable MT and a child table CT.

in a stored procedure i am accession a row of master table and doing the calculations etc, from different tables whil=ch is a long stored procedure with so many selctupdate statements. in between i am updating the child table records also of same master table row.

so while doing this transactions, i dont want any other user access and do any changes to this mastertable row n its child tables until the transction finished.

So want to rock the master table row n its child table rows till the transaction is finished, and the other users should not retrieve these records.

any ideas please

LutzM SSC Guru Points: 107049 More actions · Answer 3

Since you're trying to lock a specific row my guess is you're using a c.u.r.s.o.r. or any other kind of loop to process your data.

If that's the case, you should think about a set based solution instead of row based locking.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Narayana_17 SSC Eights! Points: 933 More actions · Answer 4

Hi can you tell how to use set locks .

Possible explain with an example please.

Thanks

Lakshman

LutzM SSC Guru Points: 107049 More actions · Answer 5

Narayana_17 (3/10/2011)
Hi can you tell how to use set locks .
Possible explain with an example please.
Thanks
Lakshman

???

I'm talking about a set based sql code solution.

Think of the task that needs to be done instead of how to lock a row....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 6

Narayana_17 (3/8/2011)
So want to rock the master table row n its child table rows till the transaction is finished, and the other users should not retrieve these records.

You're probably looking for something like this:

begin transaction

--calculate some number from child table

--update master table

--update child table

--Calculate new number

commit transaction

This will tell SQL not to release the locks until the commit is done. If for whatever reason you don't want to keep the data changes made (if you get an error) you'll need to check for those conditions and do a rollback.