Pro VB DatabasesAdvanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
Problem: When trying to access data from the SQL Server 2000 database. My program will receive a timeout expired error if another user is accessing data also. It is obvious to me the reason for the timeout is due to the locking of the database objects. The database is using the default level of isolation Read committed. However, the SQL statements are using hints to set the locking level. Read only queries are using NOLOCK. Update queries are using UPDLOCK and Insert queries are using ROWLOCK. This has done a great deal to reduce the number of timeouts. I have also revised my application to shorten the transactions, that is the time\code between BeginTrans and CommitTrans. All of this has helped to make the data more accessible and the application more efficient. However, the application is still in the debug and testing phase with only two users and I am experiencing access problems.
Situation: I have created a document manager application. The database one central table, among many related table, called tblDocRegister. It acts as a registry for all of the documents. Users can check documents out, check documents in, add new documents, get a read only copy, etc. All of these actions require an entry in tblDocRegister to maintain the state of the document or they at least have a relationship to the register table because of the document id.
I have been able to resolve problems with the read only data access, queries that provide information to the user in the GUI, by using the NOLOCK hint. However, if more than one user is trying to check out a document I am running in to timeout problems. As I mentioned, this is happening with only two users! In production there could be over a hundred users. Still, I know there are applications out there handling thousands, if not hundreds of thousands of users.
I am kind of surprised to be experiencing this. I have used SQL Server in a limited role previously. I have quit a bit of experience with Access on the back end. And all that I have been reading in regards to Database programming never really seemed to address this. There has to be some level of locking I don't want it to be a free for all, but yet there has to be a high degree of access as well.
Just to clarify, Are you using a column in the table to denote a checkout, or are you leaving the row locked to denote the check out. You should not be holding any locks for any longer than necessary. Most document management systems have columns to denote that a record is checked out, who to, when it was checked out etc. (I have both worked with some of the best 3rd party EDM systems (FileNET, Watermark, Documentum), and have written them from scratch.)
Secondly, you are talking about doing explicit transactions. This is typically done for one of two reasons 1) You are touching more than one table. 2) You are locking from a middle tier, and have another repository that you have to do updates to, and both updates need to be either committed or rolled back. In most cases, a checkout shouldn't fall into either of these categories. After all, the update for the checkout should just be marking that the document is checked out. This is not dependent on other data, and can be put back into it's original state. E.g. There is no reason to hold the update of the checkout while the document is being delivered to the client. If the delivery fails, then you can simply do another update to undo the checkout. The point of checking in/out is that once the document record is checked out, no one else should be able to alter the data in record (your software should enforce this, not the DB).
If you are doing your locking/updating properly, you shouldn't be holding locks for very long, and even if the second checkout attempt hits a locked row, the row shouldn't stay locked for long enough to cause a timeout.
I found your reply very interesting and wanted to at least say thank you for taking the time to respond. For some reason I never recieved your reply in my email I just happened to browsing this site and wanted to review my post.
I have been able to gain considerable improvement by moving all of the SQL calls to the SQL server as stored procedures. Well, most all of them still have a few to go.
In regards to denoting a document as being checked out I am using columns to indicate the current state of the document and if it is checked out who has control of the document.
In regards to the data and the transfer of the documents being mutually independent I have been thinking about that for the last couple of days or so. Currrently, I am using transactions to control/committ changes to the database. The rollback method seems to be very clean and efficient without much coding. If I were to make the transfer of the physical document independent of the data then I imagine I would have to add a column, such as TransactionID, to each table affected by the update. There are/can be a couple of un-related tables affected by a document being checked in.
Here is at least one reason I have the physical transfer of the documents and the data updates mutually dependent. The first part of the process involves copying all of the documents being entered into the EDM to a temporary folder. All of the actions are taking against the temp folder. Following a successful transfer of the documents to the vault, a secured server, the database updates begin. If both the transfer and the updates complete successfully then the database transaction is committed. Also, the temporary folder and the original folder are then deleted. Should any part of the transfer process fail, compression, etc. then the temp file is deleted, and the newly added documents are removed from the server. The database transaction is rolled back and the original documents have not been altered.
It is imperative to ensure the transfer and the database entries have completed successfully prior to deleting the original documents. Perhaps it is a design flaw I have introduced from the begining. But, if the documents are transfered successfully, then deleted, and something should fail in the database transaction. I leave myself open for another failure when trying to restore the original documents. If the transfer fails and the datbase transaction completes then I have to do some fairly elaborate coding to restore the database.
One of the more complex aspects of the EDM application we created is the documents can be related to each other and the application creates the relationships when documents are checked in. As an example, If I were to check in a project folder conataining several hundred documents related to a specific job. By providing two additional peices of information, Customer Name and any top level documents in the folder. I can create a relationship with customer, work order, top level documents. Also, if the folder contains AutoCAD files the application will automatically identify the sub-assemblies and capture the attributes. Many of these tables are related based on the document id. However, it is necassary to create some relationships independent of the document id. Perhaps this is the way all EDM applications work? I am not sure.
Just an interesting fact. I was able to retrieve the customer\work order data and other related information from existing company data. This allowed me to provide the user with a drop down box for the work order. By selecting the work order the user gets the customer name and other pertinent data is loaded in the background giving us the ability to integrate this data with existing company data. It is all dynamic and current. Also, documents can be entered independently of any relationship to a specfic customer or work order. So there is some flexibility.
Probably more than you wanted to know, But thanks for listening. This is my first EDM application and I do appreciate the feedback.
i have same situation as you mention. i have a asp.net apps which run as webservice will received more than 10 request per-second. each request will be given a running no which is stored in a table (TBLRUNINGNO column next_id). i use single stored procedure to select/update to increate the next_id.
sometime, i will get the next_id is duplicating in my transaction log and this has cause the problem for not able to return the result to that request.
i been try to use ROWLOCk, SERIALIZABE in my store procedure but it still doens't help to resolve this problem. some more i can see there is a error message in the event viewer saying "the process id(xxx) has encounted deadlock". do you mind to share your experince to me on how to workaround this issue ?
i'm using the sqlclient.ExecuteScala to call the store procedure to get the next_id. the store procedure has begin transaction...commit transaction
If you are trying to get the "next id". I guess we need more information. I believe that the issue is probably in your stored procedure. I am going to make an assumption that you have a table, lets call it tblNextId. This table has only one field fldNextId (of some numeric type). This being the case I would write the SP something like this.
CREATE PROCEDURE GetNextId
DECLARE @MyNextId int -- Or whatever datatype is correct.
What this will do is it will return the value in the table in the next id, then it will update the record to increment the id by 1.
This probably should not be run in another transaction. Note this is important, because if it is run in another transaction, then no one else will be able to get an id until the rest of the transaction is complete.
What if the rest of the stuff I am doing fails...?
Well then you wasted an id number, but who cares. If you are using an int in MS-SQL server, you have 2 billion of them, wasting one every now & then probably isn't a big deal.
Also, if you are using MS-SQL then you could have the table setup with a identity, which will handle all that stuff for you. Of course Oracle doesn't have identities, so if you are using Oracle, then you are on the right course.
I personally perfer to have all the id counters in one table with two columns. Column one is the tables name, and is the primary key for the table. Column 2 is the next counter value for the table listed in column 1. If you do this, then you want to add a ROWLOCK suggestion to the update statement above, as well as having a parameter that is passed in to give the table name you are interested in.
I came accross this and saw that you have a lot of experience in EDM. I am going to write an application using VB 6.0 to query and bring back selected documents from our FileNet system. The user will review,select and mark the document for deletion. I was hoping you can help me establish the connection to FileNet from VB 6 and let me know what components and references should I be including in my program to get my project going.