SQL Server Performance Forum – Threads Archive
locking issues
i have a table which i’m keeping it updated by the below storedproc algorithm. begin transhourlyHit = select_sp with (update_lock)
if (hourlyHit < 1)
call insert_sp with (upd_lock) which sets hourly hit to 1
else
call update_sp with (upd_lock) which sets hourly hit to hourlyHit + n
(where n is a number changes frequently)
end trans
i’m using sps inside a transaction because i’ve to update the correct values for the hourly hit… transaction i believe makes the selected hourlyHit value to the same until the end of the transaction, so no any updates’re occuring on the same row and this leads to no data corruption. appz invoke those sps to try to change the same row through those sps for the same hour… and of course there’re lock contentions occuring, what’re ur recommendations for performance&preventing locking contentions ?? dummy table looks like this: log_summary
logtime | hits
——————–
.. 13:00 | 12
.. 14:00 | 232
.. 15:00 | 45
.
. (it has an index on logtime, actually real table has more indexes) thx a lot
FOr the locking contention refer tohttp://www.sql-server-performance.com/lock_contention_tamed_article.asp andhttp://www.sql-server-performance.com/reducing_locks.asp to resolve the issue. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
thx, i’ve read the documents and did the things they talk about. yes there is an improvement but i was asked a special issue which many clients trying to select/update the very "same row" with an update lock. is not there any experienced users who can improve on this problem?? thx a lot again
The Update (U) locks are used to prevent a deadlock. For example, if two transactions intend to update the same row, each of these transactions set the shared lock on this resource and after that tried to set the exclusive lock. Without Update (U) locks, each transaction will wait for the other transaction to release its shared-mode lock, and a deadlock will occur. In the generic terms, if another connection would try to get another update lock on the same row, it would be put on hold until the first connection releases its lock — two update locks on the same resource are incompatible. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
How about:
…
begin trans
update ls
set ls.hits = ls.hits + @n
from log_summary ls (tablock)
where ls.logTime = @logTime
—
select @rowCount = @@rowCount, @error = @@error
—
if @@error <> 0 begin
rollback transaction
return
end
—
if @@rowCount = 0 insert into log_summary (logTime, hits) values(@logTime, @n)
…
It looks like you are allways accessing the same row, so you can use table lock, it would have the same effect. I believe it takes less resources to aquire table lock then row lock. For queries just reading data from that table apply nolock hint.
]]>