×

Loading...
Ad by
  • 予人玫瑰,手有余香:加拿大新天地工作移民诚聘求职顾问&行业导师!
Ad by
  • 予人玫瑰,手有余香:加拿大新天地工作移民诚聘求职顾问&行业导师!

why use trigger

assume you have a queue table (queue1) with three extra columns -
(1) processmask
0 - unprocessed
1 - processing
2 - processed
(2) spid
(3) lastUpdate

declare @starttime datetime
begin
set rowcount 20
select @starttime = getdate()
update queue1 set processmask = 1, spid=@@spid, lastUpdate = @starttime
set rowcount 0
/* begin process */
/* use batch mode to use cursor to process the 20 records by select criteria
processmask=1 and spid=@@spid and lastUpdate>=@starttime
/* end process */
update queue1 set processmask = 2
where processmask =1 and spid = @@spid and lastUpdate >= @starttime
end

From performance perspective, use row-level locking for the table.
actually it will be better to define some rule to distribute the work to each client
add index (in my case, lastUpdate+spid+processmask)
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / ms sql server 高手请进
    有个问题求解:

    假定我有多个CLIENT程序在不同机器上运行 。要处理的数据是一个VIEW里面有xxx条记录,并且有随时追加的可能(其他应用)。限制每个CLIENT每次只可以装入20条记录,处理完了再取那些没有被其他CLIENT装入的数据中的20条。有什么方法或逻辑实现那?
    • create a temp table, load the data to be process into the temp table use a trigger, lock the record when do processing, unlock/delete after done.
      • creat temp table for each active client?
        • Create only one table for all clients, which will serve as a queue.
          • why use trigger
            assume you have a queue table (queue1) with three extra columns -
            (1) processmask
            0 - unprocessed
            1 - processing
            2 - processed
            (2) spid
            (3) lastUpdate

            declare @starttime datetime
            begin
            set rowcount 20
            select @starttime = getdate()
            update queue1 set processmask = 1, spid=@@spid, lastUpdate = @starttime
            set rowcount 0
            /* begin process */
            /* use batch mode to use cursor to process the 20 records by select criteria
            processmask=1 and spid=@@spid and lastUpdate>=@starttime
            /* end process */
            update queue1 set processmask = 2
            where processmask =1 and spid = @@spid and lastUpdate >= @starttime
            end

            From performance perspective, use row-level locking for the table.
            actually it will be better to define some rule to distribute the work to each client
            add index (in my case, lastUpdate+spid+processmask)
            • Trigger is to populate the queue. You can use other methods as you wish.
              There should be only one control thread that can populate the queue though. All other clients (worker thread) should just retrieve from the top of queue and do processing. Otherwise, it will be very easy to get into a deadlock situation. Ideally, the control thread should also distribute the tasks.