Transactiom / Row Level Lock?

Question

Transactiom / Row Level Lock?

Viewing 4 posts - 16 through 19 (of 19 total)

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

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

Best I can find is

UPDATE WITH(ROWLOCK)

looks like the engine will start out with a page lock by default, but adding the ROWLOCK hint tells the engine to row lock if at at all possible.

C# Gnu
____________________________________________________

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

What I don’t understand is how you would make any change, since you can’t change the schema or application?

SQL Server locks are not meant to be used as application row locks; they are only used to control concurrency against other updates and reads in progress to ensure transactional consistency.

Also, locks are under the complete control of SQL Server, so it can chose to do row, page, or table locks according to it’s own internal rules, despite any hints you give.

Ken McKelvey SSCoach Points: 18939 More actions · Answer 3

C# Screw (11/5/2010)
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.

I am not sure what you are attempting to do, but the UPDATE at the start of the transaction will XLOCK the page being updated until the end of the transaction. What happens on other connections will depend on the isolation level. With the normal isolation levels, all reads will be blocked on that page unless the other connection is operating with READ UNCOMMITTED isolation (or the NOLOCK hint). With snapshot isolations I suspect that all connections will be able to read their own snapshot.

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

Ken McKelvey (11/5/2010)With the normal isolation levels, all reads will be blocked on that page...

Not if engine can do a rowlock, as I was trying to achieve.

C# Gnu
____________________________________________________

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