Transacciones en SQL Server

Llevaba tiempo pensando en hacer un breve post relacionado con transacciones de SQL Server y justamente hoy en el trabajo he tenido que usarlas así que me he decidido a escribirlo.

Las transacciones de bases de datos son operaciones muy importantes en las aplicaciones informáticas y especialmente en las aplicaciones web que a menudo se ejecutan de forma asíncrona. Es un concepto muy relacionado con la programación concurrente y el sentido de atomicidad, es decir cuando yo ejecuto una transacción, compuesta por varias operaciones, necesito que se ejecute entera y en caso de que una de las operaciones falle, que se cancele.

Un ejemplo clásico y muy visual es el traspaso de dinero de una cuenta bancaria a otra. El traspaso consta de dos operaciones: 1. Decrementar el saldo de la cuenta de origen, 2. Incrementar el saldo de la cuenta de destino.

Imaginemos que al ejecutar estar operaciones la primera se ejecuta correctamente, pero la segunda falla ¡El dinero se ha volatilizado! Y lógicamente esto no es un comportamiento deseable.

El concepto de atomicidad entra aquí en juego, nuestro objetivo es ejecutar todas las instrucciones bien o no ejecutarlas. O como bien dijo el Maestro Yoda: "Hazlo o no lo hagas, pero no lo intentes".

El código sobre el que se basa este post consiste en lineas generales en dos tablas de una base de datos, una con preguntas de una encuesta y otra con las respuestas de la misma. Cada pregunta puede tener una o varias respuestas y cada respuesta corresponde a una pregunta. La necesidad de un transacción viene porque por un lado necesitamos insertar antes la pregunta que las respuestas y por otro que no puede ocurrir que una pregunta se quede sin respuestas. Además solo hay una encuesta activa a la vez, por lo que debemos actualizar las encuestas anteriores.

Tablas:


Survey { id: int, question: string, active: boolean }
SurveyResponse { id int, survey_id int, response: string, votes: int }


Una vez explicada la motivación pasemos a ver un poco de código:

Private Sub InsertSurvey(question as String, responses as List(Of String),
   connectionString as String)
 Dim updateSurvey As String = "UPDATE Survey SET active=0"
 Dim insertSurvey As String = String.Format("INSERT INTO Survey"+
       "(question,active) OUTPUT INSERTED.id "+
       "VALUES ('{0}', 1)", question))
 Dim insertResponse As String = "INSERT INTO SurveyResponses"+
    "(survey_id, response, votes) VALUES ({0},'{1}',0)"
 Dim trans As SqlTransaction
 
 Using con As New SqlConnection(connectionString)
  Try
   con.Open()
   trans = con.BeginTransaction("InsertSurvey")
   Dim cmd As New SqlCommand(updateSurvey, con, trans)
   cmd.ExecuteNonQuery()

   cmd = New SqlCommand(insertSurvey, con, trans)
   Dim id As Integer = cmd.ExecuteScalar()

   For Each resp As String In responses
    cmd = New SqlCommand(String.Format(insertResponse, id, resp),
    con, trans)
    cmd.ExecuteNonQuery()
   Next
   trans.Commit()
   con.Close()
  Catch ex As Exception
   ShowMessage(String.Format("Se produjo una excepción al insertar la 
   encuesta: {0} {1}", ex.Message, ex.StackTrace))
   trans.Rollback()
  End Try
 End Using
End Sub

Vamos a desgranar las partes importantes del código. Por un lado tenemos la petición de transacción, esto se hace con el objeto de conexión, una vez que se ha abierto la conexión a la base de datos y con un nombre que queramos, esto se hace en el código así:

con.BeginTransaction("Nombre arbitrario")

Después ejecutamos varias sentencias SQL, la clave aquí es añadir estas operaciones a la transacción. Esto se hace en el constructor del SqlCommand:

cmd = New SqlCommand(insertSurvey, con, TRANS)

Una vez ejecutado todo correctamente, confirmamos la transacción:

trans.Commit()

Y así de sencillo. Ya hemos ejecutado, de forma atómica, todas las operaciones y hemos confirmado la transacción. En caso de que ocurriese algún error, en el bloque catch, cancelaríamos la transacción:

trans.Rollback()

Finalmente me gustaría señalar una forma interesante de obtener el Id de la encuesta insertada. Para ello utilizaremos un operador interesante de SQL Server, OUTPUT. Si os fijáis en el insert:

INSERT INTO Survey (question,active) OUTPUT INSERTED.id VALUES ('pregunta', 1)

Esto hace que SQL Server devuelva el campo id de la fila que acabamos de insertar. También nos permitirá sacar cualquier otro campo, pues de hecho id se refiere al campo id de la tabla survey. Y finalmente obtenemos el Id recién creado mediante un ExecuteScalar.

Una alternativa a esto es usar el SCOPE_IDENTITY() de SQL Server destrás del Insert, pero con esta solución nos evitamos hacer una nueva Select, el código queda más compacto.

Con esto terminamos el post de transacciones y otras funciones útiles de bases de datos, espero que os haya resultado útil.