none
pessimistic concurrency with dataset RRS feed

  • Question

  • Hello

    i've to do the porting of an old vb 6 application to an asp .net application. The old application read some data from an Oracle db , then locked some tables on an access db (adlockpessimistic) and imported some records. Now i need to replicate this, and i've found the following articles about pessimistic locking with datasets:
    http://articles.techrepublic.com.com/5100-10878_11-1049842.html
    http://www.dotnetspider.com/resources/18138-Locking-Table-SQL-SERVER-Using-VB-NET.aspx


    Essentially it create a new connection , generate a transaction object, assign it to the select/insert/update/delete commands of the adapter and do its operations

    yet i'm not convinced it works and /or has worst drowbacks , is this mechanism correct or my only chance to use a pessimistic lock is to use a "connected" approach? Someone knows if there are drowbacks in this approach?

    obviously i MUST use pessimistic concurrency (the boss says)

    thanks in advance

    Stefano

    Friday, June 19, 2009 10:08 AM

All replies

  • I don't believe that there exists a pessimistic concurrency method using a connected approach, as was possible in Classic ADO. That is why you need to implement transactions instead. With some database systems (like SQL Server) you can lock a row so that it cannot be updated, but that is the only other option that I would see as available.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, June 19, 2009 1:28 PM
  • Sorry i haven't understood your answer. Do you mean i need to use the sqltransaction class setting the appropriate isolation level to have a lock on a record or do you mean that i must manage concurrnecy directly on sql server?

    In the first case my question was related to the following code:
    Dim txn As SqlTransaction
    
    Private Sub cmdFill_Click(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles cmdFill.Click
       SqlConnection1.Open()
       txn = SqlConnection1.BeginTransaction(IsolationLevel.RepeatableRead)
       SqlDataAdapter1.SelectCommand.Transaction = txn
       SqlDataAdapter1.Fill(DsCustomers1)
    End Sub
    
    Private Sub cmdUpdate_Click(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles cmdUpdate.Click
       SqlDataAdapter1.InsertCommand.Transaction = txn
       SqlDataAdapter1.UpdateCommand.Transaction = txn
       SqlDataAdapter1.DeleteCommand.Transaction = txn
       SqlDataAdapter1.Update(DsCustomers1)
       txn.Commit()
       SqlConnection1.Close()
    End Sub
    is it trustable to manage users concurrency ? otherwise the following will guarantee me the record doesn't get read or update (nor read neither updated since it's an global identifier for some kind of items) while another transaction is running?:

    Private Sub ExecuteSqlTransaction(ByVal connectionString As String)
        Using connection As New SqlConnection(connectionString)
            connection.Open()
    
            Dim command As SqlCommand = connection.CreateCommand()
            Dim transaction As SqlTransaction
    
            ' Start a local transaction
            transaction = connection.BeginTransaction(IsolationLevel.RepeatableRead)
            ' Must assign both transaction object and connection
            ' to Command object for a pending local transaction
            command.Connection = connection
            command.Transaction = transaction
    
            Try
                command.CommandText = _
                  "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"
                command.ExecuteNonQuery()
                command.CommandText = _
                  "Insert into Region (RegionID, RegionDescription) VALUES (101, 'Description')"
                command.ExecuteNonQuery()
                transaction.Commit()
                Console.WriteLine("Both records are written to database.")
            Catch e As Exception
                Try
                    transaction.Rollback()
                Catch ex As SqlException
                    If Not transaction.Connection Is Nothing Then
                        Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
                          " was encountered while attempting to roll back the transaction.")
                    End If
                End Try
    
                Console.WriteLine("An exception of type " & e.GetType().ToString() & _
                  "was encountered while inserting the data.")
                Console.WriteLine("Neither record was written to database.")
            End Try
        End Using
    End Sub
    
    
    Do linq to sql provides any mechanism to do such a thing?

    thanks for your answer
    regards

    Stefano



    Friday, June 19, 2009 2:51 PM
  • No you don't have to implement the locking on the server side. That's just one of the options.

    What I meant to say is that there is no built-in pessimistic locking methods like there was in Classic ADO since an ADO.NET Dataset is essentially disconnected.
    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, June 19, 2009 6:49 PM