Advice for methods handling T-SQL transactions (ADO)?

We are talking Isolation Level here, namely concurrency in relation to an Express MSSQL server running locally.

I'm trying to figure out a way to start the data-lockout when a user decides to edit a row in a GridView -> keep the data locked until he/she's stopped editing (I.E. pressed 'Update').

As a list of events it looks like this:

1. User 1 presses Edit on row with ID of 1.
2. DataSet(row) is locked while User 1 is typing in new data.
3. In case of a User 2 trying to Edit same row, that 开发者_运维技巧user should be noticed before even entering edit-mode on that row.
4. User 1 updates the row, and edit-mode stops, therefor the data should be committed and the transaction closed.

I've started to make a few methods.

Prior to the methods, my service method always holds the connection in a private/local variable:

private SqlConnection connection = new SqlConnection("Data Source=host\\SQLEXPRESS;Initial Catalog=tablename;User ID=sa;Password=password");

Then there's the methods.

public SqlTransaction BeginTransaction() {
    //Something like this
    SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
    return transaction;
}

public bool UpdateTidsregistrering(some parameters to be parsed from GUI) {
    using(connection) {
        connection.Open();
        SqlCommand command = connection.CreateCommand();
        //Execute a query here with a try-catch around like normally...
    }
}

public void CloseTransaction() {
    //Close transaction and connection?
}

My big question is: is this possible to utilize properly?

Scenarios to be considerate of...
- Someone starts editting a row, then goes home for the evening
- Many different users holding open transactions of many different rows
- Network connection drops while user is editting
- User A refreshes screen, User B modifies and commits a row, User A starts modifying the row, oblivious to User B's change
- Cost of holding multiple open transactions on many rows
- Cost of all users having to have permantently open connections

In general, my preference is to Only use TRANSACTIONS around code without any pauses. Especially pauses created any human interaction.

My preferrred model for this scenario is to use a 'last-modified' time-stamp.

1. Refresh data on screen, holding a last_modified_time for each row
2. User clicks on a row
3. App checks if that row's last_modified_time has changed
4. If changed, update the row from the DB and inform the user of the current state
5. Allow the user to modify the row on screen
6. User clicks "commit"
7. Another check to see if the last_modified_time has changed
8. If so, show different values and ask user if they still want to commit Their change

In SQL, what you want is:

begin transaction;

select id
from tablename with (updlock, rowlock, holdlock, readpast)
where id = @id;

if @@rowcount = 0
begin
  raiseerror('This record does not exists or is currently locked.',16,1);
  rollback tran;
end;

The user makes edits here...

update tablename ... ;
commit transaction;

You can easily wrap that with c# methods to LockRecord/UnlockRecord.

Note you will need a unique index on id, or bad things will happen.

Don't hold transaction for this. It will require you to:

• Open connection
• Start transaction
• Select record for editation (= lock record)
• Wait for user to complete editation
• Commit changes
• Close connection

How long can user edit the record? What happens if user lock records and goes for a lunch? connection should not be held by the user and a transaction should be as short as possible. The whole separate part is that we are talking about ASP.NET where you will have to store connection in session and if user doesn't commit changes the connection will leak - you will run out of connection pretty soon.

This whole can be considered as utterly bad design and something you should never do!

Add new column to your table: LockedBy (you can also add LockedAt). Only change these columns in transaction when user wants to edit record. Other users will not be able to switch record to edit mode when these columns are filled. Once user commits changes clear these columns.

You will have one problem - you must unlock records if user cancel changes but what if user simply closes his browser? That will need some job which will regularly run and unlock records locked for a long time (the reason for second column). You can run such job in SQL Agent.