VB.NET - Public Shared Function

Question

I have a function in a VB.NET system that I use to execute queries in BD. The function is Public Shared type for simplicity. And there is doubt whether competing accesses to the function could generate problems.

This is user 'A' calls the function with a query and before the query is executed, user 'B' calls the same function with a different query. So this concurrent access can generate unexpected results?

Below the code:

Public Class ExecuteDB
    Public Shared Function Execute(ByVal query As String) As DataSet
        Dim ds As New DataSet
        If query Is Nothing OrElse query = "" Then
            Return ds
        End If

        Dim connectionString As String = ConfigurationManager.ConnectionStrings("CnString").ConnectionString

        Using connection As New SqlConnection(connectionString)
            Dim command As New SqlCommand(query, connection)
            Try
                Dim da As New SqlDataAdapter(command)
                da.Fill(ds)
            Catch ex As Exception
                Throw ex
            End Try
        End Using

        Return ds
    End Function
End Class

Answer 1

Maybe I didn’t quite understand the question, in my understanding you’re confusing the modified access Public Shared with BD competition. If so, they have no relationship.

With respect to concurrent access the execution of query this yes can generate unexpected results, see the classic example of bank deposit.

Imagine that an account cannot be negative and the following operation is executed:

    var umaConta = new ContaConjunta();
    //UsuarioA R$1000,00
    umaConta.depositar(1000.0, "UsuarioA"); //Local A
    umaConta.getSaldo();
    umaConta.sacar(1000,"UsuarioA");

   // Usuario B Saque R$300,00 desta conta  //Local B
    umaConta.getSaldo();
    umaConta.sacar(300,00,"UsuarioB");

Without a competition control, a delay in the withdrawal operation of the UsuarioA may cause the balance reading operation of the UsuarioB is incorrect. That is, as long as the UsuarioA processes the withdrawal of UsuarioB read the withdrawal and got the balance R$1000,00 instead of R$700,00.

For a better understanding of competition control, I recommend reading the ACID concept.

ACID PROPERTIES

• Atomicity: The execution of a transaction must be atomic, or all actions are executed, or none is;
• Consistency: Each transaction executed alone must preserve database consistency;
• Isolation: Each transaction shall be isolated from the effects of competing execution of other transactions;
• Durability: Any transaction that is successfully completed must persist in bank results even in the presence of system failures.

SOURCE: http://www.diegomacedo.com.br/controle-de-concorrencia-em-banco-de-dados