App puts MSSQL Server DB in "state" that can't be locked... 1

[OrthoDocSoft]

Folks,

As you know, my new app is running at my first beta site and is going well THANKS to so many of you!

To remind you, the app runs as "client" instances on about 8 different computers, some of which are (and all of which can be) connected to a single MS SQL server DB on a remote server on the network. Rare clients never disconnected.

When a client is connected it constantly queries the DB for new recordsets about every 3 seconds.

Today, I tried to connect a client to the system (which already had the client that never logs off on it), and I got an error message to the effect that:

Computer hist01 has the database in a state where new clients cannot lock...

blah blah blah (and I apologize that I did not write down the error message verbatim.) Hist01 is the actual name of the client that is always on.

Can anyone think of what the VB app can have done to "lock" the MSSQL Server DB so that no additional clients could lock on to it?

Thanks

"you cain't fix 'stupid'...

 

[gmmastros]

Usually....

The database itself is not locked. It's a lot more common for rows within a table to be locked. The usual condition for this is.... multiple clients trying to update the same data at the same time, or one client updating data while another is trying to read data.

What's worse is.... the way SQL Server locks data. With SQL Server, data is stored on the hard drive in 8k chunks (known as data pages). When data is locked, the whole page is locked. So, whatever rows happen to be in the same data page will also be locked.

There are ways to deal with this. You can do what's called a "dirty read". With dirty reads, it's possible to get "dirty" data. Think of it this way. Suppose you have a query that gets certain data from a table. But.... also suppose another application is in the process of deleting that data. When a delete occurs, SQL Server first locks all the data pages associated with the row(s) that will be deleted. Without dirty reads, your select query will not occur. Once the delete is done, the locks are removed and you can select again. If you are doing dirty reads, you will still be able to select the data, even while the delete is occurring. This means that you could possibly return data that you shouldn't (hence the term Dirty Read).

Let me ask this....

Can you show your connection string to the database?
What version of SQL Server are you using?
What Edition of sql server are you using (Express, Standard, Enterprise)?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Oh yeah. Almost forgot. Can you show the query that is executed every 3 seconds?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[OrthoDocSoft]

George,

Thanks! I got the error message and when I said "new clients cannot connect because it is locked..." it may have been "because a connection cannot be made..." or something to that effect. Again, I apologize that I did not copy down the error verbatim.

Here are the answers to your questions:

1) Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;InitialCatalogue=mydatabase;Data Source=myserver

2) 2005, and

3) Standard.

Thanks for your help.

Ortho


"you cain't fix 'stupid'...

 

[gmmastros]

Can you show the query that is executed every 3 seconds?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[OrthoDocSoft]

George,

Sorry. Here it is.

You all are going to laugh at this, I'm sure.

I am just getting comfortable with JOINs but I have not
incorporated that in the bit of code just yet.

This code looks for records with a certain OR (operating
room) status, and then, one by one, sees if those records have a certain AD (accession desk) status. If they do, they get added to a grid for viewing.

I tried to remove code extraneous to the actual DB query that executes every 3-4 seconds.

Dim adoRSFrozens As ADODB.Recordset
Dim adoRSAccessionDesk As ADODB.Recordset

Dim X As Integer
Dim intPendingAccessions As Integer

Dim strSQL As String
Dim strSQL2 As String

strSQL = "SELECT * FROM frozens" _
& " WHERE (ORStatus = 'ORSent' OR ORStatus = 'OREdited') AND" _
& " SpecimenType <> 'Permanent'"

Set adoRSFrozens = New ADODB.Recordset
Set adoRSAccessionDesk = New ADODB.Recordset

adoRSFrozens.Open (strSQL), adoConnection, _
adOpenKeyset, adLockOptimistic

If Not adoRSFrozens.BOF And Not adoRSFrozens.EOF Then

    intPendingAccessions = 0
    
    adoRSFrozens.MoveFirst

    For X = 1 To adoRSFrozens.RecordCount

        Dim strUniqueNumber As String
        Dim strRoom As String

        strUniqueNumber = adoRSFrozens.Field("UniqueNumber")
        strRoom = adoRSFrozens.Fields("Room")
    
        strSQL2 = "SELECT * FROM accessiondesk WHERE" _
        & " UniqueNumber = '" & strUniqueNumber & "' AND" _
        & " Room = '" & strRoom & "'"
        
        adoRSAccessionDesk.Open (strSQL2), adoConnection, _
        adOpenKeyset, adLockOptimistic
        
        'there should only be one record matching
        If Not adoRSAccessionDesk.BOF or Not
        adoRSAccessionDesk.EOF Then
        
            If Len(adoRSAccessionDesk.Fields("ADStatus"))
            <  2 Then
            
                adoConnection2.BeginTrans
                
                    adoRSAccessionDesk.Fields("ADStatus") _
                    = "ADAware"
                    
                    adoRSAccessionDesk.Update
                
                adoConnection2.CommitTrans
            
            'if there is a value and its NOT ADAccessioned, and
            'it is NOT ADFreshComplete, it must be ADAware....
            ElseIf adoRSAccessionDesk.Fields("ADStatus") _
            <> "ADAccessioned" And adoRSAccessionDesk.Fields _
            ("ADStatus") <> "ADFreshComplete" Then
            
                intPendingAccessions = intPendingAccessions + 1
            
                'THEN I PUT THIS RECORD ON A DATA GRID
                'CODE OMITTED FOR CLARITY  
                                    
                End If
            End If
        End If
    
    adoRSAccessionDesk.Close
    
    adoRSFrozens.MoveNext
       
    Next X

End If

adoRSFrozens.Close

Set adoRSFrozens = Nothing
Set adoRSAccessionDesk = Nothing

"you cain't fix 'stupid'...

 

[gmmastros]

It looks like there are 2 things going on here. You are updating the AccessionDesk table based on certain conditions and you are displaying data based on another condition.

Since you are trying to accomplish 2 things at the same time, I would encourage you to make a stored procedure for this. Don't worry if you've never made a stored procedure, I'm gonna show you how.

First, open SQL Server Management Studio. Click "New Query". Change the database dropdown so that shows your database.

Copy/Paste this code.

Create Procedure dbo.[!]ThreeSecondQuery[/!]
As
SET NOCOUNT ON

Update AccessionDesk
Set    ADStatus = 'ADAware'
From   Frozens
       Inner Join AccessionDesk
         On  Frozens.UniqueNumber = AccessionDesk.UniqueNumber
         And Frozens.Room = AccessionDesk.Room
Where	Frozens.ORStatus In ('ORSent','OREdited')
		And Frozens.SpecimenType <> 'Permanent'
        And Len(ADStatus) < 2

Select [!]*[/!]
From   Frozens
       Inner Join AccessionDesk
         On  Frozens.UniqueNumber = AccessionDesk.UniqueNumber
         And Frozens.Room = AccessionDesk.Room
Where	Frozens.ORStatus In ('ORSent','OREdited')
		And Frozens.SpecimenType <> 'Permanent'
        And AccessionDesk.ADStatus <> 'ADAccessioned'
        And AccessionDesk.ADStatus <> 'ADFreshComplete'
        And Len(ADStatus) >= 2

Now, I think the above code is correct, but there may be some tweaking you'll need to do. Also, notice the parts in red. You should certainly change those parts. First, give this stored procedure a better name. Naming things appropriately is important. Second, you should almost never return all the columns from a table. If you actually plan on using them all, I would still suggest that you list out the columns. In this case, replace the * with columns that you are actually using to display that data in the grid.

Next, change your VB code to call this stored procedure instead of your original code.

Dim adoRSFrozens As ADODB.Recordset
Dim adoRSAccessionDesk As ADODB.Recordset

Dim X As Integer
Dim intPendingAccessions As Integer

Dim strSQL As String

strSQL = "ThreeSecondQuery"

Set adoRSFrozens = New ADODB.Recordset

adoRSFrozens.Open (strSQL), adoConnection, _
[!]adOpenForwardOnly, adLockReadOnly[/!]

If Not adoRSFrozens.BOF And Not adoRSFrozens.EOF Then

    'THEN I PUT THIS RECORD ON A DATA GRID
    'CODE OMITTED FOR CLARITY  

End If
    
adoRSFrozens.Close
Set adoRSFrozens = Nothing

Notice that we are no longer updating data from the VB code. In fact, we are just displaying the data returned. So, we can change to a forward only, read only cursor. This will also improve performance.

Lastly... we need to talk about your status columns. You should really change your database structure. You see... the wider your table, the worse your performance. Hard drive access is many times slower than memory access. Minimizing the actual hard drive access will speed up your performance. To do this, you should create a couple new tables. For example, you should create an ORStatus table with an identity column for the primary key, and a description column. There would be multiple rows in this table. Something like this...

[tt]
ORStatus
OrStatusId Description
---------- -----------
1 ORSent
2 OREdited
[/tt]

Then, modify the Frozens table so that you store the ORStatusId instead of the whole description. With a table structure like this, you will use less space to store your data, and your performance will improve.

You see, SQL Server stored data on the hard drive in chunks, called data pages. A data page is 8K. By replacing strings with integers, you will ultimately be able to reduce the size required to store each row. This, in turn, allows you to store more rows per data page, which also reduces your hard drive access. Ultimately, your overall performance will increase.

You should do this with all the columns in your database that store look up values. Ultimately, you will have more tables. But... the smaller tables will have long descriptions, but few rows. Your tables with a lot of rows will have smaller widths because you will be storing more integers (which take 4 bytes) instead of longer strings (which take a lot more than 4 bytes).

Now, understand this this still does not fix your original problem. Don't misunderstand me though. By using this code, I would expect the problem to improve because your update code will be many times faster. It's probably the update that is causing the locks to occur in the first place.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[OrthoDocSoft]

George,

"Gulp"

This is very scary for me, but I know you are right. And your reply is easily understandable and is absolutely awesome. This will be my first stored proceedure, and you make it look easy as pie. (I know I will regret saying that last bit later...)

I understand the lookup table idea as you presented. So do I "InnerJoin" also on the lookup table when retrieving data so that I have the status description corresponding to the status ID? Is that what you would do.

This will take some time. I may be back with you later.

Ortho

"you cain't fix 'stupid'...

 

[gmmastros]

Yes.

People often think that it will be faster to store all the data in a single table so that you don't need to join to other tables. This is simply not true. You see, most of the time, lookup tables are small (sometimes very small). Regardless, your look up table will have a clustered primary key on the lookup id column. This means SQL Server will be able to use indexes to lookup the value and the performance will be very fast. Very, very fast.

There are other advantages too. It boggles the mind, but... MANY applications have a database sitting behind it. Databases have a lot of mechanisms associated with them to make sure your data is as clean as possible.

With lookup tables, you are presented with an excellent oppurtunity to make sure your data is kept clean. Of course, what I'm talking about is called a foreign key. You see, after creating your lookup table, you should also establish a foreign key constraint betwen the two tables. What this will do is.... make sure you don't have any values in the Frozens table (ORStatusId column) where those status values do not exist in the ORStatus table.

Now again, many developers overlook this fantastic oppurtunity. They think.... I control that app that talks to the database so I can make sure all the values are good. This may work well with a small application that has a relatively small numer of tables, but as your application progresses (in size and complexity), it becomes more and more difficult to remember all these rules. Also, when there are multiple developers on the same app, it's difficult for them to know all these rules too.

Imagine you owned the company developing this app, and right now there are just a couple employees (maybe even just one). Fast forward a couple years. Now there are 10 developers working on the app, some of them with a lot of experience and some without. A junior developer comes to you and says, "I am getting a foreign key violation error while trying to import a new hospitals data in to this table". Without the foreign key constraint, this new guy would have kept right on moving with with bad data imported in to this table.

In my opinion...

Data is the most important asset you own. Protect it well, both with the database itself and also with the application that interfaces with it.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[OrthoDocSoft]

George,

I saved the stored proceedure as "ThreeSecondQuery" (not really, but sticking with your example) in "Projects" in a subfolder named "MyFolder". When I replaced strSQL with "ThreeSecondQuery" VB said "Cannot find stored proceedure"? Do you know why?

Thanks,

Ortho

"you cain't fix 'stupid'...

 

[OrthoDocSoft]

George,

Ran the stored procedure from MS SQL, and then the VB6 application could see it.

But now, there is a record in the DB that appears to meet the criteria I'm looking for, but the stored procedure won't "get" it, ie,: adoRSFrozens.RecordCount = -1.

Also, when I try to re-execute the stored procedure from MS SQL, I'm getting this message:

Msg 2714, Level 16, State 3, Procedure UpdateAccessionsGrid, Line 14
There is already an object named 'UpdateAccessionsGrid' in the database.

Appreciate your help.

Ortho

"you cain't fix 'stupid'...

 

[PatrickIRL]

Ortho, if you need to change the SP and run it again, you need to check if it already exists ...

IF EXISTS (
  SELECT * 
    FROM INFORMATION_SCHEMA.ROUTINES 
   WHERE SPECIFIC_SCHEMA = N'<SP NAME>'
     AND SPECIFIC_NAME = N'<SP NAME>' 
)
   DROP PROCEDURE <SP NAME>
GO

Also, if you execute the SP within SQL Server does it return the rows??

Patrick

 

[OrthoDocSoft]

Patrick,

Thanks. As you may have gleened from the thread, this is my FIRST stored procedure ever.

This morning I fired up my MS SQL Studio, and "opened" my files (specificially "Projects/MyProject/MyStoredProdedure") and hit "Execute!" to see if it would work. Again, I got the message "there is already an object named.... in the database" like in my previous post. I don't exactly understand this.

A couple of times when I have hit "Execute!" I have gotten a message the the query "worked" even tho it did NOT return the row.

So do I put YOUR code in the SP, ON TOP, OR BELOW what George gave me? If you could be more specific since I'm a SP noob.

And, no, the row has never been returned, even tho my old code (posted many replies ago) still works, and returns the row.

Appreciate your help, and Georges.

Ortho

"you cain't fix 'stupid'...

 

[gmmastros]

Stored procedures are compiled in to the database. What I usually do is.... I open SQL Server Management Studio. Set the database (using the dropdown) write my procedure, and then execute it. When you execute this, you are actually executing the 'Create procedure'. You are not executing the code within the procedure. To execute the code within the procedure, you need to call it specifically.

For example:

[tt][blue]
Create Procedure dbo.NameOfProcedure
As
SET NOCOUNT ON
Select Blah From Blah
[/blue][/tt]

When you execute the code above, you will NOT receive data from the table. Instead, the execute will CREATE the stored procedure. This stored procedure will be saved in the database and is available to be called any time you want.

If you want to test the procedure from a query window, you would do this:

[tt][blue]
Exec dbo.NameOfProcedure
[/blue][/tt]

When you execute the code above, the stored procedure will run, and you will get the results of the procedure to show in a grid.

When I create a new stored procedure, I usually start with identifying the parameters, and write code like this:

Declare @EyeColor VarChar(20)
Declare @ShoeSize Decimal(3,1)

Select @EyeColor = 'Brown', @ShoeSize = 9.5

Then I press F5 to make sure I didn't make any mistakes (like spelling).

Then, I start writing my query, something like:

Declare @EyeColor VarChar(20)
Declare @ShoeSize Decimal(3,1)

Select @EyeColor = 'Brown', @ShoeSize = 9.5

[red]
Select  *
From    SomeTable
Where   EyeColor = @EyeColor
        And ShoeSize = @ShoeSize
[/red]

I press F5 again to make sure I didn't make any mistakes and that the query returns the correct data. If everything works the way I expect it to, I then make the procedure, like this:

[red]Create Procedure dbo.GetDataForEyeColorAndShoeSize
  @EyeColor VarChar(20),
  @ShoeSize Decimal(3,1)
As[/red]

Select  *
From    SomeTable
Where   EyeColor = @EyeColor
        And ShoeSize = @ShoeSize

This time, pressing F5 does appear to do anything, but it actually creates the procedure. The last step for me is to test the procedure, like this:

[red]Alter[/red] Procedure dbo.GetDataForEyeColorAndShoeSize
  @EyeColor VarChar(20),
  @ShoeSize Decimal(3,1)
As

Select  *
From    SomeTable
Where   EyeColor = @EyeColor
        And ShoeSize = @ShoeSize

[red]
Go
Exec dbo.GetDataForEyeColorAndShoeSize 'Brown',9.5
[/red]

Notice I changed the create to ALTER. I also put GO at the end and added my exec call (to call the procedure). GO is considered a "batch separator". This is unique to SQL Server Management Studio (and the old Query Analyzer). GO will separate blocks of code. For example, you could have a SQL script that creates a table, creates some indexes, populates some data, and creates a couple stored procedures. You cannot run all these commands at the same time, but you can put them in the same query window, separated by GO's, and they will each execute separately.

Lastly, it's considered best practice to also SAVE the script. (Here's another source of confusion.) When you click File -> Save, what actually happens is... the contents of the query window are saved to an ASCII file. It does NOT affect the database at all. Best practice states that you should save all the scripts and put them in to a version control system (like SourceSafe). Then, every time you want to modify a procedure, you check out the script file, open in SSMS, modify it, execute it (to compile it in to the database) and then re-save it, and check it back in to source control.

You mentioned earlier that you were just beginning to learn about joins. There is a method within SQL Server that I think you will find helpful. Let me explain:

Open SQL Server Management Studio
In the Object Explorer (on the left) drill down to your database.
You will see a category list of objects (Diagrams, Tables, Views, Synonyms, etc...) Right click Views, then click New View
Select the tables you want to join. (use CTRL-click to select multiple tables).
Click ADD. Click Close.
You will now see a window that shows your multiple tables at the top, and some other info.

You can click on the columns to add that column to the output. But.... you can drag a column from one table to a column from the other table. This will draw a line from one table to another. By default, this will make an Inner Join for you. You can right click the line and change it to a left join or right join.

You can also set sort orders and filter conditions.

SQL Server will build the SQL statement for you. You can copy/paste this query and do anything you want with it.

Long term, you will NOT want to rely on the view builder tool because there are some serious limitations. As a learning tool.... it's pretty good.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[OrthoDocSoft]

George,

I swear I'm going to have to pay you for these elegant explanations, and I'm sorry for being such a dunderhead. OF COURSE I see that executing a "CREATE"... is going to give me precisely the error that I received. Tried "EXECUTE" like you said and it worked like a charm, and returned the record I expected.

Different story, tho, when I tried this in my code.

Here it is:

dim adoRSMyRecordSet as ADODB.Recordset

set adoRSMyRecordSet = new ADODB.Recordset

strSQL = "ThreeSecondQuery"

adoRSMyRecordset.Open (strSQL), adoConnection, _
adOpenStatic, adLockOptimistic

If not adoRSMyRecordset.BOF = true or not adoRSMyRecordset = true then

intCount = adoRSMyRecordset.Recordcount

(PS: I changed the adOpenStatic/adLockOptimistic bits to what you suggested, but that didn't cause it to be successful. When we get this to work, I WILL switch it like you suggested.)

I know that the code "finds" the query as a stored procedure in MSSQL, because I purposefully misspelled the precedure name and got an error. There is no error when I spell it correctly.

Problem is when I do my "test" to see if records are present -- the line about BOF/EOF, neither condition is true, telling me there IS at least one record. But when I check the recordcount, the value is "-1" (minus one), instead of "1" like I expect, and the record is not found. Can you see the problem?

Thanks,

Ortho



"you cain't fix 'stupid'...

 

[gmmastros]

Ah. The stinkin' record count problem. There are some people that swear it is completely unreliable. In my experience, RecordCount works properly, when you know how.

In your case, you expect to get one row returned from the database, right? How big can one row be? Probably very small. So, you never really had to think about this stuff. But, consider a very large recordset, something in the order of 100 megs. It's going to take a little while to transfer that over the network (from SQL computer to VB computer). To alleviate this problem, ADO supports server side cursors and client side cursors. With a client side cursor, all of the data requested will be immediately transferred to the client computer. With a server side cursor, only a limited number of rows will be transferred. Then, as you need more data, more will be sent over. Imagine you had a large grid of data, with thousands of rows. Also imagine that only 10 rows are visible on the screen. It may take a while to completely populate the grid. But, with a server side cursor, you can transfer the first 10 rows and then only get more data (across the network) when the user scrolls the grid down.

With a server side cursor, ADO doesn't necessarily know how many rows are in the result set, so record count returns -1.

There are a couple of other things you should do. If you don't plan on updating the data you return (and stored procedures are not update-able anyway), you should NOT use Lock Batch Optimistic.

I suggest you change your code like this:

dim adoRSMyRecordSet as ADODB.Recordset

set adoRSMyRecordSet = new ADODB.Recordset

strSQL = "ThreeSecondQuery"

[!]adoRSMyRecordSet.CursorLocation = adUseClient[/!]

adLockReadOnly.Open (strSQL), adoConnection, _
[!]adOpenForwardOnly, adLockReadOnly[/!]

If not adoRSMyRecordset.BOF = true or not adoRSMyRecordset = true then

intCount = adoRSMyRecordset.Recordcount

Forward only will prevent you from navigating backward through the recordset. Specifically adLockReadOnly.MovePrevious will cause an error, but adLockReadOnly.MoveNext will be fine. Forward Only gives you slightly better performance than any other option. Also, setting LockReadOnly will give you better performance too. If you set any other locking type, the data in the table will be locked (preventing others from working with the data).

If you make the changes I suggest here, your RecordCount property will return the correct value.

While you are at it, you should also take a good hard look at the rest of your database code. Anywhere you return a recordset and don't update the data, you should change the recordset options like I suggest above. Your performance will improve and you will have less locks on your data, too.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[OrthoDocSoft]

Dear George,

It all worked, just like you said.

You have helped me:

Write my first MSSQL stored procedure,
Write my first client-side cursor,
Write my first adOpenForwardOnly/adLockReadOnly DB read for efficiency, and,
taught me about a thing or two about .RecordCount

and it was all AWESOME! I cannot thank you enough! You are incredible and that was SO HELPFUL.

THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU THANK YOU.

Ortho



"you cain't fix 'stupid'...

 

[gmmastros]

You're welcome.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom