Twitter Facebook RSS Feed

jueves, 26 de noviembre de 2009 a las 10:27hs por Dario Krapp

Actualización de datos y modos de bloqueo

Hasta el momento, los cursores que hemos visto, realizaban operaciones de lectura de datos, no vale la pena mencionar que es totalmente factible incluir operaciones de actualización de datos mientras que los mismos son leídos, como se muestra en el ejemplo:


Update leyendo

No me imagino una opción menos performante para actualizar la columna Description, pero omitamos ese detalle, ya que solo hemos incluido estas líneas de código para mencionar que es posible definir cursores actualizables y realizar la misma tarea de la siguiente forma (aunque obviamente la performance sigue siendo igual de lamentable):


Cursor Update

Un detalle a mencionar es que en este caso el cursor deberá ser DYNAMIC o KEYSET (siempre que existan las claves primarias necesarias para la segunda opción) ya que en caso contrario la actualización fallará.

Es posible incluso indicar en el cursor que columnas se modificarán, utilizando la sentencia FOR UPDATE FOR, (en caso contrario se asumirá que cualquier columna podrá modificarse, como sucede en el ejemplo previo). Para probarlo realizaremos la siguiente modificación en el codigo:


Cursor For Update Of

Se ha producido un error debido a que la columna “Description” no ha sido informada como una columna de modificación. La modificación funcionará correctamente si la columna Description es agregada a la lista de campos modificables.

Cabe mencionar que la sentencia FOR UPDATE OF no protegerá al campo generando un error si el registro es eliminado, para verificar esto podrá reemplazarse la sentencia UPDATE por DELETE como se muestra a continuación:

DELETE FROM Production.ProductDescription
WHERE CURRENT OF ProdInfo

En este punto, donde ya nos hemos formulado infinidad de preguntas, podremos continuar con nuestras interrogantes y peguntarnos si la actualización utilizando las sentencia UPDATE o DELETE con WHERE CURRENT OF tendrá algún detalle del cual no nos hemos percatado aún. Si estamos tranquilos porque en los casos previos todo ha funcionado según lo esperado, deberíamos hacer la siguiente prueba; En un cursor de modificación luego de que una fila es leída por el cursor, pero antes que el mismo la modifique, la modificaremos externamente, por ejemplo utilizando otro SQL Server Management Studio.

Realizar esta tarea es sencillo si es posible debugear el código, como ya hemos hecho previamente y como se indica a continuación:


Cursor Optimista

En este caso la actualización de la fila en cuestión (o sea la fila que ha sido leída pero actualizada luego que la sentencia update externa ha modificado los datos) no podrá realizarse y se obtendrá un mensaje similar al siguiente:


Error concurrencia optimista

Lo que ha sucedido estará relacionado con el tipo de bloqueo del cursor y el mensaje “Optimistic concurrency check failed” es todo un indicio. Los cursores son por defecto optimistas, esto significa que cuando leen un registro utilizan un número de versión para saber si el mismo ha sido externamente modificado, si el número de versión ha cambiado del leído en la sentencia FETCH la actualización fallará como nos ha sucedido en el ejemplo previo. Esta táctica de bloqueos es bastante lógica para los cursores, si consideramos que son operaciones extremadamente lentas, por lo que no es deseable que mantengan registros bloqueados. La opción OPTIMISTIC es la asumida por defecto, aunque podrá especificarse explícitamente utilizando la no sorpresiva palabra clave OPTIMISTIC tal como se muestra en el ejemplo:

DECLARE ProdInfo CURSOR DYNAMIC FORWARD_ONLY OPTIMISTIC FOR SELECT ...

Es posible modificar este comportamiento y definir cursores que bloqueen la fila mientras la misma es modificada, la palabra clave SCROLL_LOCKS permitirá realizar esta poco recomendable modificación del comportamiento del cursor. Bajo este contexto, si repetimos la prueba previa notaremos que la sentencia UPDATE externa quedará en espera, ya que ha sido bloqueda por el cursor. Ni pensar en la escalabilidad del proyecto que emplee esta posibilidad, quizás el termino pesimista sea más que adecuado.

Finalmente la última opción disponible de bloqueo, conocida como READ_ONLY, no realizará bloqueos ni controles de versión pero no permitirá actualizar registros.

Previamente habíamos mencionado a los cursores del tipo FAST_FORWARD, este tipo de cursor definirá un cursor READ_ONLY y FORWARD_ONLY conjuntamente y será la opción de cursor más performante posible.

Los cursores además podrán definirse como locales (LOCAL) o globales (GLOBAL), en el primer caso tendrán el alcance del stored procedure, trigger o batch (grupo de sentencias) que lo ha definido, en el segundo caso su alcance será a nivel de conexión.

Por otra parte, podrá incluirse en la definicion del cursor la palabra clave TYPE_WARNING, que indicará que se informen las conversiones implícitas de un cursor, y si nos preguntamos ¿qué es una conversión implícita de cursor?, recordaremos que habíamos mencionado un ejemplo hace algunos párrafos cuando un cursor definido como KEYSET se comportaba como cursor STATIC, eso es exactamente una conversión implícita de cursor (o degradación del cursor). Existen varias conversiones de este tipo dependiendo del tipo de cursor definido y la condición subyacente (y es indudablemente útil estar familiarizado con ellas para evitar confusiones), para quienes estén interesados en conocerlas en profundidad les dejo el siguiente enlace:

Conversiones implícitas de cursor

la forma de incluir esta palabra clave en la declaración se muestra en el siguiente ejemplo:

DECLARE ProdInfo CURSOR KEYSET TYPE_WARNING FOR SELECT [Descripcion] FROM Ejemplo1

Si el cursor es convertido, se indicará con un mensaje similar al siguiente:

The created cursor is not of the requested type. 

Finalmente, un último detalle a comentar es que además del diverso conjunto de opciones que hemos visto en este articulo, los cursores en SQL Server aceptarán las opciones de sintaxis SQL-92 de las cuales podemos destacar, la posibilidad de crear cursores de un tipo denominado INSENSITIVE, que no son más que cursores de tipo SCROLL Y READ_ONLY conjuntamente.

Como resumen final, podemos comentar que los cursores son una posibilidad potente y de fácil uso para acceder y modificar datos sobre SQL Server, pero no debemos olvidar que son muy costosos en términos de performance, por lo que cuando sea aplicable otro tipo de opción (como por ejemplo una variable tabla) la misma será en el común de los casos una elección menos costosa. Por otra parte, cuando no sea posible evitar el uso de cursores es importante recordar que los cursores READ_ONLY serán la opción de bloqueo menos costosa y que un cursor FORWARD_ONLY será la mejor opción de recorrido. Si tenemos la suerte de poder combinar ambas opciones (READ_ONLY y FORWARD_ONLY) en un cursor FAST_FORWARD estaremos en el mejor de los casos.

También es importante recordar que las opciones STATIC y KEYSET no son las más deseables ya que copiarán datos (ya sea claves o filas enteras) a la tabla tmpdb y que el bloqueo SCROLL_LOCKS es el menos popular, ya que comprometerá más la escalabilidad que las otras opciones de bloqueo.

Terminando con las conclusiomes solo me queda por desear que este artículo haya sido de ayuda a quienes no estén familiarizados con cursores y para quienes ya los conozcan, espero que hayan encontrado en estas líneas algún detalle inesperado o que habían olvidado. Nos vemos en el próximo artículo!.

41 comentarios »

  1. HMB dice:

    Claro y sencillo… me ayudo.

  2. Rayco dice:

    Una buena iniciación al uso de los cursores en Transact SQL, buen aporte.

  3. Kristian dice:

    Excelente esta muy entendible, gracias por el aporte, pero desearia obtener mas info tal vez me podrian ayudar con un manual. O donde puedo conseguir

  4. andrestkd dice:

    Practico y concreto, buen aporte.

  5. Jose Zarate dice:

    Excelente!! mas claro lo tengo

  6. jeremiasm dice:

    En que parte se guardan los cursores, tengo sql server 2008 y no encuentro en el explorador de objetos el cursor creado.

    • Dario Krapp dice:

      Hola jeremiasm, buenos días

      El tema es que un cursor no es un objeto persistente que se almacena en tablas del SQL como por ejemplo las tablas, los stored procedures o los indices entre otros.
      Los cursores son objetos que se crean en memoria (por eso es importante utilizar la sentencia deallocate para desalojarlo de la memoria que utiliza SQL Server). Por ese motivo no los vas a poder encontrar en el object explorer.
      Slds

  7. Luis dice:

    entendi muy bien, se agradece

  8. Edwin dice:

    Gracias Por la Ayuda …
    Buen Aporte .

  9. Sebastian dice:

    Gracias por el aporte, por cierto que buen nombre el del sitio, me veo muy identificado con este!.

  10. ERNESTO dice:

    Muy buen comentario , quisiera me pudieran ayudar , quiero realizar un cursor haciendo lo siguiente espero me puedan ayudar tengo 2 tablas
    Tabla 1 Folio
    Tabla 2 Folio + Precio

    quisiera actualizar mi tabla 1 de la 2 por medio de un cursor,quedo en espera de sus valiosos comentarios

  11. manuel lora dice:

    excelente me gusto

  12. KS dice:

    muy bueno, pero quisiera que me puedas ayudar, quiero hacer un cursor que me ayude a actualizar un campo por ejemplo:

    telefono DNI1
    telefono DNI1

    de lo anterior quisiera lo siguiente:

    telefono1 DNI1
    telefono2 DNI1

    Tendras algun material que me pueda ayudar.

    Saludos

  13. jrcastillof dice:

    Muy Buen Aporte,, lo voy a revisar, mas tarde (a la media noche), pero por lo que leí se nota claro y sencillo.

  14. VerSanCheZ dice:

    Hola a todos, tengo dos tablas ( Empleados y Entradas ), lo que quiero saber es como le hago para que la consulta me devuelva lo siguiente:

    No. Nombre Reg1 Reg2 Reg3 Reg4
    01 Pedro 07:10:20 11:45:00 12:36:14 18:50:44
    02 Luis 09:15:20 11:05:00 11:36:14 17:50:44

    La Tabla Empleado tienen los siguientes Campos;
    No. int
    Nombre varchar

    Registros de la tabla de Empleados
    01 Pedro
    02 Luis
    03 Paco

    Y la Tabla Entradas
    No. int
    Fecha date
    Registro time(0)

    Registros de la tabla Entradas
    01 2015-01-01 07:10:20
    02 2015-01-01 09:15:20
    01 2015-01-01 11:45:00
    02 2015-01-01 11:05:00
    01 2015-01-01 12:36:14
    02 2015-01-01 11:36:14
    01 2015-01-01 18:50:44
    02 2015-01-01 17:50:44

  15. carlos dice:

    Realmente muy útil!

  16. Jonathan dice:

    Muy bien explicado, excelente (y)

  17. Jesus Ob Ac dice:

    gracias por el aporte, me ha servido como no tienes idea.

    saludos

  18. Roman dice:

    Muchas gracias! Mas claro imposible!!!

  19. Jaqueline Pérez dice:

    Muchas Gracias, es muy util esta información.

    Pero tengo una duda, cree un cursor dentro de un procedimiento almacenado para hacer mas sencilla su ejecución, al ejecutarlo por primera vez no hay problema funciona bien, pero si lo ejecuto por segunda vez no me regresa nada, tengo que esperar para volverlo a ejecutar, a pesar de que tengo el “Dellocate” y el “Close”, ¿Alguna idea de que pudiera pasar?

  20. Jack SK dice:

    Oye, muchas gracias por compartir esta información tan importante de una manera tan clara.

  21. Daniel dice:

    Muy buena explicación!!! Clara y sencilla

  22. Eduardo R. A. dice:

    Hola Dario. Primero gracias por su ayuda.

    Como puedo hacer en el uso de cursores para ver los resultados del proceso de cada registro del cursor. He notado que cuando utilizo cursores los resultados solo se muestran al finalizar el proceso completo del cursor.

    Saludos.
    Nuevamente GRACIAS.

  23. Oscar Ocar's Quiñonez dice:

    Buenísima explicación!!! Me sirvió bastante!

  24. Edgard Huillca dice:

    Como llamo a un cursor dentro de un store

  25. efuv0464 dice:

    Se puede declarar un cursor con la base de datos pasada como parámetro al sp????

  26. kunaguaro007 dice:

    El dia que quiten esta pagina me quedo sin cursores, tengo como 5 años copiando y pegando el codigo jajajajaja

  27. juanmadhardy dice:

    muy bueno, y muy bien escrito.

  28. Sergio Euan dice:

    Muy bueno amigo mio, he revisado este post dos veces y las dos ocasiones me ha servido de mucho. Muchas gracias por el aporte.

  29. Gerardo Chavarria dice:

    Gracias por el aporte, estoy empezando en el uso de cursores

  30. Chava ch dice:

    tengo dudaa

    DECLARE ProdInfo CURSOR FOR SELECT Nombre , precio , cantidadtotal FROM Production.Product

    se puede hacer esto??? el cursor puede guardar mas parametros?

  31. omar dice:

    muy bueno ,siempre ocupe este ejemplo para mis cursores. Gracias

  32. Juan José dice:

    ¡Que pro!

  33. Angel dice:

    Gracias totales la pagina tiene articulos muy interesantes!

Deja un comentario

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.