1

I have been facing this problem for long time.

I have two BUTTONS on my form. btnNEXT,btnSUBMIT ..

when user clicks on btnNEXT,details of the next record are displayed. Then user enters some data and clicks on btnSUBMIT. This action will update the details of that particular record.

Now, I have around 10 users working on it. when user1 clicks on btnNEXT, he ll get a record to modify. Now i want that record to be locked and no other user can see that record. when User1 enters details and clicks on btnSUMBIT, the record ll be updated and lock will be released.

Another Scenario: User1 clicks on btnNEXT. then the record ll be locked. If the user closed the application without updating any data, the record should be unlocked.

What I have done: Begin tran Select top 1 * from table with (updlock,readpast) where condition

Update table set a=1,b=2 where id=123 commit tran

above queries satisfy my conditions for locking and unlocking the rows. But i want to Begin the transaction in btnNEXT_Click event and Commit transaction in btnSUMBIT_Click event

How can i achieve this?.. I am unable to think beyond this. Please advice me if you have any alternative that can satisfy my whole scenario

Thanks a lot

ARB
  • 285
  • 1
  • 6
  • 17
  • can you use optimistic locking ? see : http://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking . If you still really want to use pessimistic locking, you could use something like a dedicated "locking" table, but then you need some way to be able to unlock items when "transactions" are not completed. – Moe Sisko Apr 27 '11 at 06:34
  • Holding locks in SQL server for an indeterminate time is a recipe for disaster ... As @moe says a locking table may be an ok solution – Sam Saffron Apr 27 '11 at 12:59

1 Answers1

0

This is the problem inherent in a stateless application. If a user abandons the session, by just walking off or simply closing the browser, there is no good way for you to know for sure that the session should be closed. The best solution that I have come up with is to use a timestamp as the locking field then regularly poll for records that have been locked for "too long". Not a perfect solution but it should address 90%+ of your issues.

edit after comment from OP:

@ARB, Transactions are used to execute a sequence of SQL statements that may potentially need to be rolled back. It is typically reserved for save actions (inserts, updates & deletes). You can not "roll back" a select statement (nothing to 'undo'). So wrapping your btnNext action (a select) and your btnSubmit action in a transaction is not needed. Additionally when I have used transactions it has been in the form of a single sequence of commands. I can't say I know you can't join a transaction in the middle, but I have never seen it done. Depending on the complexity of your save function following btnSubmit may be a good place to use a transaction, but then only if you are saving to multiple tables.

In summary:

Because of the stateless nature of web apps, and the inability to 'force' a user to close their session 'gracefully' you need a mechanism that 'unlocks' a record that has been locked for 'too long'.

Because there is nothing to 'roll back' in your btnNext action (a select command) there is no reason to include this in a transaction. If you wish to isolate your btnSubmit (save action) then that may be useful.

Cos Callis
  • 5,051
  • 3
  • 30
  • 57
  • Hi cos, thanks for the reply. But how can i begin a transaction in btnNEXT_Click event and commit in btnSUBMIT_Click event.. pls give me some sample code if u have – ARB Apr 27 '11 at 06:19