Threads and Transaction Problems....

29 Mar 2006 8:37 PM

pjondevelopment

After four long hours of googling on the subject and finding almost
nothing, I'm forced to post this one.

I'm doing a small project that reads several IIS and stores in a
database the domains hosted there.

This application runs quite fine. But I'd like to add some transaction
control on it, because it might ab-end at any given time (so far it
didn't, but I think it's sheer dumb luck :-)

Stripped out of anything meaningful code, my approach to create the
transaction is:

---- BEGIN pseudoCode ----
Imports System.Threading

Private ThreadCount As Integer = 0

Sub Main()
   For Each s as Server In Farm
      Interlocked.Increment(ThreadCount)
      Do While Not ThreadPool.QueueUserWorkItem(AddressOf
WorkerThreadForServer, s)
         Thread.Sleep(100)
      Loop
   Next

   Do While (ThreadCount <> 0)
      Thread.Sleep(100)
   Loop
End Sub

Sub WorkerThreadForServer(s as object)

   Dim dbConn as SqlConnection
   Dim dbTrans as SqlTransaction

   Try
      dbConn = new SqlConnection
      dbConn.QueryString = QUERYSTRING
      dbConn.Open()

      dbTrans = dbConn.BeginTransaction()

      For Each w as WebServer in s
         InsertDataInDB()
      Next
      dbTrans.Commit()
   Catch(ex as Exception)
      dbTrans.Rollback()
   Finally
      dbConn.Close()
   End Try

   Interlocked.Decrement(ThreadCount)
End Sub

---- END pseudoCode ----

As far as I could gather on the net the above code SHOULD work fine,
but it stumbles on the transactions. If I remove the transactions the
code works fine, smoothly even.

During the process of inserting the data on the server I dump some
information on the screen and in a log file.

When there's no transaction I see information being dumped from all
servers at once, when the transactions are active, however, I see
information of only one server and then it throws an TimeOutException
while including the info.

I've checked, rechecked and triple-checked the code and I can't see
anything wrong.

Can anyone help me?

Regards,

PJ
http://pjondevelopment.50webs.com

30 Mar 2006 4:38 PM

Cowboy (Gregory A. Beamer) - MVP

Turn on SQL profiler and watch. When separate threads try a lock on the table
for insert, you will see the contention in the SQL profile. I am nearly 100%
sure this is where your deadlock is occuring, as this is the resource you are
using in your non-threadsafe code.

Too avoid this, add a Mutex to your threading model. The mutex (mutually
exclusive) will ensure no two threads can lock down the same resource. It is
not automatic (ie, you will have to include a bit of code to make it thread
safe), but it is far better than simply firing off threads and forgeting
about them.

Articles on thread safety, etc.
http://msdn.microsoft.com/msdnmag/issues/06/03/ConcurrentAffairs
http://msdn.microsoft.com/msdnmag/issues/05/10/ConcurrentAffairs
http://msdn.microsoft.com/msdnmag/issues/05/08/Concurrency
http://msdn.microsoft.com/msdnmag/issues/04/09/BasicInstincts
http://msdn.microsoft.com/msdnmag/issues/03/12/CriticalSections

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

Show quote

"pjondevelopm***@gmail.com" wrote:

> After four long hours of googling on the subject and finding almost
> nothing, I'm forced to post this one.
>
> I'm doing a small project that reads several IIS and stores in a
> database the domains hosted there.
>
> This application runs quite fine. But I'd like to add some transaction
> control on it, because it might ab-end at any given time (so far it
> didn't, but I think it's sheer dumb luck :-)
>
> Stripped out of anything meaningful code, my approach to create the
> transaction is:
>
> ---- BEGIN pseudoCode ----
> Imports System.Threading
>
> Private ThreadCount As Integer = 0
>
> Sub Main()
>    For Each s as Server In Farm
>       Interlocked.Increment(ThreadCount)
>       Do While Not ThreadPool.QueueUserWorkItem(AddressOf
> WorkerThreadForServer, s)
>          Thread.Sleep(100)
>       Loop
>    Next
>
>    Do While (ThreadCount <> 0)
>       Thread.Sleep(100)
>    Loop
> End Sub
>
> Sub WorkerThreadForServer(s as object)
>
>    Dim dbConn as SqlConnection
>    Dim dbTrans as SqlTransaction
>
>    Try
>       dbConn = new SqlConnection
>       dbConn.QueryString = QUERYSTRING
>       dbConn.Open()
>
>       dbTrans = dbConn.BeginTransaction()
>
>       For Each w as WebServer in s
>          InsertDataInDB()
>       Next
>       dbTrans.Commit()
>    Catch(ex as Exception)
>       dbTrans.Rollback()
>    Finally
>       dbConn.Close()
>    End Try
>
>    Interlocked.Decrement(ThreadCount)
> End Sub
>
> ---- END pseudoCode ----
>
> As far as I could gather on the net the above code SHOULD work fine,
> but it stumbles on the transactions. If I remove the transactions the
> code works fine, smoothly even.
>
> During the process of inserting the data on the server I dump some
> information on the screen and in a log file.
>
> When there's no transaction I see information being dumped from all
> servers at once, when the transactions are active, however, I see
> information of only one server and then it throws an TimeOutException
> while including the info.
>
> I've checked, rechecked and triple-checked the code and I can't see
> anything wrong.
>
> Can anyone help me?
>
> Regards,
>
> PJ
> http://pjondevelopment.50webs.com
>
>