Help required for implementing the rowlevel locking trough ADO Connection Object
-
Dear All, I need the help for implementing the row level locking in my transaction-based form. Following is the details of the working logic and the problem faced by me : - Front End: MS Visual Basic 6.0 Back End: MS SQL Server 2000. In one of my form first the user makes the document and saves it. While saving the document numbers is generated and then the details are saved in the detail table and one entry will be passed in the header table. After which the user needs to do the authorization of the same. In the authorization form when the user selects the particular document for authorization then in the lost focus of the document number text box I want to lock the record for that document in the header table. The table contains precisely one record for each document. I first BEGIN the transaction for the connection object I’m using and then I set the LOCK_TIMEOUT property to 0, by executing the statement 'SET LOCK_TIMEOUT 0' through the connection object. Then I execute the following statement: - SELECT * from HEADERTBL WITH (UPDLOCK) where GRNNO='001' But when I go back to SQL Server and execute the SP_LOCK procedure that gives me the status of locks hold by SQL Server. It shows that the record that I want is locked by Update lock but the other records are also locked by SHARED Lock, which I don’t want. After going through the number of records locks it was found that it locks the whole page by default with SHARED Lock and the record matching the where condition with the lock specified. The problem is that if some other user when tries to lock some other document for UPDATE Lock then SQL Server refuses that until the rows locked previously are released. Is anybody can help to set some property through ADO or directly so the records matching the where condition are only locked and other users are also allowed to lock other records then the one already locked. Also the locked records need to be allowed to read by all the users as they take the help for document number and due to which I was unable to use EXCLUSIVE Locks. Revert back in case of any further details are required. Chintan
-
Dear All, I need the help for implementing the row level locking in my transaction-based form. Following is the details of the working logic and the problem faced by me : - Front End: MS Visual Basic 6.0 Back End: MS SQL Server 2000. In one of my form first the user makes the document and saves it. While saving the document numbers is generated and then the details are saved in the detail table and one entry will be passed in the header table. After which the user needs to do the authorization of the same. In the authorization form when the user selects the particular document for authorization then in the lost focus of the document number text box I want to lock the record for that document in the header table. The table contains precisely one record for each document. I first BEGIN the transaction for the connection object I’m using and then I set the LOCK_TIMEOUT property to 0, by executing the statement 'SET LOCK_TIMEOUT 0' through the connection object. Then I execute the following statement: - SELECT * from HEADERTBL WITH (UPDLOCK) where GRNNO='001' But when I go back to SQL Server and execute the SP_LOCK procedure that gives me the status of locks hold by SQL Server. It shows that the record that I want is locked by Update lock but the other records are also locked by SHARED Lock, which I don’t want. After going through the number of records locks it was found that it locks the whole page by default with SHARED Lock and the record matching the where condition with the lock specified. The problem is that if some other user when tries to lock some other document for UPDATE Lock then SQL Server refuses that until the rows locked previously are released. Is anybody can help to set some property through ADO or directly so the records matching the where condition are only locked and other users are also allowed to lock other records then the one already locked. Also the locked records need to be allowed to read by all the users as they take the help for document number and due to which I was unable to use EXCLUSIVE Locks. Revert back in case of any further details are required. Chintan
You are attempting to use database level concurrency for an application concurrency problem, and it is a poor fit. Rather create your own "locking" table, and use that to mark documents as locked.
-
You are attempting to use database level concurrency for an application concurrency problem, and it is a poor fit. Rather create your own "locking" table, and use that to mark documents as locked.