Concurrent Access in Transactions

[Adnan]

Hey Ppl,

I'm developing an Online Auction Site using ASP.net and am
experiencing a problem with Transactions in ADO.Net.

When beginTrasaction() function is invoked from a specific connection,
and insertions are made into a table, no other connection can access
the table, until the transaction is committed. I've set the
Isoloation level to ReadUncommited.

This is a problem because, I'm expecting 100s of concurrent users, and
key tables being unavailable for reads (something like pessimestic
locking), would lead to disastrous performance.

What should I do?

Following is the code excerpt for your reference:



Public Function addItem(ByVal p_Name As String, ByVal
p_Description As String, ByVal p_ItemImage As HtmlInputFile, ByVal
p_Category1ID As Integer, ByVal p_EndTime As String, ByVal
p_StartPrice As Integer, ByVal p_ReservedPrice As Integer, ByVal
p_BidIncrement As Integer, ByVal p_MarketPrice As Integer, ByVal
p_Quantity As Integer, ByVal p_Warranty As Boolean, ByVal p_OwnerID As
String) As Boolean

'Use of Transactions in this routine may pose a problem
with multiple users

Dim sqlTransaction As SqlClient.SqlTransaction
Dim sqlConn As New SqlClient.SqlConnection

Try


sqlConn.ConnectionString = getDBConnString()

sqlConn.Open()
sqlTransaction =
sqlConn.BeginTransaction(IsolationLevel.ReadUncommitted)


Dim sqlString As String = "INSERT INTO Items (Name,
Description, StartTime, EndTime, StartPrice, ReservedPrice,
BidIncrement, Quantity, ExpectedMarketPrice, InWarranty,
OwnerID,SellTypeID,SellPrice) VALUES ('" _
& sqlEncode(p_Name) & "','" &
sqlEncode(p_Description) & "', { fn NOW() }, { fn NOW() }+" _
& sqlEncode(p_EndTime) & ", " &
sqlEncode(p_StartPrice) & "," & sqlEncode(p_ReservedPrice) _
& "," & sqlEncode(p_BidIncrement) & "," &
sqlEncode(p_Quantity) & "," & sqlEncode(p_MarketPrice) _
& "," & IIf(p_Warranty, 1, 0) & "," &
sqlEncode(p_OwnerID) & ",1," & p_StartPrice & ")"

DataAccess.executeQueryTI(sqlString, sqlConn,
sqlTransaction)


Dim dsItem As DataSet
dsItem = New DataSet
sqlString = "SELECT ItemID from Items WHERE Name='" &
sqlEncode(p_Name) & "' and Description='" & sqlEncode(p_Description) &
"' and ReservedPrice=" & sqlEncode(p_ReservedPrice) & " and
BidIncrement=" & sqlEncode(p_BidIncrement) & " and Quantity=" &
sqlEncode(p_Quantity) & " and ExpectedMarketPrice=" &
sqlEncode(p_MarketPrice) & " and OwnerID=" & sqlEncode(p_OwnerID)
DataAccess.getDataTI("item", sqlString, dsItem,
sqlConn, sqlTransaction)
'DataAccess.getData("Item", sqlString,
getDBConnString(), dsItem)

Dim itemID As String =
dsItem.Tables("item").Rows(0).Item(0)

'uploading the image

Dim fn As String =
System.IO.Path.GetFileName(p_ItemImage.PostedFile.FileName)
Dim SaveLocation As String =
"c:\Inetpub\wwwroot\ZabAuctionz\Data\" & itemID &
System.IO.Path.GetExtension(p_ItemImage.PostedFile.FileName)
p_ItemImage.PostedFile.SaveAs(SaveLocation)
sqlString = "UPDATE Items SET Image ='data/" &
sqlEncode(itemID) & System.IO.Path.GetExtension(p_ItemImage.PostedFile.FileName)
& "' WHERE ItemID=" & sqlEncode(itemID)
DataAccess.executeQueryTI(sqlString, sqlConn,
sqlTransaction)
sqlString = "SELECT "


sqlString = "INSERT INTO ItemCategories VALUES (" &
sqlEncode(itemID) & ", " & sqlEncode(p_Category1ID) & ")"
DataAccess.executeQueryTI(sqlString, sqlConn,
sqlTransaction)
sqlTransaction.Commit()

Catch ex As Exception
sqlTransaction.Rollback()
End Try

End Function

Is there any way to maintain transaction integrity, while allowing
concurrent access to the tables.

 

[William Ryan eMVP]

Adnan:

A few things. First, you are using Dynamic SQL and you aren't using
parameters. This is just a recipe for disaster.
Here's how to use Params w/ Stored procedures
http://www.knowdotnet.com/articles/storedprocsvb.html and here's a whole
discussion on Dynamic SQL and how to replace it (both why and how)
http://msmvps.com/williamryan/posts/4063.aspx

First off, it'd really help in every regard if you cleaned up the code.
There's some stuff that's defintely not necessary, Option Strict isn't on
(and Option Strict Off = Option Slow On AND Option ErrorProne On)

Next, when you do something like ExecuteNonQuery or ExecuteReader, there
will be a momentary lock on the record while the operation is being
performed. This is a safeguard of your data integrity. Now, looking at
that code below.. if you are doing this with SQL Server, I'd seriously
recommend losing the whole client side Transaction approach. At best you'll
have a solution that's never going to be very good. I'd replace this with
Stored procedures and implement the Transactions in the procs. In the
instance below, the flow of the transaction seems a bit bizarre. You have
more datasets than you need, you set the command text to "SELECT' then you
just write over it, it looks like setting the value to Select does nothing
in the one part b/c you immediately write over it. You always reference
colum0 and row 0 of the datatable,so if you are filling a datatable that
only is used to hold one row, you could probably work around this. You
could also make a field that indicates "OK". At first, set it to not ok..
At the end when you get all the data and you know everything is good you can
fire a query to make it "OK" and if that fails, you can have logic ot kill
the record. This is a convoluted way I know, but it wouldn't lock the table.

Anyway, I think the problem you are having is the structure of the
transaction.. I don't think you need to wrap everything like this and even
if I'm really misreading it and you do, it would definitely be better to
wrap it in a proc and do it server side. Transactions are just much more
natural there. I can see a few other problems here but correcting one thing
would break a few other things that are also wrong. If you at a minimum
parameterize this, it'll be a lot cleaner. For instance, can't you get that
fielname or restructure how that works so you can use this in another
command? I bet you could use some Output Parameters and get values back
differently and cut out the select statement, or at least append it onto the
insert. that p_OwnerID looks like a prime candidate for using Output params
but it's really hard to follow b/c of the dynamic sql.

I'll walk you through it if you want, but let me know first b/c I don't know
how much you are willing to change. Here's a starting place with params
(but seriously, if we can, let's go with Stored procs and server side
Transactions [Use StringBuilders too not b/c it's causing the problem but
just to be efficient)


Dim sqlStringBuilder as new StringBuilder
sb.Append("INSERT INTO Items(Name
, Description
, StartTime
, EndTime
, StartTime
, ReserevedPrice
, BidIncrement
, Quantity
, ExpectedMarketPrice
, InWarranty
, OwnerID
, SellTypeID
, SellPrice)
sb.Append(" VALUES @pName
, @pDescription
, Now()
, Now()
, @pEndTime
, @pStartPrice
, @pReservedPrice
, @pBidIncrement
, @pQuantity
, @pMarketPrice
, @pOwnerID
, @pWarranty ")

With cmd.Parameters
.Add("@pDescription", SqlDbType.Varchar, 50).value = p_Name
.Add("@pEndTime", SqlDbType.DateTime).Value = p_EndTime
'keep on here.
End With

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

 

[Adnan Haider]

Bill,

I'm really thankful for your detailed response.
I've carefully read the given links, and see the prudence in
parameterized queries and stored procedures. I'll be reworking the
highest-trafikked web-pages, according to the approach you demonstrated.

I made the decision to go with dynamic SQL, because I wanted to keep the
code database independent. Its currently on MsSql, I aim to shift it to
mySql latter. And I assumed that using storedProcs would've made porting
difficult.

Thanks again, will seek your advice along the way.

Regards,

A H