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 |
|
Pzygote
Starting Member
3 Posts |
 Posted - 2008-05-01 : 02:18:13
|
| I have several osql processes running piped sql transactions, each of these transactions call pretty much the same update statements with joins to table(s) (table2 in this case) that, though large, are pretty much created by each process so they are not shared - the destination table has about 2.5M rows:UPDATE t1 SET t1.a = t2.a, t1.b = t2.b t1.c = 'U' FROM table1 t1 WITH (ROWLOCK) INNER JOIN table2 t2 WITH (NOLOCK) ON (t2.d = t1.d AND t2.e = t1.e AND t2.f = t1.f AND t2.g = t1.g) Initially I couldn't get the processes to run concurrently, but after creating an index on fields d, e, f, g and using the NOLOCK and ROWLOCK hints, the processes will run for a while and then hang on the UPDATE - it took me a while to trace it & invariably the system stops processing (no errors!) when the above and similar UPDATE's to the same 'table1' are attempted. The UPDATE's are embedded in transactions with each having a TRY and CATCH clause for error handling - the weird thing is the processes hang without error...I really need help cuz I've been banging my head against a wall on this! Since a lot of rows are affected I imagine the ROWLOCK's are being ignored and pagelocks are occurring affecting other processes and their updates; I'm not getting a deadlock or a lock timeout (though I set that absurdly high--will reduce it an try again now). I could really use help on even approaching this another way - I know since 'table2' has a lot a rows a single UPDATE is probably not a good idea so i might break that up into single UPDATE's - help on iterating through 'table2' and updating 'table1' one row at a time would be appreciated - and any other thoughts, comments, solutions, etc...Many Thanks!-p |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-05-01 : 08:31:36
|
| if there are a lot of rowlocks the locks will escalate to pagelock to lessen the use of resources.the solution might be to update in chunks._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
 |
|
|
Pzygote
Starting Member
3 Posts |
Posted - 2008-05-01 : 11:37:09
|
| Thanks for your response - I'm trying to implement updates in chunks-is there an easy way to do that or to iterate through the rows of 'table2' and then UPDATE 'table1' one row at a time with ROWLOCK? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-05-01 : 11:50:32
|
| set rowcount 10 -- update 10 rows in a chunkwhile <some condition here usually an exists to check if more rows need updating>begin-- your update here -- it will update 10 rows at a timeendset rowcount 0 -- reset the rowcountif you can implement this then you don't really need rowlock_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com |
|
|
|
Pzygote
Starting Member
3 Posts |
Posted - 2008-05-01 : 15:01:15
|
| Thanks spirit1 for the additional help - I'm using a #temp table and iterating like this: set rowcount 0 select NULL mykey, * into #Temp FROM Temp WITH (NOLOCK) set rowcount 1 update #Temp set mykey = 1 while @@rowcount > 0 begin set rowcount 0 UPDATE dest_table SET dest_table.a = temp_table.value, dest_table.b = temp_table.asof_date, dest_table.c = 'U' FROM dest dest_table WITH (ROWLOCK) INNER JOIN #Temp temp_table WITH (NOLOCK) ON (temp_table.d = dest_table.d AND temp_table.e = dest_table.e AND temp_table.f = dest_table.f AND temp_table.g = dest_table.g) WHERE mykey = 1 delete #Temp where mykey = 1 set rowcount 1 update #Temp set mykey = 1 end set rowcount 0Seem reasonable? Any other thoughts? |
|
|
|
|
|
|
|
|
Please Help with Concurrency Locking - Hangs