> On 10/12/2004, at 9:55 PM, Mike Scally wrote: > > Maybe that is the case, but my transactions were causing blocking under > heavy load but when I moved the code including transactions to a stored > procedure I haven't seen the problem arise since.
Although databases are now easily set up and administered by anyone and applications like witango allow non DBAs and non database programmers to create complex database driven applications there is still a lot you must consider to be able to manage your database efficiently with your multi user applications. The following article is a light read that highlights the issues of blocking on a database. http://vyaskn.tripod.com/anti_blocking_strategies.htm In a multi user application like a web app you need to understand these blocking/deadlocking issues intimately. The article focuses on MS SQL but the points it raises and discusses are relevant to any rdbms. The reason that a stored procedure would stopp blocking on a db under load is because the resultsets for each witango database action are not being retrieved from the database and hence the transaction load and length has been shortened. When a stored procedure is called by the witango server it will only retrieve the first resultset along with the out parameters. All other accumulated resultsets are basically ignored as they could be different in structure and witango would be unable to create a n x m array of the results. By putting all the sql staements into a single stored procedure you: 1. remove the data transfer overhead from the db server, network and witango server 2. remove the need to process each resultset into an array on the witango server 3. reduce the SQL parsing and preparation time on the witango server and dbms 4. make the transaction a single connection sql statement to the dbms In essence you have optimised the transaction. This is a good thing. The problem will still appear under very heavy load but the stored procedure's effect will have reduced the processing on the db to a considerably shorter time and minimised the risk of 2 transactions colliding or blocking each other. When you start getting deadlocks and blocking returning to the system under load the only solution now will be to add faster HW (disks, CPUs, RAM, Bus). Phil ________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
