November 12, 2007 at 9:20 am
I am using SQL Server 2005 as a back-end DB, and using a Business Basic language (ProvideX) with the SQL Server ODBC drivers to communicate with the DB. My issue is that I want to be able to place a record lock on a particular record when I am going to update the record. The way the BB language is set up, it builds the SELECT statement and sends it to SQL Server for execution. I am not privy to the source code for all of this. There are some options that are set, one of them being AUTOCOMMIT, which instructs the drivers to auto commit any SQL commands. This means that when I have a SELECT statement that places an WITH (UPDLOCK, ROWLOCK) lock on a record, the lock is released as soon as the SELECT statement is executed. ON the other hand, if I set AUTOCOMMIT=OFF, the locks are never released, as the BB language does not implicitly commit transactions on its own. Are there any ways around this? Will this require a change to the BB language, or is there a combination of options that can get this done? I am not a SQL Server guru by any stretch, nor do I have access to how ProvideX is uusingthe ODBC driver. Any help would be appreciated though!
Thanks
November 12, 2007 at 9:46 am
I am unfamiliar with ProvideX, but it sounds like you are able to run a SELECT statement, look at the data, and then run an UPDATE statement when done. Can you include a BEGIN TRANSACTION statement before the first statement and a COMMIT TRANSACTION after the second one?
If you sent something like
BEGIN TRAN
SELECT * FROM MyTable (ROWLOCK) WHERE ID = 1
and then sen something like
UPDATE MyTable SET MyCol = 'A' WHERE ID = 1
COMMIT TRAN
If autocommit was off and the connection remained open between the statements, this would allow you to hold an exclusive lock on the record.
I am not sure this is a great idea - especially if you have to wait for a user to click a button between these statements (lock a record and then go to lunch always comes to mind), but it may do what you are asking.
If you need to regularly manage locking to this level of detail and your ProvideX product does not handle this better than you have described, you should find a product more suited to your needs.
November 12, 2007 at 10:00 am
Michael Earl (11/12/2007)
I am unfamiliar with ProvideX, but it sounds like you are able to run a SELECT statement, look at the data, and then run an UPDATE statement when done. Can you include a BEGIN TRANSACTION statement before the first statement and a COMMIT TRANSACTION after the second one?If you sent something like
BEGIN TRAN
SELECT * FROM MyTable (ROWLOCK) WHERE ID = 1
and then sen something like
UPDATE MyTable SET MyCol = 'A' WHERE ID = 1
COMMIT TRAN
If autocommit was off and the connection remained open between the statements, this would allow you to hold an exclusive lock on the record.
I am not sure this is a great idea - especially if you have to wait for a user to click a button between these statements (lock a record and then go to lunch always comes to mind), but it may do what you are asking.
If you need to regularly manage locking to this level of detail and your ProvideX product does not handle this better than you have described, you should find a product more suited to your needs.
Thanks, but unfortunately, the product I work on is well established (over 20 years old) and will not be moving to another platform anywhere in the near future. Currently, the app iuses the proprietary data files which is mush faster, but we wanted the ability to use SQL Server OR the ProvideX files in some cases. This had to be done in a transaprent way to the source code so we wouldn't have to rewrite the entire product. ProvideX does some things internally in their code to make it transparent. It all works great except for the pessimistic locking we have to do. Because of the transparent nature, I am unable to use the "begin, commit" transaction method in my code.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply