Best way to ensure query isolation

Question

Best way to ensure query isolation

Navigation

#1 by (0 votes)

0

I have the following scenario:

A table in SQL Server that contains records of processes that can be run across multiple services ( Windows Services in C # ). Something like:

CREATE TABLE Processo
(
    Id          INT IDENTITY NOT NULL,
    Status      VARCHAR(100)
)

GO

INSERT INTO Processo VALUES 
  ('Aguardando processamento'), ('Aguardando processamento'), ('Aguardando processamento')

SELECT  *
FROM    Processo 

/*
| Id  | Status                   |
| 1   | Aguardando processamento | 
| 2   | Aguardando processamento | 
| 3   | Aguardando processamento | 
*/

Services query this table simultaneously for a process with status "Waiting for processing". Once one of these services finds the process, the status is updated to "Processing" in a method something like this:

using (SqlConnection conn = new SqlConnection("string de conexão"))
{
    conn.Open();

    using (SqlCommand command = conn.CreateCommand())
    {
        command.CommandType = System.Data.CommandType.Text;
        command.CommandText = "SELECT Top 1 Id FROM Processo WHERE STATUS = 'Aguardando processamento'";
        int processo = (int)command.ExecuteScalar();

        command.CommandText = "UPDATE Processo SET STATUS = 'Processando' WHERE Id = @Id";
        command.Parameters.Add(new SqlParameter("Id", processo));
        command.ExecuteNonQuery();
    }                
}

The problem occurs when, between the query and the status change for "Processing" in the "A" service, a "B" service encounters the same record and also starts processing.

I need to ensure that when a service encounters a process, no one else can find or change this same registry.

What is the best way to ensure this?

EDIT: The purpose of my question is to solve the problem considering .NET applications on multiple servers, the one suggested as "possible duplicate" (#

sql-server c# ado.net windows-service

asked by anonymous 18.04.2018 / 19:00

1 answer

Relationship error between tables when implementing audit with Hibernate Envers capture servlet value in jsp

score 0 · Accepted Answer

You can execute both commands within a transaction using SqlTransaction :

using (SqlTransaction trans = conn.BeginTransaction())
{
    using (SqlCommand command = conn.CreateCommand())
    {
        command.CommandType = System.Data.CommandType.Text;
        command.CommandText = "SELECT Top 1 Id FROM Processo WHERE STATUS = 'Aguardando processamento'";
        int processo = (int)command.ExecuteScalar();

        command.CommandText = "UPDATE Processo SET STATUS = 'Processando' WHERE Id = @Id";
        command.Parameters.Add(new SqlParameter("Id", processo));
        command.ExecuteNonQuery();
    }
    trans.Commit(); 
}

MSDN Reference: SqlTransaction