Threads and Transaction Problems....

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(ThreadCoun
t)
Do While Not ThreadPool. QueueUserWorkItem(Ad
dressOf
 WorkerThreadForServe
r, s)
Thread.Sleep(100)
Loop
Next

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

Sub  WorkerThreadForServe
r(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(ThreadCoun
t)
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

Move your COMMIT to outside the Try-Catch block.

The logic should be:

Do tasks
If failure, roll back
Else Commit.

Your logic is

Do tasks
Commit
If Failure roll back.


--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP



< pjondevelopment@gmai
l.com> wrote in message
news:1143828352.786554.44810@i40g2000cwc.googlegroups.com...
> 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(ThreadCoun
t)
>      Do While Not ThreadPool. QueueUserWorkItem(Ad
dressOf
>  WorkerThreadForServe
r, s)
>         Thread.Sleep(100)
>      Loop
>   Next
>
>   Do While (ThreadCount <> 0)
>      Thread.Sleep(100)
>   Loop
> End Sub
>
> Sub  WorkerThreadForServe
r(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(ThreadCoun
t)
> 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
>