473,324 Members | 2,501 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Concurrent Access in Transactions

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.ReadUncomm itted)
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.
Nov 20 '05 #1
2 2342
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" <ah******@yahoo.com> wrote in message
news:ad**************************@posting.google.c om...
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.ReadUncomm itted)
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.

Nov 20 '05 #2
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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 20 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: Eloff | last post by:
This is not really Python specific, but I know Python programmers are among the best in the world. I have a fair understanding of the concepts involved, enough to realize that I would benefit from...
1
by: Robert Zurer | last post by:
My architecture is, (ala Fowler PoEAA) Presentation Layer | Service Layer | Problem Domain (My Business Objects are held in memory) | Persistence Layer
11
by: Durai | last post by:
Hi All, I tested "concurrent testing" in MySQL. It works fine. But I couldn't do in PostgreSQL 7.3.4 on HPUX IPF. I got deadlock problem. I used the PHP script to update table( one script...
8
by: pnp | last post by:
Hi all, I've developed a win C# app that is actually database driven using SQL server 2000. The idea is that only one application will be installed on a server in a network and the program will be...
5
by: Jill Graham | last post by:
Hi, What is the best way to resolve following problem in a multi-user environment ? I have a table called TOTAL_SALES. The table has 2 columns : "CarId" and "TotalSales" I need to access a...
2
by: Sridhar | last post by:
Hi, I am trying to implement sql transactions. But I am not knowing how to do that. I created a data access layer which contains methods to select/insert/update tables in a database. I have also...
9
by: rysch | last post by:
Hi, I am building a warehouse database for a small non-profit organisation that works in Africa. Currently, I have created a data entry form and table. The table is called: Inventory Transactions....
3
by: Michael Schöller | last post by:
Hello, First of all english is not my natural language so please fogive me some bad mistakes in gramatic and use of some vocables :). I have a great problem here. Well I will not use it...
0
amitpatel66
by: amitpatel66 | last post by:
There is always a requirement that in Oracle Applications, the Concurrent Program need to be execute programatically based on certain conditions/validations: Concurrent programs can be executed...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.