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 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply