SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Lock only one row in update (lock a row)


Lock only one row in update (lock a row)

Author
Message
abitguru
abitguru
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 290
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.!!!
McSQL
McSQL
Old Hand
Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)

Group: General Forum Members
Points: 372 Visits: 299
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
abitguru
abitguru
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 290
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
McSQL
McSQL
Old Hand
Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)Old Hand (372 reputation)

Group: General Forum Members
Points: 372 Visits: 299
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86993 Visits: 45267
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, 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


abitguru
abitguru
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 290
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)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86993 Visits: 45267
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, 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


GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86993 Visits: 45267
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, 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


abitguru
abitguru
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 290
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!
abitguru
abitguru
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 290


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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search