Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Lock only one row in update (lock a row) Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 4:40 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 6:01 AM
Points: 74, Visits: 287
Hello!!!

The question is (if it is possible without any workaround, like add a state column)

How can i achieve this... I understand that I can't lock a row with select statement, but can I achive this:

--TRANS 1 while a transation is executing an update statement modifing one row, lock this row. Nobody can select this row while transaction is running.
--TRANS 2 while TRANS1 is running I can update other ROWS (not row in TRANS 1)

--example 1
--TRANS 1 => update table set field = 1 where id = 1 (LOCK THIS ROW ONLY THE ROW!!!)
--TRANS 2 => select * from table where id = 1 =====> MEANWHILE TRANS 1 IS RUNNING, HAS TO FAIL BECAUSE TRANS 1 is updating...

--example 2
--TRANS 1 => update table set field = 1 where id = 1
--TRANS 2 => select * from table where id = 2 =====> OK !

--example 3
--TRANS 1 => update table set field = 1 where id = 1
--TRANS 2 => update table set field = 1 where id = 2 =====> OK ! no one is locking this row!!!

* I disable lock escalation on the table (LOCK_ESCALATION = DISABLE)
* No cluster index on the table.

Hope you understand, i try a lot of query hints but noone get the results I spected (rowlock, xlock) (updlock)
when I use hits I test the lock results with

use master
SELECT * from sys.dm_tran_locks where request_session_id in (55,60) order by request_session_id

and see a PAGE lock for the resuouce, trans id 55 and 60 both takes differentes rows,

for a more especific example i post the las example i try.

TRANS 55

begin transaction

update reque_pa with (rowlock) set MINIMO = 2 WHERE CEREQ = 1

--with (rowlock,updlock)

WAITFOR DELAY '00:00:10';
commit transaction



The waitfor is to simulate a more long transaction to force the lock.

TRANS 60

begin transaction

SELECT * FROM Reque_pa with (rowlock) WHERE CEREQ = 2

commit transaction



hope you can help me, thanks in advance.!!!
Post #1434814
Posted Monday, March 25, 2013 5:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 4:06 AM
Points: 146, Visits: 281
You can use the WITH UPDLOCK clause in your update query.

There's a good thread on it here;

http://stackoverflow.com/questions/1921091/thread-safe-sql-transaction-how-to-lock-a-specific-row-during-a-transaction
Post #1434848
Posted Monday, March 25, 2013 6:09 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 6:01 AM
Points: 74, Visits: 287
Thanks McSQL

Will read the thread!

I try (with updlock) but dont work in my case, cause transaction 1 has a page lock that prevents transation 2 to takes a row
Post #1434853
Posted Monday, March 25, 2013 6:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 4:06 AM
Points: 146, Visits: 281
You can try disabling page locks for the index your query is using maybe and then try the UPDLOCK clause to make sure the row still gets locked;

ALTER INDEX <INDEX_NAME>
SET (
ALLOW_PAGE_LOCKS = OFF
)
GO
Post #1434855
Posted Monday, March 25, 2013 6:15 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
You want the ROWLOCK hint. Do note that just tells SQL to start with row locks, it can and will still escalate if need be. It will also still take the intent locks at page and table level.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1434856
Posted Monday, March 25, 2013 6:20 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 6:01 AM
Points: 74, Visits: 287
McSQL (3/25/2013)
You can try disabling page locks for the index your query is using maybe and then try the UPDLOCK clause to make sure the row still gets locked;

ALTER INDEX <INDEX_NAME>
SET (
ALLOW_PAGE_LOCKS = OFF
)
GO


Will try with this, in unique index, I only set (LOCK_ESCALATION = DISABLE) on the table

GilaMonster (3/25/2013)
You want the ROWLOCK hint. Do note that just tells SQL to start with row locks, it can and will still escalate if need be. It will also still take the intent locks at page and table level.


GilaMonster, Can I prevent lock escalation with (LOCK_ESCALATION = DISABLE) ?? So sql not escalate (only in this table)
Post #1434862
Posted Monday, March 25, 2013 6:25 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
abitguru (3/25/2013)
GilaMonster, Can I prevent lock escalation with (LOCK_ESCALATION = DISABLE) ?? So sql not escalate (only in this table)


You can. So you'd prefer SQL to run out of lock memory, fail any pending queries with an error (out of lock memory) or possibly even crash rather than escalating locks?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1434870
Posted Monday, March 25, 2013 6:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:30 PM
Points: 41,531, Visits: 34,448
McSQL (3/25/2013)
You can try disabling page locks for the index your query is using maybe and then try the UPDLOCK clause to make sure the row still gets locked;

ALTER INDEX <INDEX_NAME>
SET (
ALLOW_PAGE_LOCKS = OFF
)
GO


I would recommend not touching the locking hints on tables (allow page locks, allow row locks or lock escalation) as they can have some side effects including various operations failing, SQL running out of lock memory or even more serious problems than that. There are very few good reasons to change the locking options on indexes.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1434871
Posted Monday, March 25, 2013 6:37 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 6:01 AM
Points: 74, Visits: 287
GilaMonster (3/25/2013)
abitguru (3/25/2013)
GilaMonster, Can I prevent lock escalation with (LOCK_ESCALATION = DISABLE) ?? So sql not escalate (only in this table)


You can. So you'd prefer SQL to run out of lock memory, fail any pending queries with an error (out of lock memory) or possibly even crash rather than escalating locks?



I think its my mistake for not to explain me well (and my out of practice english)

I have a parameter table to number invoices for example, with around 20 records,

this records are independent each other, so if I read parameter number 1 I want noone can read this row. I know that this action is imposible in sql server (I research a lot), because it hasn't something like select for update...

Now I want to know if it is posible to lock one row meanwhile I am updating it. I see that a transaction that updates one row affects most of the records because SQL Server takes a PAGE lock.
I try varios ways to disable lock escalation in this particulary small table, but with no results. (LOCK_ESCALATION = DISABLE, ISOLATION LEVEL 3,4, Hints.)

I understand what you say GilaMonster, but I want only disable lock escalation in this table.

Am I explain myself right?

sorry if you dont understand my point, I'll try my best.

Thanks a lot!
Post #1434876
Posted Monday, March 25, 2013 6:45 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 10, 2013 6:01 AM
Points: 74, Visits: 287


Look at this image,

connection 57 has this query
update reque_pa with (updlock) set MINIMO = 2 WHERE CEREQ = 1

connection 58 has this query
SELECT * FROM Reque_pa WHERE CEREQ = 2


To complete query 2, query 1 has to finish (i put a wait.... to test it)

lock the locks in image, connection 57 makes a row lock!!! for every row I have in the table,
resouce description
1:1702673:0 (this is the row I am updating in query 1)
1:1702673:1
1:1702673:2
1:1702673:3
1:1702673:4

I can't explain why sql behaves this way..

Hope this helps!
Post #1434879
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse