July 29, 2015 at 11:09 pm
To avoid locking/blocking, or in transaction scope, we are trying make a common practice of writing coide for update commands in our all SPs based on primary key columns in where clause. I have a following scenario in which I need suggestion.
UPDATE [dbo].[TL_CST_Locker_Issuance] SET
[isActive] = 0
WHERE
LockerIssuanceId IN (SELECT LockerIssuanceId
FROM TL_CST_Locker_Issuance WHERE
LockerAppFormId = @LockerAppFormId and LockerNumber=@LockerNumber and isActive=1 )
the other approach is to use CTE to bring all Ids in CTE and then run update.
;WITH T AS
(
SELECT LockerIssuanceId
FROM TL_CST_Locker_Issuance WHERE
LockerAppFormId = @LockerAppFormId and LockerNumber=@LockerNumber and isActive=1
)
UPDATE [dbo].[TL_CST_Locker_Issuance] SET
[isActive] = 0
WHERE
LockerIssuanceId IN (select LockerIssuanceId from T)
Please let me know what is the better approach and should be followed to avoid locks and gain performance or best approach. Any set of steps to follow or guidance I need?
Shamshad Ali
July 30, 2015 at 5:07 am
Have you compared execution plans for those queries ?
July 30, 2015 at 5:40 am
Neither.
UPDATE [dbo].[TL_CST_Locker_Issuance] SET
[isActive] = 0
WHERE
LockerAppFormId = @LockerAppFormId and LockerNumber=@LockerNumber and isActive=1;
Writing the query in a more complicated way is not a good plan in general. SQL would very likely have simplified the query to the above one anyway, but it would have taken time.
Locking problems are fixed with proper indexes, queries written in their simplest form and so that they can use indexes efficiently, and if there are still severe locking problems, with a change of isolation level (READ COMMITTED SNAPSHOT/SNAPSHOT)
My general rule for queries:
Write the query in the simplest way possible. If testing shows that there are performance/locking problems, then consider alternate forms and test them.
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
July 30, 2015 at 6:20 am
A peer of mine is saying when we use updLock, it uses Primary key and so the practice of using PK should be forcefully used in all update statements with WHERE clause, so I am trying to evaluate all those such SPs of update statements where Primary Key is not used. He is saying it Locks the row/Table until a transaction is committed or rolled back if we are not using primary key when running update based on non primary keys. Let me know what you suggest then?
Shamshad Ali
July 30, 2015 at 6:33 am
Please examin query plans for all three queries above. Most probably you will not find the difference concerning perfomance.
Optimizer always tries its best to utilize PK index, you needn't to force server to utilize it.
July 30, 2015 at 6:43 am
I've already told you what I suggest.
Write the query in the simplest way possible. If testing shows that there are performance/locking problems, then consider alternate forms and test them.
As for your colleague, ask him to prove what he's claiming. Because update locks are always held until the end of the transaction regardless of the form of the query.
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
July 31, 2015 at 5:52 am
I tested this with the following query:
Begin tran A
update TL_CST_Account set AccountTitle = 'Shamshad-windows' where AccountId = 10001000010021
The idea is, if we are running an update on a table for a particular row in a transaction scope then, it should not lock whole table for other users to run select, update or delete query. If the above row is not committed, I can't run select * from Table with no where clause.
but if i run select * from table where accountId = "some other account", it works ...
select * from TL_CST_Account where accountId = 10001000010017 -- this runs successfully because I am selecting other account from same table
select * from TL_CST_Account where accountId = 10001000010021-- this won't run until i commit update query
select * from TL_CST_Account -- this won't run
select * from TL_CST_Account where accounttitle like '%shamshad%' -- this won't run until i commit update query
Generally, it should not lock whole table only the row that is under transaction scope.
Shamshad
July 31, 2015 at 7:13 am
It won't lock the entire table, it'll lock the row being updated.
You can't do a select * with no where clause because there's a row locked. If the entire table was being locked due to the update, then you wouldn't be able to run a select on some other account, and as you show, you can.
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply