October 8, 2013 at 7:23 am
Hi,
We have several tables, sales, orders, ..., that when they're updated insert record in a pool so that the customer balance is updated through a scheduled procedure...
The scheduled procedure currently has a cursor that goes through all the records in the pool, makes a big update query and then deletes the record from the pool (no risk of doing the same record twice since SQL Agent doesn't start a new schedule until the old one has finished...).
Also a scheduler log is inserted in a table with the number of records processed..
SET @cnt = 0
DECLARE cur CURSOR FOR SELECT Id, Customer FROM UpdatePool
OPEN cur
FETCH NEXT FROM cur INTO @id, @customer
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE ...... WHERE customerid = @customer
SET @cnt = @cnt + 1
DELETE FROM UpdatePool WHERE id = @id
FETCH NEXT FROM cur INTO @id, @customer
END
CLOSE CUR
DEALLOCATE cur
INSERT INTO PoolLog (Date, NumProcs) VALUES (GETDATE(), @cnt)
Is there any way of doing this with an UPDATE .. SET ... WHERE customerid IN (SELECT customerId FROM UpdatePool) and then delete the processed records?! I could try to put the INSERTED.customerId from the UPDATE in a table variable and then delete from UpdatePool where customerid IN ... but I can delete more recent process requests (while I'm processing a 2nd customerid a request for the 1st can be inserted again...).
Thanks,
Pedro
October 8, 2013 at 7:31 am
Would something like this work?
update ...
from TableA a
join UpdatePool b on a.CustomerID = b.CustomerID
delete from UpdatePool
from TableA a
join UpdatePool b on a.ID = b.ID
select @RowCnt = @@ROWCOUNT
insert into PoolLog ... values (getdate(), @RowCnt)
October 8, 2013 at 7:41 am
ID doesn't exist on the customers table... Exists but it's different from the pool (id in the pool is an identity seed number just for storing...).
I have an implementation that's almost like this... but I don't delete the pool right away...
I could add a status column on UpdatePool (0 - to process, 1 - processing, 2 - delete)
DECLARE @tbl TABLE (customerId INT)
UPDATE t SET status = 1 OUTPUT INSERTED.CustomerId INTO @tbl FROM (SELECT id, status, customerId FROM UpdatePool WHERE status = 0) t
UPDATE ... SET ... WHERE customerId IN (SELECT customerId FROM @tbl)
DELETE FROM UpdatePool WHERE status = 1
Since the code isn't ran twice at the same time no 2 processes will set the flag status to 1.... I think...
Thanks,
Pedro
October 9, 2013 at 10:32 am
PiMané (10/8/2013)
ID doesn't exist on the customers table... Exists but it's different from the pool (id in the pool is an identity seed number just for storing...).I have an implementation that's almost like this... but I don't delete the pool right away...
I could add a status column on UpdatePool (0 - to process, 1 - processing, 2 - delete)
DECLARE @tbl TABLE (customerId INT)
UPDATE t SET status = 1 OUTPUT INSERTED.CustomerId INTO @tbl FROM (SELECT id, status, customerId FROM UpdatePool WHERE status = 0) t
UPDATE ... SET ... WHERE customerId IN (SELECT customerId FROM @tbl)
DELETE FROM UpdatePool WHERE status = 1
Since the code isn't ran twice at the same time no 2 processes will set the flag status to 1.... I think...
Thanks,
Pedro
You could accomplish this without the status column. You can delete from UpdatePool and OUTPUT the deleted.[column] values into a table variable or temp table and update other tables from that. I would suggest that you manage transactions carefully and include robust error handling, though. How you do that will depend on what should happen if a DELETE - OUTPUT - UPDATE process that includes a row with customerID=123 encounters an error in the UPDATE stage while in the meantime another row has been inserted into the UpdatePool table for customerID=123. Do you want to roll back the DELETE, which means you would not lose any data but would end up with two rows where customerID=123 in the UpdatePool table, and if not, what will you do with the data in the table variable or temp table so it isn't lost?
BTW, even if you ran two instances of your code at the same time, you still can't have a row that gets updated from "status = 0" to "status = 1" by both instances. One instance will hold exclusive locks on the rows it is updating (or perhaps the whole table if lock escalation occurs); the other instance will have to wait for those locks to be released before it can complete its UPDATE.
What can and very likely will happen with two instances of this code running, though, is a deadlock. Imagine that there are 10,000 rows to be updated. Instance 1 starts running and takes row locks on rows 1-5000 when instance 2 starts running and seeing locks on rows 1-5000, takes row locks on rows 5001-10000. Now, instance 1 will be holding locks on rows 1-5000 and waiting for locks on rows 5001-10000 while instance 2 will be holding locks on rows 5001-10000 and waiting for locks on rows 1-5000. Neither instance will release its locks until it gets the rest of the locks it needs, which it can't get because the other instance is holding those locks, so the two instances become deadlocked. SQL Server monitors this and when it detects a deadlock, it will kill one of the instances with an error message that it was deadlocked and chosen as the deadlock victim.
This is a simplistic example - there's much more to locking, blocking, and deadlocking than this, but it illustrates the principal.
Jason Wolfkill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply