VFP deadlocking in SQL Server 2000 1

[zer0n3]

Greetings All!

I've spent some time reviewing and doing some searching prior to this post but I couldn't confirm if I've found the right information for my issue.

I, as a SQL Server DBA, recently have been dealing with a deadlock situation between two different applications in my server. One application is VFP which is writing to a SQL table through a multi-join SQL server view.

This is the way the the Foxpro dev and the other dev's had set this up but I am here to gather information on the best practices on reading and writing to another DBMS like SQL server.

I believe the VFP dev is using version 9.

The way the process currently works is that the application connects to SQL server, does a lookup through view "Accounting" and then does an update to the same record it just found through the same view ("Accounting") which is a multi joined view. This whole VFP process is killing a simple select process multiple times a day forcing it to rollback.

So my goal here is to find ways to tone down the locking VFP has on this table during this whole process.

One thing that needs to change is that the query against the view should be read-only and that the update should be done directly to the table or through another view that isn't joined to other tables but I am not sure where to look in VFP to see that a certain object is set to read only or not.

Any other advice of what to review in VFP would be most helpful.

Kind Regards,
-Z

 

[Mike Lewis]

Hi Z,

One thing that needs to change is that the query against the view should be read-only and that the update should be done directly to the table or through another view that isn't joined to other tables but I am not sure where to look in VFP to see that a certain object is set to read only or not.

That certainly looks like a good place to start.

In VFP, it's not a question of any object being set to read-only or not. But, rather, of how the query and updates are actually coded.

Can you give us any more information about what is happening in the VFP code? There might be a remote view with update criteria enabled (a remote view is a view that lives in a Foxpro database but which connects to tables on the back end).

Or, there might be a SQL SELECT statement that retrieves data into a local cursor, and further code to make the cursor updateable.

Or, there might be something called a cursoradapter, which is indeed an object, with properties set to make it updateable.

In general, the best approach in VFP is not to use updateable cursors or remote views to perform the updates, but to explicitly send UPDATE, INSERT and DELETE statements. If we knew what the VFP code is doing, we could give you advice on how to change it.

Sorry if this is a bit vague, but VFP is a big product, and there are many different approaches the developer might have taken.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[zer0n3]

Mike, thank you for your response!

Since you've explained the various methods now I have enough information to work with the dev on changes.

What I do recall seeing in his VFP application looked like a table but it was a link to the view so I think he might be using a remote view and passing an update through that.

Kind Regards,
-Z

 

[zer0n3]

The developer confirmed he is doing updates through a remote view.

He agreed it would be better to do it through a SQL statement and set the remote view to read-only. So now it's time for a code change!

Thank you again Mike!

 

[Mike Lewis]

Z,

Glad to have helped.

Keep in mind that he can still use the remote view to retrieve the data (in place of a SELECT). The important thing is to explicitly send the updates, inserts and deletes.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk