Programando a medianoche

Introducción, conceptos y definiciones básicas

En SQL Server un cursor puede definirse como un elemento que representará a un conjunto de datos determinado por una consulta T-SQL, el cursor permitirá recorrer fila a fila, leer y eventualmente modificar dicho conjunto de resultados.

SQL Server ofrecerá una amplia variedad de opciones y de funciones para crear y operar sobre cursores. En este articulo, donde hemos utilizado SQL Server 2008 Enterprise Edition en los ejemplos, daremos un paseo por las posibilidades disponibles.

La creación y utilización de un cursor estará compuesta, como es de esperarse, por una serie de instrucciones T-SQL, las cuales podrán separarse en grupos bien diferenciados, los cuales son: Declaración, Apertura, Acceso a datos, Cierre y Desalojo, a continuación detallaremos cada grupo de instrucciones.

Declaración

El primer paso constará de la declaración del cursor, donde se indicarán (junto con el nombre del cursor) la consulta que el mismo representará y algunas otras características bastante interesantes.

Un ejemplo de declaración de cursor es el siguiente:

DECLARE ProdInfo CURSOR FOR SELECT Name FROM Production.Product

Donde ProdInfo representará al nombre del cursor y la sentencia “SELECT ProductNumber,Name FROM Production.Product” será el conjunto de datos del mismo.

Como comentamos previamente, es posible en este paso definir algunas características del comportamiento del cursor, por ejemplo la sentencia:

DECLARE ProdInfo CURSOR READ_ONLY FOR SELECT Name FROM Production.Product

Indicará que el cursor será de solo lectura, más adelante veremos en detalle las opciones disponibles, por el momento nuestro objetivo es crear un cursor lo más simple posible.

Apertura

La apertura del cursor ejecutará la consulta definida en el paso previo y cargará los datos en el mismo. La función OPEN de T-SQL permitirá efectuar esta terea, para continuar con el ejemplo previo la forma de abrir el cursor será la siguiente:

OPEN   ProdInfo

Recorrido del cursor y acceso a los datos

Este paso constará de recorrer los resultados del cursor, la instrucción FETCH permitirá efectuar dicha operación. Las filas leídas podrán copiarse a variables utilizando la sentencia INTO en combinación con la sentencia FETCH, por ejemplo la sentencia:

FETCH NEXT FROM ProdInfo INTO @Description

Tomará la siguiente fila de resultados del cursor y lo alojará en la variable @Description.

Un detalle a comentar es que en la sentencia INTO (como puede verse en el ejemplo anterior) el mapeo entre columnas del cursor y variables se realizará implícitamente, asignándose la primera columna a la primera variable, la segunda columna a la segunda variable y así sucesivamente. Esto implica que deberán crearse tantas variables como columnas se definan en la declaración del cursor y las mismas deberán ubicarse en el mismo orden que se encuentran definidas las columnas en la sentencia SELECT de la declaración.

Como cada sentencia FETCH leerá un registro, una pregunta interesante que podríamos hacernos es, ¿de qué manera podremos saber si existe un próximo o previo registro, o si hemos llegado al límite (ya sea superior o inferior)?. La respuesta se encontrará en una variable de SQL Server llamada @@FETCH_STATUS que tomará el valor 0 si la lectura del registro ha sido correcta.

En este punto será también posible modificar o eliminar las filas que se van recorriendo, como veremos al final del artículo.

Cierre del cursor

En el cierre del cursor se liberarán los registros tomados por el mismo. Una vez que el cursor es cerrado ya no podrá recorrerse el conjunto de resultados hasta que el mismo sea reabierto, la sentencia CLOSE cerrará un cursor abierto y la sintaxis puede verse a continuación:

CLOSE  ProdInfo

Desalojo del cursor

Este paso eliminará la referencia al cursor definido previamente, por lo que ya no será posible realizar una reapertura del mismo, en este caso la sintaxis será:

DEALLOCATE ProdInfo

Ejemplo de cursor

A continuación juntaremos todos los pasos descriptos previamente y crearemos el ejemplo más simple de un cursor el cual recorrerá la tabla Production.ProductDescription de la base de datos AdventureWorks y mostrará por pantalla el valor del registro Description luego que el mismo ha sido copiado a la variable @Description.

DECLARE @Description AS nvarchar(400)
DECLARE ProdInfo CURSOR FOR SELECT [Description] FROM Production.ProductDescription
OPEN ProdInfo
FETCH NEXT FROM ProdInfo INTO @Description
WHILE @@fetch_status = 0
BEGIN
    PRINT @Description
    FETCH NEXT FROM ProdInfo INTO @Description
END
CLOSE ProdInfo
DEALLOCATE ProdInfo

En el ejemplo puede verse la utilización de la variable @@FETCH_STATUS para verificar la existencia de la fila leída, por otra parte la sentencia FETCH NEXT devolverá la próxima fila de datos, siempre que la misma exista.
Este ejemplo, a pesar de parecer tan trivial y claro esconde un amplia variedad de comportamientos que el cursor asumirá, y que en este caso han tomado el valor por defecto, ya que no los hemos definido explícitamente.
El próximo paso será dar una recorrida por estas opciones y entender cuales se adaptarán mejor a nuestras necesidades.

La intención de este artículo es la de comentar una de las nuevas capacidades de SQL Server 2008 que es la posibilidad de crear índices filtrados, pero me parece que es una buena oportunidad para mencionar que son los índices, cual es su objetivo, que tipos de índices existen y dejar para final del artículo este asunto de los índices filtrados.

Comencemos por la idea más básica que es la de preguntarse qué es un índice y para qué sirve, un índice es un mecanismo que permite acceder a un conjunto de datos en forma más eficiente que si no se utilizase dicho mecanismo, considerando a la velocidad de acceso a los datos como el factor de eficiencia que los índices optimizan. En el primer tipo de índice que vamos a comentar la estrategia de optimización consiste en ordenar físicamente los datos de forma que puedan encontrarse más rápidamente, esto significa que en este tipo de índice existirá una o varias columnas que definirán de que manera estará la tabla físicamente ordenada.
Esta idea no es nueva, para quienes hayan programado alguna vez en cualquier lenguaje sabrán que si debemos buscar un valor en un vector ordenado, podremos utilizar algunas técnicas como por ejemplo la búsqueda binaria que permitirán encontrar los datos buscados en orden logarítmico a diferencia del inmejorable orden lineal cuando los valores dentro del vector están desordenados. Para quien nunca haya programado podrá recordar un diccionario, en un diccionario un usuario busca una definición (datos) a partir de una clave (palabra a buscar) y el hecho de que los datos estén ordenados por la clave (o sea las definiciones por las palabras) permitirá que el usuario no tenga que recorrer todas las palabras del diccionario hasta encontrar la palabra deseada. De forma similar dentro de la estructura de tablas del SQL Server el hecho que los datos se encuentren ordenados físicamente por la clave permitirá un acceso más rápido a los mismos. No estará quien se pregunte qué sucederá cuando se inserte un nuevo registro con la performance, y no hay dudas que será menos eficiente que si los datos estuviesen desordenados, pero no hay que olvidar que lo que se desea es eficiencia en las operaciones de búsquedas, que son las que se realizan con mayor frecuencia.

La forma más sencilla de ver la diferencia que puede provocar un índice de este tipo es crear una tabla simple en nuestro motor de base de datos SQL Server y ver el plan de ejecución en ambos casos (con y sin el índice), comencemos creando la tabla y agregando algunos valores:

CREATE TABLE [dbo].[Datos1](
	[ID] [int] NOT NULL,
	[Numero] [int]NOT NULL,
	[Descripcion] [nvarchar](50) NOT NULL,
)

INSERT INTO Datos1 ([ID],[Numero],[Descripcion]) VALUES (1,1,'D1')
INSERT INTO Datos1 ([ID],[Numero],[Descripcion]) VALUES (2,2,'D2')
INSERT INTO Datos1 ([ID],[Numero],[Descripcion]) VALUES (3,3,'D3')
INSERT INTO Datos1 ([ID],[Numero],[Descripcion]) VALUES (4,4,'D4')

Luego iniciaremos una búsqueda y veremos el plan de ejecución. El plan de ejecución mostrará de qué manera el query optimizer intentará acceder a los datos durante una consulta, (El query optimizer es el encargado de diseñar la estrategia del acceso a los datos).
Existen varias maneras de ver el plan de ejecución, utilizaremos en estos ejemplos la forma grafica.
Luego de haber ejecutado el script previo deberemos escribir lo siguiente en un query analizer:

SELECT [ID], [Numero], [Descripcion] FROM Datos1 WHERE ID=1

Y luego presionar CTRL+L. Se obtendrá un resultado similar a lo siguiente:

Los planes de ejecución en formato gráfico deben leerse de izquierda a derecha y de arriba hacia abajo, y aunque pueden ser extremadamente largos y complejos de leer, en nuestro caso podemos ver el mismo está compuesto por solamente dos iconos y una flecha que los une a ambos. Cada icono representará una operación y la flecha simbolizará el movimiento de datos entre las dos operaciones, indicándonos que la operación “Table Scan” ha tomado los datos que la operación SELECT procesará, en realidad la operación SELECT no ha hecho nada en este caso. Este diagrama nos indica que está haciendo internamente el motor de base de datos.

Una operación “Table Scan” nos está indicando que el motor ha necesitado recorrer secuencialmente la tabla Datos1 para poder encontrar los registros que cumplan con la condición pedida.
La operación “Table Scan” es equivalente a tener un diccionario desordenado donde es necesario recorrerlo secuencialmente hasta encontrar la palabra que deseamos buscar, pero además la palabra puede existir más de una vez, así que siempre deberemos recorrerlo hasta la última palabra para asegurarnos que hemos encontrado todas las definiciones. Cuando no hay índices creados la performance de las búsquedas quedan gravemente comprometidas.
En contraposición crearemos un índice y veremos que cambios se producen en el plan de ejecución, ejecutaremos la siguiente línea de código:

 CREATE CLUSTERED INDEX IX_1  ON [dbo].[Datos1] (ID)

Donde hemos indicado la creación de un índice por la columna “ID”,(la palabra CLUSTERED indicará que la tabla se ordernenará físicamente por el índice solicitado, luego veremos que existe otro tipo de índices que no impone tal condición.)
Si volvemos a ejecutar la consulta anterior, el plan de ejecución tomará el siguiente formato:

Indicando que en este caso la búsqueda de datos está utilizando el índice IX_1, de manera que el motor ya no debe recorrer toda la tabla para encontrar los registros pedidos.
Podemos ahora preguntarnos que pasaría si además es necesario realizar búsquedas por otro campo, supongamos por el campo “Numero”, en este caso no podremos reordenar la tabla físicamente por “Numero”, ya que al hacer esto perderíamos el orden físico que ya habíamos establecido por el campo “ID”, es claro que el orden físico puede establecerse solo para una clave (ya sea compuesta por un solo o varios campos). Para estos casos existen otro tipo de índices conocidos como índices non-clustered, ya que no modifican el orden físico de los registros en la tabla original, estos índices guardarán en otra estructura una copia de los valores involucrados en la clave y un puntero al registro original de la tabla. Para probar lo antes comentado ejecutaremos el siguiente comando:

CREATE INDEX IX_2  ON [dbo].[Datos1] (Numero)

Y luego veremos el plan de la siguiente búsqueda:

Donde puede verse que el query optimizer ha decidido utilizar el nuevo índice IX_2.

Habrá seguramente quien se haya percatado que en este último query solo estamos incluyendo a la columna “Numero” y se pregunte el por qué de esta decisión?, y más aun, habrá quien pareciéndole extraño realizará la misma búsqueda pero esta vez con todos los campos (al menos eso espero). Si es así, quien realice esta prueba descubrirá algo pertubador, y es que el query optimizer habrá decidió utilizar el índice IX_1, y no IX_2, pero por que? podrán preguntarse y la respuesta es la siguiente:
Como comentamos previamente los índices non-clustered guardan una copia de las claves y un puntero al registro original, de esta manera cuando hemos buscado solamente por “Numero” el índice IX_2 es capaz de devolver la información solicitada ya que posee el valor de la columna “Numero”, pero cuando hemos pedido otros datos como “ID” y “Descripcion” que no existen en IX_2 el query optimizer ha decidido que es menos costoso recorrer la tabla por IX_1 para devolver los datos que IX_2 no posee. Cuando un índice non-clustered cubre todos los datos solicitados en la consulta se dice que es un covered-index, el caso contrario no será un covered-index y el query optimizer deberá buscar alguna estrategia para obtener los datos faltantes, obviamente los clustered index son siempre covered index, ya que poseen el registro completo.
El query optimizer puede utilizar otras estrategias para obtener los datos faltantes como veremos a continuación. Si ejecutamos el siguiente código:

DELETE FROM Datos1
DECLARE @C int =1
WHILE @C < 10000
BEGIN
	INSERT INTO Datos1 ([ID],[Numero],[Descripcion])
	VALUES (@C,@C + 1,'D1' + cast(@C as nvarchar(10)))
	SET @C+=1
END

Donde solamente hemos agregado más datos y volvemos a ejecutar la consulta anterior veremos lo siguiente:

Ahora el query optimizer ha utilizado nuestro índice IX_2 pero para recuperar los datos faltantes a requerido efectuar una operación de Key Lookup extra utilizando el índice IX_1, para finalmente unir los datos en la operación Nested Loops. Si creamos un nuevo índice que cubra todos los datos pedidos de la siguiente forma:

CREATE INDEX IX_3  ON [dbo].[Datos1] (Numero,ID,Descripcion)

No debería sorprendernos el siguiente resultado:

Otra opción para incluir las columnas restantes es utilizar la sentencia INCLUDE de la siguiente forma:

CREATE INDEX IX_3 ON [dbo].[Datos1] (Numero)  INCLUDE (Descripcion, ID)

En el segundo caso, las columnas son agregadas al índice pero no forman parte del mismo.

En ambos tipos de índices, clustered o non-clustered existe la posibilidad de definirlos como únicos (unique), un índice único no admite repetición de valores, y permite una mayor optimización en las búsquedas. Las claves primarias de las tablas están compuestas por índices “unique” que pueden ser o no clustered.

En Sql Server 2008 existe además la posibilidad de crear índices filtrados, o sea índices que se aplican solo a un grupo de datos. Para probarlo podemos eliminar los índices IX_2 e IX_3 y crear un nuevo índice IX_4 filtrado, las siguientes líneas de código efectuan estas operaciones:

DROP INDEX IX_2 ON [dbo].[Datos1]
DROP INDEX IX_3 ON [dbo].[Datos1]

CREATE INDEX IX_4 ON [dbo].[Datos1] (Numero,ID,Descripcion) WHERE Numero < 100

De esta forma el índice IX_4 será aplicable para algunas condiciones solamente, por ejemplo si ejecutamos el siguiente query:

El query optimizer ha decidido emplear IX_4 mientras que en el caso de:

Ha optado por IX_1.

Por último me queda por comentar que existen además de los índices que hemos mencionado (que son los que se utilizan en la mayoría de los casos) los índices full text, los índices XML y los índices espaciales, los cuales espero podamos ver en algún próximo articulo.

Espero como es la costumbre que este articulo haya sido de utilidad y nos vemos en el próximo.

En más de una ocasión nos fue necesario importar datos en formato CSV a una tabla en nuestro servidor de base de datos SQL Server, después de esta última vez me pareció una buena idea hacer un muy breve comentario de una de las posibilidades disponibles, así es que presento al comando BULK INSERT, el cual permite llevar a cabo esta operación.
Antes que nada debemos suponer que poseemos la siguiente tabla:

El siguiente archivo CSV:

1,Cabecera1,20001211,20001211 22:10:30,41422.66, 22.22
2,Cabecera2,20001212,20001212 22:10:32,1234567.123456, 22.4444

Y la clara intención de copiar la información desde el archivo CSV hacia la tabla en cuestión. Deberemos entonces utilizar el siguiente comando:

BULK INSERT
	Cabecera
FROM
	'C:\....\archivo.csv'
WITH (
    FIELDTERMINATOR= ',',
    ROWTERMINATOR = '\n'
);

Donde hemos especificado origen (el path al archivo CSV) destino (la tabla Cabecera) y los separadores de filas y columnas en los argumentos ROWTERMINATOR y FIELDTERMINATOR.

El resultado de la operación generará el siguiente resultado:

Donde puede verse que los datos han sido agregados aunque algunos de ellos (IngresoAnual y Numero) han quedado truncados en la tabla Cabecera al no poder ser representados en forma completa en los tipos de datos destino. Si se produce algún error de conversión de datos durante la importación la fila será omitida, el mismo comportamiento utilizará el comando si se viola la constraint NOT NULL.
En otros tipos de errores, como por ejemplo, violación de la constraint UNIQUE, la importación fallará, cabe mencionar que para el caso particular de las constraints CHECK y FOREIGN KEY, las mismas serán por defecto omitidas, al menos que se utilice el parámetro CHECK_CONSTRAINTS en la sentencia BULK INSERT para especificar lo contrario.
El comando BULK INSERT posee además de los pocos parámetros que hemos visto una diversa cantidad de los mismos, como por ejemplo FIRSTROW y LASTROW que permite especificar la fila inicial y final de copia, FIRE_TRIGGERS que permite indicar si se dispararán los triggers de las tablas durante la importación de datos, KEEPIDENTITY que permite especificar de qué manera se manejarán la columnas identity durante la importación y KEEPNULLS que indica si las columnas vacías deberán conservar el valor NULL.
Dejo para quien este interesado el siguiente link donde podrán obtener más información del comando, de todos los argumentos disponibles (que son muchos más que los que hemos mencionado) y de su uso:

http://technet.microsoft.com/es-es/library/ms188365.aspx

Espero que esta pequeña contribución haya servido para quienes no conocieran la existencia de este comando.

Comencemos por definir que es una transacción dentro del contexto de SQL Server, una transacción es un conjunto de operaciones Transact SQL que se ejecutan como un único bloque, si alguna operación falla, el bloque completo falla, si todas las modificaciones de los datos realizadas en la transacción tienen éxito, la misma se confirma y el conjunto de modificaciones se hacen permanentes en la base de datos.
Existen tres tipos de transacciones, las transacciones por procedimiento almacenado, las transacciones iniciadas por el cliente y las transacciones COM+ (de las cuales no hablaremos en este artículo).
Las transacciones por procedimiento almacenado tienen lugar en el motor de base de datos mientras que las iniciadas por el cliente tienen lugar en el código, para nuestro caso código .NET. En ambas circunstancias se emplea una estructura similar para el manejo de transacciones:

-- INICIO DE TRANSACCION
-- Comandos
-- ..
-- ...
-- ......
-- SI HUBO ALGUN ERROR CANCELACION DE TRANSACCION
-- SI NO HUBO ERRORES, CONFIRMACION DE TRANSACCION

En el motor de base de datos SQLServer 2005 o 2008, este esquema tomará la siguiente forma

BEGIN TRAN
BEGIN TRY
 Comandos
 ..
 ...
 ......
 COMMIT TRAN
END TRY
BEGIN CATCH
 ROLLBACK TRAN
END CATCH

En versiones previas de SQL Server los comandos de capturas de excepciones (BEGIN TRY, END CATCH, etc.) no se encuentran disponibles, pero existe en cambio la variable @@ERROR (también disponible en versiones posteriores) que puede consultarse luego de cada instrucción para saber si el comando se ejecutó exitosamente.
Desde .NET, empleando clases ADO.NET, el esquema sería el siguiente:

SqlConnection objMyConnection = new SqlConnection(strCnString);
SqlCommand objMyCommand = new SqlCommand(".....");
SqlTransaction objMyTransaction = null;
objMyCommand.CommandType = CommandType.Text;
objMyCommand.Connection = objMyConnection;
objMyConnection.Open();
objMyTransaction = objMyConnection.BeginTransaction(IsolationLevel.ReadUncommitted);
objMyCommand.Transaction = objMyTransaction;
try
{
 //comandos através de objMyCommand
 objMyTransaction.Commit();
}
catch
{
 objMyTransaction.Rollback();
}
finally
{
 objMyConnection.close();
}

Ambos esquemas son semánticamente idénticos. Un punto a considerar es que siempre es preferible que las transacciones se manejen desde el propio motor, ya que se evita el overhead adicional de la transferencia de datos, aunque esta posibilidad no es siempre factible.
Lo más interesante de las transacciones es la concurrencia, y una pregunta frecuente, al menos me parece que lo es, es ¿qué pasa con los datos dentro de una transacción cuando hay concurrencia sobre esos datos?, ¿qué sucede si una transacción modifica datos que aún no ha confirmado y otra transacción lee esos mismos datos en ese momento? ¿Existe una forma de aislar las transacciones para que no haya interferencia entre ellas?, ¿ese aislamiento podrá traer otras consecuencias?. Esos son los puntos que intentaremos recorrer en este articulo, contestando inmediatamente a las primeras preguntas que ya hemos formulado, y la respuesta es (como era de suponerse), si, existe una forma de aislar las transacciones, en realidad existe todo un abanico de posibilidades, conocido como “modos de aislamiento” o “isolation modes”.

Quizás la idea más intuitiva es esperar que no exista ningún tipo de interferencia de datos entre las transacciones, o sea, que cada una sea totalmente aislada de las otras, esto sería esperar que los datos modificados, (inserts, updates, deletes) sean vistos luego de la confirmación, por ejemplo si dentro de una transacción se borran mil registros, y otra transacción lee esos registros en el intervalo de tiempo existente luego del borrado, pero antes de la confirmación, la segunda transacción no debería ver esos registros, ya que si los viera la información sería inconsistente.
Para que esto funcione de esta forma, la segunda transacción debería esperar a que la primera termine de ejecutarse (obviamente si es que la segunda va a leer datos que la primera modificó) ya que es la única manera de asegurarse que los datos estuvisen siendo leídos luego del commit.

La idea no es nada nueva y es conocida como Read Commited y aunque funciona parece que el hecho de que las transacciones deban esperarse entre sí cuando hay cambios en los datos puede ocasionar problemas a medida que la concurrencia vaya incrementándose, no es difícil imaginarse que algunas transacciones recibirán un timeout cuando la concurrencia se incremente.

Una forma rápida de probar como funciona este esquema es crear desde el Visual Studio un método que abra una transacción y ejecute algún comando de modificación sobre una tabla (insert, update o delete), teniendo el cuidado de dejar un break en la línea de código del commit, al ejecutarse el código se abrirá una primera transacción de modificación de datos no confirmada en el servidor SQL Server. Puede entonces desde el Microsoft SQL Server Management Studio abrirse una segunda transacción y ejecutarse una sentencia de consulta de datos sobre la misma tabla (claro está que debe leer datos que hayan sido modificados por la primer transacción) . Veremos entonces que nuestra consulta en el Microsoft SQL Server Management Studio se queda en espera hasta que la línea commit en nuestro proyecto de Visual Studio es ejecutada, si no ocurre un timeout antes.

un detalle a considerar es que, si una transacción update no modifica los valores de un registro (ya que los valores originales y los nuevos son iguales), el registro no se bloqueará.

Existe otro esquema de trabajo totalmente opuesto, su nombre es inevitablemente Read Uncommited, en este modo de aislamiento pasa todo lo contrario, las transacciones pueden leer los cambios de otras transacciones aunque las mismas aún no se hayan confirmado, pero estarán leyendo datos no confirmados, en este esquema no se producen los bloqueos que se daban en el caso anterior, si hacemos la misma prueba que en el caso previo, pero abriendo la transacción en el Microsoft SQL Server Management Studio como Read Uncommited (Reemplazar SET TRANSACTION ISOLATION LEVEL READ COMMITTED por SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED) veremos que no hay bloqueos y los datos son devueltos inmediatamente.
Quizás ya no se generen problemas de concurrencia, debido a que no hay bloqueos, pero es de esperarse que algo malo pueda suceder, y la frase anterior “pero estará leyendo datos no confirmados” es un indicio, las transacciones Read Uncommited leen los datos que aún no han sido confirmados, pero entonces que pasará si esos datos se cancelan con un Rollback, lo que sucede es que la transacción Read Uncommited leerá datos inválidos, este comportamiento de las transacciones Read Uncommited es conocido como Dirty Read literalmente “Lectura Sucia”, Los bloqueos en las transacciones Read Commited evitan justamente este problema.

Por todo lo visto hasta ahora no faltará quien pueda deducir que entonces las transacciones Read Commited son una mejor opción, ya que, si bien pueden bloquearse entre sí, no hay problemas en la lectura de datos. Quizás este modo de aislamiento sea una mejor opción en la mayoría de los casos, y de hecho es el modo de funcionamiento que traen las transacciones por defecto en SQL Server, pero las transacciones Read Commited pueden ocultar una sorpresa, lo cual no suele ser nada grato en ningún caso imaginable, al menos para mí, y las transacciones en SQL Server no son la exepción, solo debemos preguntarnos ¿Qué sucederá si mientras una transacción Read Commited está leyendo datos, una segunda transacción, supongamos también Read Commited modifica datos, digamos que hace updates, deletes e inserts y los confirma?, claro está que si bien nos aseguramos de no leer datos basura gracias a la espera del commit, de todas formas tendremos problemas, ya que algunos de los datos leídos podrían haberse eliminado, podrían también haberse agregado y modificado otros nuevos y la primera transacción ni se enteraría.
A las filas eliminadas, que aunque la primera transacción ve ya no existen, se las conoce como filas fantasma (phantom rows). Un efecto del escenario planteado es que si la primer transacción vuelve a leer los datos (luego que la segunda ya haya confirmado la transacción) aunque la consulta sea idéntica se obtendrán distintos resultados.
Debido a las filas fantasma (eliminadas) y a las filas actualizadas, no es posible garantizar que dos lecturas consecutivas devuelvan el mismo resultado, esto se lo conoce como “lecturas irrepetibles”, claro está que este problema surge porque las transacciones Read Commited le permiten a otras transacciones modificar datos que ellas ya han leído, recordemos que las transacciones Read Commited a lo sumo se bloquean ellas mismas cuando intentan leer datos modificados, pero no bloquean otras transacciones.

Para resolver estos problemas, existen otros modos de aislamientos aún más restrictivos que Read Commited. El modo de aislamiento Repeteable Read (Lecturas repetibles) permite, como su nombre da a imaginar, resolver el problema de las lecturas irrepetibles, ya que no le permite a otras transacciones eliminar o modificar filas leídas por la transacción actual hasta que la misma haya confirmado los cambios, otras transacciones si podrán agregar nuevos datos, se puede entender al Modo Repeteable Read como el modo Read Commited más la restricción a otras transacciones de eliminar o modificar los datos leídos por la primera, en este caso las transacciones Repeteable Read bloquean además otras transacciones para no permitirles modificar datos que ellas han leído.

Finalmente existe un nivel aislamiento aún más restrictivo que los anteriores conocido como Serializable, este nivel de aislamiento se comporta como el modo Repeteable Read, pero además tampoco le permite a otras transacciones agregar datos, en consecuencia cada transacción se ejecuta en serie una tras otra sin ninguna posibilidad de realización de tareas en paralelo.
Hasta el momento es claro que a un mayor nivel de aislamiento le corresponderá un mayor bloqueo de registros, en consecuencia, una menor escalabilidad.

SQL Server 2005, trajo un nuevo modo de aislamiento conocido como Snapshot, la idea de este modo de aislamiento es que una transacción pueda leer datos sin la posibilidad que se produzcan dirty reads, pero sin establecer bloqueos, o sea, una solución con la estabilidad de un Read Commited pero con los bloqueos de un Read Uncommited, para poder utilizar este modo de aislamiento, a diferencia de los anteriores hay que establecer en la base de datos lo siguiente:

ALTER DATABASE ... SET ALLOW_SNAPSHOT_ISOLATION ON

Desde ese momento todas las transacciones establecidas como snapshot emplearán este nivel de aislamiento, si se desea que este modo sea el modo por defecto y ya no ReadCommited, debe ejecutarse el comando:

ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON

El modo de aislamiento Snapshot mantiene las versiones de los cambios en la base de datos tempdb para cada transacción, cuando una transacción toma datos, los mismos poseen asociado un numero de versión, para que la confirmación tenga éxito es requisito que los datos a modificar no posean un número de versión mayor al que posee la transacción. Si una transacción intenta confirmar datos con una versión de cambios anterior a la actual, la operación fallará.

Un último comentario sobre este modo de aislamiento es que emplea un esquema optimista. En un esquema optimista se espera que la concurrencia a los mismos datos desde varias transacciones sea poco frecuente, por eso no es muy problemático devolver un error cuando se dá dicha situación. En cambio en el enfoque pesimista se espera que haya mucha concurrencia a los mismos datos y por tal motivo se emplean mecanismos de bloqueo para poder evitar el problema. En las aplicaciones donde se espera que una gran cantidad de usuarios concurrentes, por ejemplo las aplicaciones web tan en auje actualmente, la idea de bloquear registros parece cada vez menos atrayente, por eso el enfoque optimista parece haber tomado popularidad, En LINQ para SQL, como tecnología de punta en acceso a datos, no solo se considera el esquema optimista, sino que además de emplearlo como modo de concurrencia por defecto, posee ya incorporadas metodologías reajustar los datos en el caso de fallas por acceso a datos concurrentes, quien esté interesado y aún no haya podido ver nada del tema, puede esperar a que escribamos algún artículo o adelantarse a buscar info de esta tecnología, un tip para los segundos : RefreshMode.KeepChanges y espero como siempre, que este artículo haya sido de utilidad.

Hace unos días en un proyecto que estamos desarrollando con tecnología PHP 5.0 y MySql 5.0, viendo la documentación funcional descubrimos que teníamos una gran cantidad de formularios los cuales realizarían tareas de listado, altas, bajas y modificación de datos.
Entonces comenzamos por buscar una forma de automatizar las tareas que se realizarían frecuentemente, comenzamos con los listados, la aplicación contenía listados, donde una característica mínima que esperábamos de los mismos, era la posibilidad de paginación, para crear una experiencia de usuario aceptable y para no comprometer la performance de nuestro servidor de base de datos. De esta primera necesidad surgió la idea de reutilización de código y de encapsulamiento de toda la lógica de paginación. En este articulo comentamos como el uso de clases en PHP nos ayudó a resolver nuestro problema.

La intención que tuvimos fue la de crear una clase de paginación de forma de que en cada página donde se utilizara la clase, solo debiera definirse como se deseaba realizar el paginado y luego se le pidiera al objeto que mostrará el paginador y como es de suponerse, pudiera pedirsele el conjunto de registros correspondiente a la página seleccionada.
Nuestra clase, que llamamos pagedQuery quedo constituida por un constructor y algunos métodos, detallaremos que hace cada uno de ellos a continuación:

El constructor

El constructor toma los parámetros

p_uid
p_pagesize
p_cnn
p_table
p_preservepagenumber (opcional)
p_customSqlSentence (opcional)

Dónde:

p_uid es una cadena de texto con un nombre de identificación, este nombre interviene en los nombres e id’s de elementos HTML y código javascript que renderizará el paginador, por ejemplo si le pasamos un p_uid llamado “Usuarios” el objeto creará un input hidden llamado PgUsuarios y una función javascript llamada GTP_Usuarios para uso interno. De esta forma, si se desean ubicar varios paginadores dentro de una misma pagina PHP, simplemente hay que asignarle distintos p_uid’s y todo funcionará sin que un paginador interfiera con los otros.

p_pagesize indica la cantidad de registros por página que se mostrarán.

p_cnn espera una conexión a MySql para la toma de datos

p_table indica el nombre de la tabla de la cual se tomarán los datos a mostrar

p_preservepagenumber que es opcional y por defecto se encuentra establecido en verdadero, indica si se debe mantener el número de página en variables de sesión, una opción muy útil para mantener la página seleccionada aunque el usuario ingrese en otras páginas, un caso que se dará sin dudas en los listados editables (o sea en listados desde donde el usuario podrá crear, modificar y eliminar registros).

p_customSqlSentence que por defecto se encuentra establecido como un string vacio y es opcional, tiena la función de indicar que sentencia SQL se debe emplear como fuente de datos (por ejemplo si se debe emplear más de una tabla o si se desea ordenar por algún criterio), en tal caso se omitirá el parámetro p_table, una consideración que se debe tomar en cuenta es que en la consulta pasada en p_customSqlSentence el paginador siempre deberá insertar el comando LIMIT de MySql, a fin de paginar lo más eficientemente posible. por defecto el comando LIMIT es insertado al final de la sentencia SELECT pasada por el usuario, pero si por algún motivo se desea especificar dónde el comando LIMIT debe establecerse, deberá escribirse el texto {INSERT LIMIT HERE} dentro de la sentencia que el usuario establece en p_customSqlSentence para que el paginador haga el reemplazo correspondiente.

La tarea del constructor es guardar los parámetros pasados y recuperar el número de página, (o establecerlo si la página se carga por primera vez, en ese caso se establecerá en la primer página o en la que se indique en una variable interna de sesión ulilizada por la clase, si la opción p_preservepagenumber se ha establecido).

Un punto importante a considerar es que en el costructor se renderizan algunos elementos html y javascripts necesarios para el control del paginado, por lo que es imprescindible que el constructor sea invocado dentro del formulario <form> para que funcione correctamente.

Ejemplos:

El método getRecords

El método getrecords devolverá los registros correspondientes a la página seleccionada.

Ejemplo:

El método renderPager

La invocación al método renderPager renderizará la porción de elementos html necesaria para poder efectuar el paginado.

Ejemplo:

El método getPageNumber

El método getPageNumber devolverá cual es el número de página seleccionado.

Ejemplo:

El método getPageCount

El método getPageCount devolverá la cantidad de páginas totales

Ejemplo:

La propiedad (variable pública de la clase) CSSClass

La propiedad CSSClass Permite asignarle el nombre de un estilo deseado al paginador y también leerlo.

Para asignar los estilos adecuadamente hay que considerar la estructura del paginador renderizada, la cual se muestra a continuación:

Ejemplo:

si no se asigna ninguna clase de estilos, el paginador tomará por defecto el estilo Pager_ + p_uid, para nuestro ejemplo donde p_uid es “Usuarios” el paginador utilizará el estilo Pager_Usuarios por defecto.

Soporte en Javascript

La clase renderizará una serie de funciones javascript por si se desea interactuar con el paginado desde el cliente, todas las funciones javascript terminarán con el postfijo p_uid, Supongamos para nuestro ejemplo que nuestro p_uid es “Usuarios” entonces la clase
renderizará las funciones:

getPageNumberUsuarios, que permite conocer el número de página seleccionado del objeto con p_uid “Usuarios”

getPageCountUsuarios, que permite conocer el número total de páginas del objeto con p_uid “Usuarios”

goToPageUsuarios(número de página), provocará que la página se envié al servidor y vuelva al cliente en la página solicitada en la función del objeto con p_uid “Usuarios”.

Esperamos que esta pequeña contribución les pueda ser de ayuda y a continuación dejaremos una página PHP con la clase y otra con algunos ejemplos

Para que el ejemplo pueda ejecutarse deberá crearse en MySql, en una base de datos que se desee, una tabla que deberá llamarse tblUsuario con dos campos VARCHAR, nombre y apellido y deberán establecerse los datos de la conexión en la página Ejemplos.php en a través de las variables $ej_hostname_cnx (nombre del servidor MySql), $ej_database_cnx (nombre de la base de datos), $ej_username_cnx(nombre de usuario) y $ej_password_cnx(clave del usuario) que se encuentran al principio de la página.

El uso de arreglos (o arrays) es tan necesario en ciertas ocasiones que ha hecho que su aparición sea tan inmediata como los primeros lenguajes declarativos, y hasta la actualidad, los lenguajes más modernos no los han descartado.

Entonces ¿podrían ser los arreglos tan necesarios en el T-SQL de Sql Server? La respuesta es que depende para quien, como suelen ser las respuestas a casi todas las preguntas formulables. Quizás esta necesidad no se manifieste tanto dentro del propio motor de base de datos, ya que siempre es posible acceder a los datos necesarios de alguna u otra forma, pero dentro del mundo de los desarrolladores la necesidad es innegable, como lo veremos con un ejemplo posteriormente. Claro que todo este preámbulo no tendría sentido si el T-SQL de Sql Server tuviese arreglos, pero de hecho no los posee (dejemos de lado por ahora la llegada de SQL Server 2008). ¿Entonces como se puede resolver este problema?, plantearemos entonces el siguiente caso que es, como acostumbramos, un caso del mundo real.

Hace un tiempo desarrollamos una aplicación que empleaba SQL Server 2005 como gestor para el soporte de datos. No tardó en aparecer la siguiente situación, entre nuestras tablas se daba, por las reglas de negocios el caso de que cuando se daba de alta un registro en una tabla del tipo “cabecera” debían asociársele unos cuantos registros del tipo “detalle” conjuntamente. Supongamos el caso de las tablas que se muestran a continuación que representan la estructura del problema abstrayendo complicaciones adicionales.

En nuestro caso estábamos empleando C# sobre el Framework 3.5 y ADO.NET para que nuestra capa de datos accediera al motor de base de datos SQL Server 2005, ante el problema analizamos las siguientes opciones;

1. Emplear procedimientos almacenados (haciendo tantas llamadas como fueran necesarias).
2. Emplear consultas dinámicas.
3. Emplear un procedimiento almacenado que resolviera todo de alguna forma.

Recordemos que nuestro problema a resolver era que cuando se creaba un registro cabecera, en conjunto se creaban siempre varios registros detalle.

La primera aproximación fue la más inocente, poseer un procedimiento almacenado que inserta una cabecera y otro que inserta un detalle, llamémoslos ins_cabecera e ins_detalle para darle nombres intuitivos, entonces si había que crear una cabecera con, por ejemplo, 10 detalles. La metodología era llamar a los procedimientos almacenados desde nuestra capa de datos en .NET de la siguiente forma

Claro está que funciona perfectamente, pero hay algo que no se ve muy bien y es cada vez que se llama a una inserción en la capa de datos de .NET se produce una larga cantidad de llamadas contra el motor de base de datos, a pesar de ser procedimientos almacenados, claro está que hay una gran pérdida de tiempo. Inmediatamente se pensó que había que evitar hacer tantas llamadas.

Una segunda opción, (que creo que la descartamos incluso desde antes que a alguien se le ocurriera mencionarla), es la del uso de consultas dinámicas. Básicamente es la idea de escribir una larga sentencia T-SQL y pasársela al servidor de una sola vez para que el mismo la ejecute, este tipo de solución haría una sola llamada, claro está, pero hay muchas desventajas al usar consultas dinámicas en contrapartida a procedimientos almacenados.

En SQL Server (y estoy convencido que en cualquier otro motor de base de datos también), el uso de procedimientos almacenados ofrece ventajas en contraste a las consultas dinámicas, un factor clave es por ejemplo la compilación del plan de trabajo (la estrategia que empleará el motor de base de datos para acceder a los datos) que es reutilizada en procedimientos almacenados y no tiene que recalcularse cada vez que se lanza la consulta, un factor que suma puntos a la hora de evaluar la performance.

Además el uso de procedimientos almacenados asegura la inmunidad contra ataques por injection, como ya sabemos es posible crear consultas dinámicas parametrizadas, pero queda en manos de l del programador utilizarlos, quien puede omitir esta práctica y armar las sentencias por simple concatenación, quedando el sitio vulnerable a ataques por injection, (aunque parezca una locura, claramente este problema es más común de lo que uno se imagina), en cambio, en las llamadas a procedimientos almacenados no existe la posibilidad de dejar esa puerta de entrada. Finalmente otra ventaja es que los procedimientos almacenados pueden invocarse unos a otros, lo cual puede permitir atomizar y reutilizar código, otro punto a favor (Exceptuando lamentablemente nuestro caso en SQL Server 2005 donde la opción no está disponible) los procedimientos almacenados pueden depurarse con mucha facilidad, estos son a mi parecer motivos suficientes para desterrar a las consultas dinámicas, aunque hay situaciones en que no es posible utilizar procedimientos almacenados, por ejemplo existen aplicaciones que crean, modifican y destruyen estructuras de datos dentro del motor de base de datos empleando instrucciones DDL, en éstos casos el acceso a datos posiblemente deberá llevarse a cabo mediante consultas dinámicas. Esperemos que los programados sean cautos en esos casos delicados.

Entonces nos quedó nuestra última carta emplear una sola llamada a un procedimiento almacenado que resolviera todo el problema. Siendo algo así como la solución mágica, la misma consideraría pasarle a nuestro procedimiento almacenado, llamémoslo ins_cabdet, los datos de la cabecera y una estructura con todos los detalles (ya que no sabemos cuántos puede haber) para que los inserte juntos ins_cabdet de una sola vez.

Nuestra función desde la capa de datos en .NET tomaría la siguiente forma:

ins_cabdet “datos cabecera”, Estructura(“datos detalle 1″, “datos detalle 2″ … “datos detalle 10″)

Pero aquí mismo es donde aparece el problema, un arreglo nos vendría más que bien en este caso, pero ya dijimos que SQL Server no soporta arreglos (para quien recién halla cambiado de canal y se haya perdido el principio, recordemos que estamos omitiendo SQL Server 2008). Claramente ahora deberíamos pensar que los arreglos si eran tan necesarios el T-SQL de Sql Server. De todas formas no todo estuvo perdido, el problema pudimos resolverlo desde otro lugar algo lejano. Una de las características que posee SQL Server 2005 es el uso de XML, SQL Server 2005 posee un tipo de datos XML y es capaz de parsear XML’s empleando incluso XPath, entonces la idea fue clara. si no podemos pasarle arreglos al SQL Server intentemos pasarle un parámetro XML con los datos necesarios para poder realizar las inserciones, el XML es flexible y fácil de usar por lo que este tipo de solución es adaptable a muchos casos similares. El precio es claramente la creación del XML desde .NET y el parseo desde SQL Server, pero hoy día ya casi nada es gratis.

Debimos diseñar primero un formato de XML, creamos algo similar a lo siguiente:

Con nuestro fragmento XML armamos un procedimiento almacenado, al que llamamos Ins_CabDet el cual haría lo siguiente:

• Extracción de cabecera del xml
• Inserción de cabecera
• Para cada detalle del xml
  • Extracción del detalle del xml
  • Inserción del detalle

En el mundo real la función tomó la siguiente forma:

Básicamente hace lo esperado, parsea el XML y lo inserta en las tablas adecuadamente.

Finalmente quedaba un último punto, efectuar la llamada desde la capa de datos en .NET, escribimos el código necesario (en nuestro caso estábamos empleando VS2008) para armar el XML y llamar al procedimiento almacenado, creamos una función similar a la siguiente:

Detalle es la estructura:

Con esta simple solución pudimos resolver el problema. Un punto a considerar es que en realidad no estamos pasando arreglos, de alguna forma estamos adaptando algo que fue pensado para otra cosa, como una especie de artificio para conseguir nuestro fin, por lo que este tipo de solución no es una solución universal y debería evaluarse en qué casos simplifica y en que otros complica el problema a resolver. Otra opción que no mencionamos es crear un tipo de datos y un procedimiento CLR, aprovechando las ventajas de poder ejecutar código manejado dentro del motor SQL Server 2005, esta es una opción válida que dependiendo del caso también podría implementarse.

Arreglos en SQL 2008

La versión 2008 de Sql Server ha resuelto el problema, finalmente hay arreglos disponibles, aunque los arreglos en T-SQL poseen una visión más cercana al manejo de tablas que al manejo de arreglos de los lenguajes de programación a los que los desarrolladores estamos acostumbrados, esto no provoca ningún problema. La idea aplicada al caso anterior es la siguiente;

suponiendo que seguimos teniendo las mismas tablas Cabecera y Detalle, lo primero que vamos a hacer es crear un tipo de datos de usuario, en SQL Server 2008 no hay que escribir código CLR, la forma de hacerlo es simplemente escribir un script, para nuestro caso el siguiente:

Algo bastante similar a nuestra estructura en C#, una vez creado el tipo de datos ya podemos pasar a crear el procedimiento almacenado

Claramente puede verse en T-SQL un arreglo es visto como una especie tabla, en cada fila de la tabla hay un elemento (del tipo Detalle en nuestro caso, para eso primero debimos crearlo) y
cada columna representa una propiedad del elemento (Nombre, OtroDato1, etc.), como mencionamos previamente, es un poco diferente a lo usual, pero no puede negarse que es una visión bastante razonable para un motor de base de datos.

Finalmente queda ver como efectuar la llamada desde .NET, en C# la llamada tomaría la siguiente forma:

Un último detalle es que desde en el código C# puede verse como el arreglo es preparado como una tabla, mapeando la estructura que el motor espera recibir.

SQL Server 2008 trae muchas otras novedades como los campos Date (sin Time) y Time (sin Date), tipos de datos espaciales, la sentencia MERGE y otras cosas que seguramente veremos en algún otro artículo si es que las hemos utilizado para resolver algún problema.

Probablemente si recién está comenzando a utilizar el SQL Server 2008 se vea frustrado al intentar modificar la estructura de una tabla desde el diseñador que trae el Managemente Studio y este le devuelva un error como el siguiente: “Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”.

Esto es debido a un nueva opción agregada al diseñador que, por defecto, evita que los usuarios puedan hacer cambios que requieran la eliminación y recreación de las tablas.  Para modificar esta opción y poder utilizar el diseñador como en las versiones anteriores, hay que destildar la opción “Prevent saving changes that require table re-creation“, ubicada en el menú “Tools”, opción “Options…”, dentro del item “Designers” y “Table and Database Designers” del árbol de la izquierda.

Espero que este “mini-tip” les sea de utilidad.

Cómo muchos sabrán, ayer salió la versión RTM (ya no un Release Candidate) del Microsoft® SQL Server 2008, el cual bajamos e instalamos en nuestro servidor. El primer inconveniente que encontramos fue que no se pudo hacer un upgrade desde el SQL Server 2005 Standard al SQL Server 2008 Web Edition, lo cual no fue mucho problema, simplemente desinstalamos nuestro SQL actual, instalamos el 2008 y “attachamos” las bases anteriores en el nuevo motor.

Hasta acá funcionó todo bien, pero no todo es tan sencillo, ya que con el Management Studio del SQL Server 2005 no se puede conectar al del SQL Server 2008 (obviamente), entonces intentamos instalar el Management Studio nuevo en las máquinas de desarrollo, las cuales ya tenian instalado el Visual Studio 2008 Professional.
Luego de varios pasos de validaciones, instalaciones de los archivos del instalador (si, aunque suene redundante) e instalaciones de parches (incluido el .NET 3.5 Service Pack 1), llegamos a un último chequeo en el que el instalador nos informa que hay un error en una “regla”. Este error es, ni más ni menos, que el siguiente: “Rule “Previous releases of Microsoft Visual Studio 2008″ failed.” A previous release of Microsoft Visual Studio 2008 is installed on this computer. Upgrade Microsoft Visual Studio 2008 to the SP1 before installing SQL Server 2008, o traducido, “Error en la regla ‘Versiones anteriores de Microsoft Visual Studio 2008′.” En el equipo hay instalada una versión anterior de Microsoft Visual Studio 2008. Actualice Microsoft Visual Studio 2008 al SP1 antes de instalar SQL Server 2008. Lo primero que intentamos hacer es instalar el “Visual Studio 2008 Service Pack 1″ pero, para nuestra sorpresa, sólo hay un Beta de este paquete, el cual bajamos e instalamos de todas formas, pero todo fue inutil, el instalador del SQL Server 2008 seguia devolviéndonos el mismo mensaje.  Luego de buscar en el sitio de soporte de Microsoft® encontramos una página (http://support.microsoft.com/kb/956139/en-us) la cual nos dice que antes de instalar el SQL Server 2008 hagamos alguno de los siguientes puntos:

• Instalar una versión comercial del Service Pack 1 para Visual Studio 2008 – el cual aún no existe.
• Desinstalar todos los componentes del Visual Studio 2008 anteriores al Visual Studio 2008 - o sea, todos.
• No instalar ningún componente del SQL Server 2008 que requiera el Visual Studio 2008 – el problema acá es que no dice cuales son los componentes que lo requieren pero, para nuestro pesar, descubrimos que el Managemen Studio es uno que si.

Conclusión

Si ya necesitan utilizar el SQL Server 2008 por alguna de sus nuevas características como las de seguridad o sus nuevos tipos de datos GEOMETRY y GEOGRAPHY, lo mejor es instalarlo en un servidor y conectarse desde desde las máquinas de desarrollo con el Visual Studio o con un Terminal Server al servidor, aunque la mejor opción es esperar que liberen la versión final del Service Pack 1 de Visual Studio 2008, la cual estaria disponible el 11 de Agosto.

Comentarios del 11 de agosto del 2008

A partir de hoy ya se puede bajar el Service Pack 1 final para Visual Studio 2008 desde la siguiente dirección: http://www.microsoft.com/downloads/details.aspx?FamilyId=FBEE1648-7106-44A7-9649-6D9F6D58056E&displaylang=en