Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ronb
Starting Member
1 Post |
 Posted - 2008-02-05 : 08:59:21
|
| I would like to build a workflow system where 100 processes are requesting an item from a ~1.000.000 items large DB, process that item and move it to the next state. The problem with the current implementation I tried is that I get deadlocks....The DB table looks like:CREATE TABLE Transactions(itemid CHAR(32),status TINYINT not null default 0,result INT not null default 0,lockby TINYINT not null default 0, .... (etc.)PRIMARY KEY(refno));CREATE INDEX IxStatus on transactions (status)Each process (with his own ID) is doing 4 step:1) update transactions set status=1, lockby=<ID> from (select top 1 itemid from transactions where status=0) as t1 where t1.itemid=transactions.itemid2) select itemid from transactions where status=1 and lockby=<ID>3) process item4) update transactions set status=2,result=<RESULT> where itemid=<ITEMID>Does anyone has a suggestion on how to do this? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2008-02-06 : 06:21:24
|
| [{a)transactions]-<[{b)transactionstate]>-[{c)state]keys(a) idfield(c) idfield (sequencefield as data item)(b) fk-a-id, fk-b-id, startdate/time, enddate/timeI have done something similar myself using the structure above.Basically transactions can be linked with multiples states over time, but with only one state at any one point in time. A transaction can repeat with any ony state (i.e. it can go backwards/forwards within a work_process).Initially a transaction is created, and automatically assigned to the "initial/starting" state.A user picks a trans and moves it onwards to another state. In that case 1 old (b) record is updated and 1 new (b) record is inserted within 1 "SQL transaction". You can either prevent a [transaction] moving if it's [state] is not where the user thinks it should be or you could code on the basis that a [transactionstate] is changed and if a problem occurs (duplicate key (on the time period)) then rollback the move and advise the user to refresh their screen and start again - it's a tradeoff of which is more likely to happen v the cost of checking/rolling back. |
 |
|
|
JasonL
Starting Member
35 Posts |
Posted - 2008-02-27 : 18:29:26
|
| Wondering whether you could just commit after step 1 and for the rest of the step just "update back to what it was if there is any error". I think that should get rid of the of lock/wait/deadlock problem?JasonL @Microsoft http://blogs.msdn.com/usisvde/ |
|
|
|
|
|
|
|
|
How to build a workflow using a SQL DB?