MS SQL Server - Threads and Transaction Problems....

Author

Threads and Transaction Problems....

pjondevelopment@gmail.com

2006-03-31, 1:23 pm

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

Geoff N. Hiten

2006-03-31, 8:23 pm

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
>

Sponsored Links