|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Sql Server LockHope everyone is having a nice day. I'm currently developing in ASP.NET and sql Server. Here's my problem. I have a database that acts like a queue. Users can add or delete lines from this queue. When the stored procedure sp_CalculationProcedure (see section 1.0)is called, based on whats in the queue certain other stored procedures are called. This stored procedure is fired off through an asynchronous call by asp.net. The main problem i'm having is when the queue contains one or two entries, the procedure runs as it should and all databases are appropriately inserted to and updated. However, when more than two entries are in the queue a database (sp_CalculationProcedure calls other procedures that will insert and update several databases )will randomly stall or timeout. Im not sure which is happening. But the only way to resolve this problem after a timeout like this is to restart the server. Another puzzling thing is that when I run the stored procedure, sp_Calculation procedure, in SQL Query Analyzer it runs perfectly fine regardless of the number of entries existing in the database queue. Anyone have any guesses to why its locking up like this or any ideas on how I could further test this application? Do you think the connection between ASP.NET and SqlServer is timing out? ******section 1.0:********* CREATE PROCEDURE sp_CalculationProcedure ( @startDate datetime, @endDate datetime ) AS DECLARE @tmp_select nvarchar(500) DECLARE @currentMeasureCode nvarchar(100) DECLARE @calculationProcedure nvarchar(500) DECLARE measureCodeCursor CURSOR FOR SELECT MeasureCode FROM tbl_Measure_Calculation_Queue ORDER BY MeasureCode ASC OPEN measureCodeCursor FETCH NEXT FROM measureCodeCursor INTO @currentMeasureCode WHILE @@FETCH_STATUS = 0 BEGIN CLOSE measureCodeCursor if ( ( @currentMeasureCode = 'expensePercentRevenue') OR (@currentMeasureCode = 'operatingProfitCustRevenue' ) ) BEGIN EXEC sp_CalculateRevenueHelper @startDate, @endDate; END else if ( @currentMeasureCode = 'cycleCountAccuracy' ) BEGIN EXEC sp_CalculateCycleCountHelper @startDate, @endDate; END else if ( @currentMeasureCode = 'inventoryQualityRatio' ) BEGIN EXEC sp_CalculateIQRHelper @startDate, @endDate; END else if ( @currentMeasureCode = 'poCreationDateVsSupplierInvoice' ) BEGIN EXEC sp_CalculatePOCreationInvoiceDiffHelper @startDate, @endDate; END else if ( @currentMeasureCode = 'annualizedOperatingProfitARandInv' ) BEGIN EXEC sp_CalculateROCEHelper @startDate, @endDate; END else if ( @currentMeasureCode = 'stockTransferOrdersShippingTime') BEGIN EXEC sp_CalculateSTODifHelper @startDate, @endDate; END OPEN measureCodeCursor -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM measureCodeCursor INTO @currentMeasureCode END CLOSE measureCodeCursor DEALLOCATE measureCodeCursor GO *******END SECTION 1.0********** Thanks, Ben Ben Ong wrote:
Show quote > Hi all, If you are going to open and close the cursor with each fetch, why use a > > Hope everyone is having a nice day. I'm currently developing in > ASP.NET and sql Server. Here's my problem. > > I have a database that acts like a queue. Users can add or delete > lines from this queue. When the stored procedure > sp_CalculationProcedure (see section 1.0)is called, based on whats in > the queue certain other stored procedures are called. This stored > procedure is fired off through an asynchronous call by asp.net. The > main problem i'm having is when the queue contains one or two entries, > the procedure runs as it should and all databases are appropriately > inserted to and updated. However, when more than two entries are in > the queue a database (sp_CalculationProcedure calls other procedures > that will insert and update several databases )will randomly stall or > timeout. Im not sure which is happening. But the only way to resolve > this problem after a timeout like this is to restart the server. > > Another puzzling thing is that when I run the stored procedure, > sp_Calculation procedure, in SQL Query Analyzer it runs perfectly fine > regardless of the number of entries existing in the database queue. > > Anyone have any guesses to why its locking up like this or any ideas > on how I could further test this application? Do you think the > connection between ASP.NET and SqlServer is timing out? > > ******section 1.0:********* > > CREATE PROCEDURE sp_CalculationProcedure > ( @startDate datetime, @endDate datetime ) > AS > > DECLARE @tmp_select nvarchar(500) > DECLARE @currentMeasureCode nvarchar(100) > DECLARE @calculationProcedure nvarchar(500) > DECLARE measureCodeCursor CURSOR FOR SELECT MeasureCode FROM > tbl_Measure_Calculation_Queue ORDER BY MeasureCode ASC > > OPEN measureCodeCursor > FETCH NEXT FROM measureCodeCursor > INTO @currentMeasureCode > > WHILE @@FETCH_STATUS = 0 > BEGIN > CLOSE measureCodeCursor > > if ( ( @currentMeasureCode = 'expensePercentRevenue') OR > (@currentMeasureCode = 'operatingProfitCustRevenue' ) ) > BEGIN > EXEC sp_CalculateRevenueHelper @startDate, @endDate; > END > > else if ( @currentMeasureCode = 'cycleCountAccuracy' ) > BEGIN > EXEC sp_CalculateCycleCountHelper @startDate, @endDate; > END > > else if ( @currentMeasureCode = 'inventoryQualityRatio' ) > BEGIN > EXEC sp_CalculateIQRHelper @startDate, @endDate; > END > > else if ( @currentMeasureCode = 'poCreationDateVsSupplierInvoice' ) > BEGIN > EXEC sp_CalculatePOCreationInvoiceDiffHelper @startDate, @endDate; > END > > else if ( @currentMeasureCode = 'annualizedOperatingProfitARandInv' ) > BEGIN > EXEC sp_CalculateROCEHelper @startDate, @endDate; > END > > else if ( @currentMeasureCode = 'stockTransferOrdersShippingTime') > BEGIN > EXEC sp_CalculateSTODifHelper @startDate, @endDate; > END > > OPEN measureCodeCursor > -- This is executed as long as the previous fetch succeeds. > FETCH NEXT FROM measureCodeCursor > INTO @currentMeasureCode > END > > CLOSE measureCodeCursor > DEALLOCATE measureCodeCursor > GO > > *******END SECTION 1.0********** > > Thanks, > > Ben cursor in the first place? Why not just select the TOP 1 from the table into a local variable? Also, you should specify read only for cursors that are not updated. I don't really see anything wrong with the piece of code you posted. Hi David,
Thanks for the quick reply. I changed the stored procedure from using a cursor to just selecting the top 1 from the table into a local variable. The database is still locking up on me. And you're right, I don't think it has anything to do with the code in the stored procedure because it executes without any errors in SQL query analyzer. Everything is updated as it should be and there are no locks on any of the databases. However, a database that gets information inserted into it will lock up when the stored procedure is executed asynchronously through ASP.NET. The database that locks up is usually the database that corresponds to the third or fourth entry in the queue. Anyone know why this is acting the way it is? Any tips on debugging this problem? Thanks, Ben Ben Ong wrote:
Show quote > Hi David, There is no asynchronous execution support in ADO.Net as far as I know. > > Thanks for the quick reply. > > I changed the stored procedure from using a cursor to just selecting > the top 1 from the table into a local variable. The database is still > locking up on me. And you're right, I don't think it has anything to > do with the code in the stored procedure because it executes without > any errors in SQL query analyzer. Everything is updated as it should > be and there are no locks on any of the databases. However, a > database that gets information inserted into it will lock up when the > stored procedure is executed asynchronously through ASP.NET. The > database that locks up is usually the database that corresponds to > the third or fourth entry in the queue. > > Anyone know why this is acting the way it is? Any tips on debugging > this problem? > > Thanks, > > Ben I thought that was an ADO.Net 2.0 feature. Could you explain more about your code. With asynchronous calls, you cannot use the same connection to perform additional tasks if it's involved in processing. You would require two connections to the server to run one asynchronously and then use the other for additional tasks. I'm still fairly new so I'm going to take a stab at this explanation of
what I'm doing. Please bear with my inexperience. The asynchronous call that I am using is part of the ASP.NET frame work called IAsyncResult. I'm only using one db connection( I created a singleton class to handle connections to the database ) to connect to the database. I think this structure may be the cause of why im getting timed out when I call the stored procedure through ASP.NET and why it seems to be running fine through SQL Query Analyzer. I will try implementing a second connection to handle the asynchronous call and keep another connection to just read from the database (i.e. retrieve data for display, etc.). I'll then look at the isolation level suggested by Mr. Kelly. I'll post the results on Monday seeing how it's Friday today and time to be let go. thanks for all the help guys. Ben Ong P.S. I removed all the "sp_" prefixes from my user stored procedures and to my disappointment, doing that did not solve my problem. For those who are interested about the "sp_" prefix in Sql Server Stored Procedures: I did some research and found that the "sp_" prefix is used by Microsoft to name system stored procedures. SQL server looks for "sp_" prefixed stored procedures in this following order :the master database, the stored procedure based on the fully qualified name provided, the stored procedure using dbo as the owner, if one is not specified. So if you had two similarly named stored procedures, one in the master database and one in a user database the one found in the master database would be executed. Be careful in that a lot of drivers will set the isolation level to
serializable by default. If you don't have a proper index it can really kill you. -- Show quoteAndrew J. Kelly SQL MVP "Ben Ong" <benis.***@gmail.com> wrote in message news:1121457680.632994.89690@g14g2000cwa.googlegroups.com... > Hi David, > > Thanks for the quick reply. > > I changed the stored procedure from using a cursor to just selecting > the top 1 from the table into a local variable. The database is still > locking up on me. And you're right, I don't think it has anything to > do with the code in the stored procedure because it executes without > any errors in SQL query analyzer. Everything is updated as it should > be and there are no locks on any of the databases. However, a database > that gets information inserted into it will lock up when the stored > procedure is executed asynchronously through ASP.NET. The database > that locks up is usually the database that corresponds to the third or > fourth entry in the queue. > > Anyone know why this is acting the way it is? Any tips on debugging > this problem? > > Thanks, > > Ben > First, you should remove the sp_ prefix and see what happens. This prefix
has a special meaning for SQL-Server and can lead to subtil bugs, especially when you are manipulating different databases. For the rest, you will have to take a close look at Locks, Indexes and Transaction problems. -- Show quoteSylvain Lafontaine, ing. MVP - Technologies Virtual-PC "Ben Ong" <benis.***@gmail.com> wrote in message news:1121453694.878405.284010@g49g2000cwa.googlegroups.com... > Hi all, > > Hope everyone is having a nice day. I'm currently developing in ASP.NET > and sql Server. Here's my problem. > > I have a database that acts like a queue. Users can add or delete > lines from this queue. When the stored procedure > sp_CalculationProcedure (see section 1.0)is called, based on whats in > the queue certain other stored procedures are called. This stored > procedure is fired off through an asynchronous call by asp.net. The > main problem i'm having is when the queue contains one or two entries, > the procedure runs as it should and all databases are appropriately > inserted to and updated. However, when more than two entries are in > the queue a database (sp_CalculationProcedure calls other procedures > that will insert and update several databases )will randomly stall or > timeout. Im not sure which is happening. But the only way to resolve > this problem after a timeout like this is to restart the server. > > Another puzzling thing is that when I run the stored procedure, > sp_Calculation procedure, in SQL Query Analyzer it runs perfectly fine > regardless of the number of entries existing in the database queue. > > Anyone have any guesses to why its locking up like this or any ideas on > how I could further test this application? Do you think the connection > between ASP.NET and SqlServer is timing out? > > ******section 1.0:********* > > CREATE PROCEDURE sp_CalculationProcedure > ( @startDate datetime, @endDate datetime ) > AS > > DECLARE @tmp_select nvarchar(500) > DECLARE @currentMeasureCode nvarchar(100) > DECLARE @calculationProcedure nvarchar(500) > DECLARE measureCodeCursor CURSOR FOR SELECT MeasureCode FROM > tbl_Measure_Calculation_Queue ORDER BY MeasureCode ASC > > OPEN measureCodeCursor > FETCH NEXT FROM measureCodeCursor > INTO @currentMeasureCode > > WHILE @@FETCH_STATUS = 0 > BEGIN > CLOSE measureCodeCursor > > if ( ( @currentMeasureCode = 'expensePercentRevenue') OR > (@currentMeasureCode = 'operatingProfitCustRevenue' ) ) > BEGIN > EXEC sp_CalculateRevenueHelper @startDate, @endDate; > END > > else if ( @currentMeasureCode = 'cycleCountAccuracy' ) > BEGIN > EXEC sp_CalculateCycleCountHelper @startDate, @endDate; > END > > else if ( @currentMeasureCode = 'inventoryQualityRatio' ) > BEGIN > EXEC sp_CalculateIQRHelper @startDate, @endDate; > END > > else if ( @currentMeasureCode = 'poCreationDateVsSupplierInvoice' ) > BEGIN > EXEC sp_CalculatePOCreationInvoiceDiffHelper @startDate, @endDate; > END > > else if ( @currentMeasureCode = 'annualizedOperatingProfitARandInv' ) > BEGIN > EXEC sp_CalculateROCEHelper @startDate, @endDate; > END > > else if ( @currentMeasureCode = 'stockTransferOrdersShippingTime') > BEGIN > EXEC sp_CalculateSTODifHelper @startDate, @endDate; > END > > OPEN measureCodeCursor > -- This is executed as long as the previous fetch succeeds. > FETCH NEXT FROM measureCodeCursor > INTO @currentMeasureCode > END > > CLOSE measureCodeCursor > DEALLOCATE measureCodeCursor > GO > > *******END SECTION 1.0********** > > Thanks, > > Ben >  |
|||||||||||||||||||||||
Other interesting topics