asked on SQL Server Deadlock Lock chosen for fail
The code below is part of a SQL Job and periodically fails as the deadlock victim
Is there a way I can stop this?
Perhaps with an "If Exists ...
Or just putting a Print statement after the first update?
I changed this...
To This
![Screen Print]()
Is there a way I can stop this?
Perhaps with an "If Exists ...
Or just putting a Print statement after the first update?
I changed this...
--sometimes the amount ends with just a . not .00
UPDATE ewPayments
SET Amount = REPLACE(Amount, '.', '.00')
WHERE RIGHT(Amount, 1) = '.';
UPDATE ewPaymentsActivity
SET Amount = REPLACE(Amount, '.', '.00')
WHERE RIGHT(Amount, 1) = '.';
To This
--sometimes the amount ends with just a . not .00
-- LB Job had excessive amount of deadlock fails.
-- Previous code is archived after current
IF EXISTS ( SELECT 1
FROM ewPayments WITH ( NOLOCK )
WHERE RIGHT(Amount, 1) = '.' )
BEGIN
UPDATE ewPayments
SET Amount = REPLACE(Amount, '.', '.00')
WHERE RIGHT(Amount, 1) = '.';
END;
IF EXISTS ( SELECT 1
FROM ewPaymentsActivity WITH ( NOLOCK )
WHERE RIGHT(Amount, 1) = '.' )
BEGIN
UPDATE ewPaymentsActivity
SET Amount = REPLACE(Amount, '.', '.00')
WHERE RIGHT(Amount, 1) = '.';
END;
/*
UPDATE ewPayments
SET Amount = REPLACE(Amount, '.', '.00')
WHERE RIGHT(Amount, 1) = '.';
UPDATE ewPaymentsActivity
SET Amount = REPLACE(Amount, '.', '.00')
WHERE RIGHT(Amount, 1) = '.';
*/
Microsoft SQL ServerRemote Access
Last Comment
_agx_
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Not an answer to your question, but ... does this field store currency? Either
A) changing the data type to currency so formatting is unnecessary OR
B) formatting the value properly, when INSERTed
would avoid the need for the current update, which probably does a table scan, increasing the likelihood of a deadlock.
A) changing the data type to currency so formatting is unnecessary OR
B) formatting the value properly, when INSERTed
would avoid the need for the current update, which probably does a table scan, increasing the likelihood of a deadlock.
ASKER
Hey _agx
Yes it does store currency
And I am in the middle of making changes but it is a huge table and will take me a bit to "model"
I just took over and discovered this.
Yes it does store currency
And I am in the middle of making changes but it is a huge table and will take me a bit to "model"
I just took over and discovered this.
help me understand why the % works better than a RIGHT statement?When using a function over a table column, you're taking all the ability to the engine to use any index, that's why you should avoid as much of possible the usage of functions in any search criteria.
ASKER
Got it. Thanks.
Valid answer... awarding now
Valid answer... awarding now
ASKER
Thanks
Okay, I'm sure you already know that currency type would be better (long term) for a number of reasons (no formatting needed, query performance, etc..) so ultimately that's the option I'd suggest.
ASKER
_ahg... totally agree.
It seems that this table was brought over from MYSQL during a API update and they simply missed this and add bandaids all over the place rather than fix the underlying issue.
It seems that this table was brought over from MYSQL during a API update and they simply missed this and add bandaids all over the place rather than fix the underlying issue.
missed this and add bandaids all over the place rather than fix the underlying issue.
Yep, been there, done that ;-)
Not against it at all
Just want to understand why.