[dba-VB] vb.net - Updates

Shamil Salakhetdinov shamil at smsconsulting.spb.ru
Tue Apr 29 14:51:42 CDT 2008


John,

I'm not sure yet what happens< I will try to find out more details in the
coming days - here is the message I'm currently getting with new Strategy 4
, which I'm making similar to your way of coding this task but running in
multi-threading environment:

+++
System.Data.SqlClient.SqlException: Transaction (Process ID
 57) was deadlocked on lock resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.
---

Just wanted to warn you - it might happen you'll never see this message but
if you'll see it then you may find there is no workaround except abandoning
the idea of batch updates running in several threads - and I mean parallel
batch updates: of course, as I noted in my previous e-mail parallel reading
and parsing could be done but batch updates very probably can't be run in
parallel threads because they might result in deadlocks...

But it looks like (from your recent posting here) that you don't need such
parallel batch updates at all?

Thanks.

Shamil


-----Original Message-----
From: dba-vb-bounces at databaseadvisors.com
[mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Tuesday, April 29, 2008 11:12 PM
To: Discussion concerning Visual Basic and related programming issues.
Subject: Re: [dba-VB] vb.net - Updates

Interesting.  Do you think that you would get deadlocks for different 
blocks of records?  In my case there is not "a million users" scenario. 
  This process (and its threads) are the only things modifying data. 
Each "chunk" is a defined block  of records.

John W. Colby
www.ColbyConsulting.com


Shamil Salakhetdinov wrote:
> <<<
> ...it is definitely going to be desirable to feed the update 
> process off to a thread...
> Hi John,
> 
> I was lucky with multi-threaded batch updates' samples I posted here the
day
> before yesterday - and the new tests show that batch updates in
> multi-threading environments result in MS SQL deadlocks: I should have
> thought about those issues before...
> 
> Recap: forget batch updates - one of the scenarios of your task could be:
> 
> - many readers;
> - many parsers;
> - but just one updater making batch updates, or many updaters but updating
> just one row in one cycle...
> 
> HTH to save your time,
> Shamil
> 
> 
> 
> -----Original Message-----
> From: dba-vb-bounces at databaseadvisors.com
> [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Tuesday, April 29, 2008 3:31 AM
> To: Discussion concerning Visual Basic and related programming issues.
> Subject: Re: [dba-VB] vb.net - Updates
> 
> We have ignition folks.  I just updated the first 10K records in the 
> database.  I had to set the batch size to 100, as I was getting a 
> timeout with a batch size as small as 1000 records.  I haven't done any 
> timings but it is definitely going to be desirable to feed the update 
> process off to a thread if I can figure out how to do that.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> jwcolby wrote:
>> I THINK I have found my solution, called the SQLCommandBuilder.  I found 
>> reference to the command build saying how terrible it is, then a piece 
>> of code that, in simple plain language, shows how to use it all, using 
>> the simple objects I am already using.
>>
>> We shall see.
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> _______________________________________________
> dba-VB mailing list
> dba-VB at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-vb
> http://www.databaseadvisors.com
> 
> 
_______________________________________________
dba-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com




More information about the dba-VB mailing list