Diapositiva 1

1
Aislamiento Bloqueo
2
Necesidad de aislamiento

• En ambientes multiusuario, las transacciones
  acceden a los datos simultáneamente
• Datos que no estén aislados pueden estar errados

3
Bloqueo (locking)

• Mecanismo automático que aisla los datos para
  prevenir conflictos de los datos que se están
  modificando

4
Estructura interna de una tabla
5
Alcance de los candados

• El alcance de un candado determina cuántos datos
  se aislan
• Tres alcances

6
Tipos de candados

• El tipo de candado determina la extensión del
  aislamiento de datos de otras transacciones
• Tres tipos de candados
• Shared
• Exclusive
• Update

7
Candados Shared

• Usado por sentencias que leen datos (selects)
• Otros procesos pueden leer los datos (coloca
  candado shared), pero ningún proceso puede
  cambiar los datos (coloca candado exclusive)

8
Candados exclusive

• Usado por sentencias que cambian datos (inserts,
  updates, deletes)
• Ningún otro proceso puede leer los datos (coloca
  candado shared) o cambiar los datos (coloca
  candado exclusive sobre la página)

9
Candados shared y exclusive

• Nota Se intenta involucrar dos tablas un select
  para
• publishers y un delete para authors
• Nota Se usa la opción holdlock, la cual asegura
  que los candados shared no se liberan hasta
  cuando concluya la transacción.
• El instructor tipea
• begin tran
• select from pubs2..publishers
• holdlock
• delete from pubs2..authors
• Espera ver los datos de publishers
• select from pubs2..publishers

10
Candados update

• Usado por operaciones que pueden o no cambiar los
  datos (updates, deletes)
• Cuando el proceso primero escanea los datos, le
  aplica un candado update. Otros procesos pueden
  colocar candados shared, pero ningún proceso
  puede colocar candados exclusive o update

11
Resúmen de tipos de candados

• Updates y deletes usan candados exclusive
  solamente para encontrar los datos que necesitan
  modificar

12
Deadlock
13
Resolución del deadlock
14
Esquema de bloqueo

• Esquema de bloqueo es un atributo de la tabla que
  determina qué datos asociados con la tabla están
  bloqueados

15
bloqueo allpages

• Se pueden bloquear las páginas de índices
• El servidor usa candados de tabla y candados de
  página, pero no candados de fila

16
Bloqueo datapages

• Las páginas de índices nunca se bloquean
• El servidor usa candados de tabla y candados de
  página, pero no candados de fila

17
Bloqueo datarows

• Las páginas de índices nunca se bloquean
• El servidor usa candados de tabla, candados de
  página y candados de fila

18
Comparación de esquemas de bloqueo
19
Fijar el esquema bloqueo

• Sintaxis simplificada
• create table table_name (
• column_name datatype NULL NOT NULL
  IDENTITY ,
• ...
• column_name datatype NULL NOT NULL
  IDENTITY )
• lock allpages datapages datarows
• Ejemplo
• create table publishers (pub_id char(4) NOT
  NULL,pub_name varchar(40) NULL,city varchar(2
  0) NULL,state char(2) NULL)lock datarows
• Si no se especifica un esquema de bloqueo, la
  tabla usa el esquema default de bloqueo

20
Cambiar el esquema de bloqueo

• Sintaxis simplificada
• alter table table_name
• lock allpages datapages datarows
• Ejemplo
• alter table publishers
• lock datapages

21
Ejemplo

• Ver el default del esquema de bloqueo actual
• sp_configure "lock scheme"
• Crear una tabla con el esquema default de
  bloqueo
• create table def_scheme (a int)
• Crear una tabla con un esquema de bloqueo
  especifico
• create table dpl_scheme (a int) lock datapages
• Ver el esquema de bloqueo ambas tablas
• sp_help def_scheme
• exec sp_help dpl_scheme

22
Ejemplo

• Cambiar el esquema de bloqueo de la primera
  tabla
• alter table def_scheme lock datarows
• Ver el esquema de bloqueo ambas tablas
• sp_help def_schemeexec sp_help dpl_scheme
• Borrar los objetos de base de datos creados
• drop table dpl_schemedrop table def_scheme

23
Leer datos no aislados

• Hay tres tipos de consultas o reads, que pueden
  retornar datos que son inadecuados para limitar
  el aislamiento de datos
• Las características de cómo se hacen estos
  reads son propios de cada DBMS
• Hay tres tipo de reads
• Dirty reads
• Nonrepeatable reads
• Phantom reads

24
Lectura sucia

• La transacción 1 modifica datos
• La transacción 2 lee los datos modificados antes
  de que la modificación haya terminado
• Esta transacción lee datos uncommitted o dirty

25
Lectura no repetible

• La transacción 1 lee datos
• La transacción 2 modifica esos datos antes de que
  la primera transacción haya terminado
• La primera lectura es ahora nonrepeatable

26
Lectura fantasma

• La transacción 1 lee un conjunto de filas que
  cumplen una condición
• La transacción 2 modifica los datos de algunas
  columnas que no cumplían esa condición y ahora la
  cumplen, o al contrario
• Las filas que aparecen y desaparecen se denominan
  phantoms

27
Nivel de aislamiento

• Un nivel de aislamiento es un conjunto de
  candados que permiten o no una combinación
  particular de los tres tipos de lectura sucia,
  no repetible o con fantasmas
• ANSI define cuatro niveles de aislamiento, cada
  uno más restrictivo que el anterior

28
Nivel 1 de aislamiento

• Nivel 1 - Comportamiento de select
• Se fijan candados Shared hasta que el select
  termine la lectura de una fila o página
• select espera a que se liberen los candados
  exclusive

29
Nivel 2 de aislamiento

• Nivel 2 - Comportamiento de select
• Se fijan candados Shared hasta que termine la
  transacción
• Este comportamiento es diferente al del nivel 1
• select espera a que se liberen los candados
  exclusive
• Comportamiento discreto de nivel 2 requiere
  bloqueo row-level
• Tables APL y tables DPL no tienen bloqueo
  row-level
• Si una consulta con nivel de aislamiento 2 lee
  una tabla APL o DPL, se forza comportamiento de
  aislamiento nivel 3

30
Nivel 3 de aislamiento

• Nivel 3 El nivel más restrictivo
• Nivel 3 - Comportamiento de select
• Se fijan candados shared hasta que termine la
  transacción
• Este comportamiento es diferente al del nivel 1
• select espera a que se liberen los candados
  exclusive

31
Nivel 0 de aislamiento

• Nivel 0 El nivel menos restrictivo
• Nivel 0 - Comportamiento de select
• Se fijan candados Shared hastaque select termine
  la lectura de una fila o página
• select ignora los candados exclusive
• Este comportamiento es diferente al del nivel 1

32
Fijar nivel de aislamiento

• Sintaxis para aislamiento a nivel de sesión
• set transaction isolation level 0 read
  uncommitted
• 1 read committed 2 repeatable read
• 3 serializable
• Sintaxis para aislamiento a nivel de sentencia
• select ...
• at isolation
• 0 read uncommitted 1 read committed
• 2 read repeatable
• 3 serializable

33
holdlock y noholdlock

• holdlock forza nivel de ailamiento 3, sin
  importar el nivel de aislamiento actual
• Para select se fijan candados shared hasta que
  termine la transacción
• noholdlock forza nivel de ailamiento 1, sin
  importar el nivel de aislamiento actual
• Para select se liberan los candados shared cuando
  se ha leido una fila o página
• Sintaxis simplificada
• select column_list
• from table_list holdlock noholdlock
• Ejemplo
• select title
• from titles holdlock
• where pub_id "0877"

34
(No Transcript)
35
Cursor

• Un cursor es un mecanismo que sirve para procesar
  fila por fila los resultados de una consulta

36
Beneficios de los cursores

• Se pueden procesar los datos fila por fila
• SQL es un lenguaje orientado a conjuntos
• El procesamiento se hace normalmente sobre las
  filas que cumplan con una condición dada
• Los cursors permiten el procesamiento fila por
  fila
• Se pueden modificar los datos fila por fila
• Se puede sortear la brecha existente entre la
  orientación a conjuntos de las bases de datos
  relacionales y la orientación a filas de muchos
  lenguajes de programación

37
Ciclo de vida de un cursor

• 1. Declarar el cursor
• 2. Abrir el cursor
• 3. Tomar cada fila
• 4. Cerrar el cursor
• 5. Desasignar el cursor

38
Paso 1 Declarar el cursor

• Cuando se declara un cursor
• Se especifica una consulta
• Se especifica un modo para el cursor
• De solo lectura
• Para actualización

39
Sintaxis para declarar un cursor

• Sintaxis simplificada
• declare cursor_name cursor
• for select_statement
• for read only update of
  column_name_list
• Ejemplo
• declare biz_book cursor
• for select title, title_id from titles where
  type "business" for read only
• go

40
Paso 2 Abrir el cursor

• Cuando se abre el cursor
• El servidor crea el conjunto resultado
• El apuntador está señalando antes de la primera
  fila del conjunto respuesta

41
Sintaxis para la apertura de un cursor

• Sintaxis
• open cursor_name
• Ejemplo
• declare biz_book cursor
• for select title, title_id from titles where
  type "business" for read only
• go
• declare _at_title char(80), _at_title_id char(6)
• open biz_book
• fetch biz_book into _at_title, _at_title_id
• while _at__at_sqlstatus 0
• begin
• -- process _at_title and _at_title_id fetch
  biz_book into _at_title, _at_title_id
• end
• close biz_book
• deallocate cursor biz_book

42
Paso 3 Tomar cada fila

• Cuando se ejecuta un fetch
• El cursor señala a la siguiente fila válida
• Retorna la siguiente fila válida

43
Sintaxis de un fetch

• Sintaxis
• fetch cursor_name into fetch_target_list
• Ejemplo
• declare biz_book cursor
• for select title, title_id from titles where
  type "business" for read only
• go
• declare _at_title char(80), _at_title_id char(6)
• open biz_book
• fetch biz_book into _at_title, _at_title_id
• while _at__at_sqlstatus 0
• begin
• -- process _at_title and _at_title_id fetch
  biz_book into _at_title, _at_title_id
• end
• close biz_book
• deallocate cursor biz_book

44
Pasos 4 y 5 Cerrar y desasignar el Cursor

• Cuando se cierra un cursor
• Termina el procesamiento de la consulta hecha
• Cuando se desasigna el cursor
• Se liberan todos los recursos de memoria
  asignados al cursor

45
Cerrar y desasignar un Cursor

• Sintaxis
• close cursor_name
• deallocate cursor cursor_name
• Ejemplo
• declare biz_book cursor
• for select title, title_id from titles where
  type "business" for read only
• go
• declare _at_title char(80), _at_title_id char(6)
• open biz_book
• fetch biz_book into _at_title, _at_title_id
• while _at__at_sqlstatus 0
• begin
• -- process _at_title and _at_title_id fetch
  biz_book into _at_title, _at_title_id
• end
• close biz_book
• deallocate cursor biz_book

46
Variables para el manejo de cursores

• Se tiene una variable que retorna el número total
  de filas procesadas (_at__at_rowcount)
• Se tiene una variable que indica el estado o
  resultado de mover el cursor (_at__at_sqlstatus)
• Exitoso se alcanzó una fila válida
• Hay un error al tratar de tomar la fila
• Ya se procesaron todas las filas

47
Notas adicionales para fetch

• fetch siempre mueve el apuntador a la siguiente
  fila válida en el conjunto respuesta
• Algunos servidores permiten regresarse a una fila
  anterior
• Cerrar y reabrir un cursor hace que el apuntador
  siempre señale al comienzo
• Por default, fetch siempre retorna una fila
• Algunos servidores permiten cambiar este defaullt
• Sintaxis
• set cursor rows number for cursor_name
• Ejemplo
• set cursor rows 5 for biz_book

48
Prácticas recomendadas para desarrollo

• Siempre especificar el modo del cursor en la
  sentencia declare
• Como los cursores pueden demandar muchos
  recursos, evitar dejar abiertos los cursores por
  mucho
• Si se ejecuta la misma operación en cada fila del
  cursor, hay que buscar una alternativa

49
Ejemplo de cursor

• declare books_csr cursor for
• select title_id, type, price
• from titles
• for read only
• go
• -- List all business and mod_cook books. Show
  business books
• -- at 8 increase in price. This cursor allows
  you to
• -- selectively manipulate a subset of the rows
  while
• -- retaining a single result set.
• declare _at_title_id tid, _at_type char(12), _at_pr
  ice money
• open books_csr
• -- initial fetch
• fetch books_csr into _at_title_id, _at_type, _at_price

50
Ejemplo de cursor
while _at__at_sqlstatus 0 begin if _at__at_sqlstatus
1 begin raiserror 30001 "select
failed" close books_csr deallocate cursor
books_csr return end if
_at_type"business" select _at_title_id,
_at_type,CONVERT(money,_at_price1.08) else if
_at_type"mod_cook" select _at_title_id, _at_type,
_at_price -- subsequent fetches within
loop fetch books_csr into _at_title_id, _at_type,
_at_price end
51
Ejemplo de cursor

• close books_csr
• deallocate cursor books_csr
• go
• Results
• - - ------------------------
• BU1032 business 21.59
• - - ------------------------
• BU1111 business 12.91
• - - ------------------------
• BU2075 business 3.23
• - - ------------------------
• BU7832 business 21.59
• - - ------------------------
• MC2222 mod_cook 19.99
• - - ------------------------
• MC3021 mod_cook 2.99

52
Alternativas al uso de cursores

• Los cursores no son la única manera de ejecutar
  una tarea
• Alternativa usar case
• select title_id, type,
• case type
• when "business" then price 1.08
• when "mod_cook" then price
• end
• from titles
• where type in ("business", "mod_cook")
• Alternativa hacer dos consultas
• select title_id, type, price 1.08
• from titles
• where type "business"
• select title_id, type, price
• from titles
• where type "mod_cook"

53
Ejemplo de cursor

• declare title_author_csr cursor for
• select authors.au_id, au_fname, au_lname, title
• from titles, authors, titleauthor
• where titles.title_id titleauthor.title_id
• and authors.au_id titleauthor.au_id
• order by upper(au_lname), upper(au_fname)
• for read only
• go
• set nocount on --Turns off display of rows
  affected
• declare _at_fname varchar(20), _at_lname varchar(40),
•         _at_title varchar(80), _at_au_id char(11),
•         _at_old_au_id char(11)
• open title_author_csr
• fetch title_author_csr into _at_au_id, _at_fname,
  _at_lname, _at_title

54
Ejemplo de cursor
while _at__at_sqlstatus 0   begin      if _at__at_sqlstatus
1        begin          raiserror 23000
"Select failed."          return        end      i
f _at_au_id ltgt _at_old_au_id        begin          print
"   "          print "1! 2! is the author of
these books", _at_fname, _at_lname        end      
print "    1!", _at_title      select _at_old_au_id
_at_au_id      fetch title_author_csr into _at_au_id,
_at_fname, _at_lname, _at_title   end
55
Ejemplo de cursor

• close title_author_csr
• deallocate cursor title_author_csr
• set nocount off --Turns back on display of rows
  affected
• go
• Resultados
• ...
• Ann Dull is the author of these books
•        Secrets of Silicon Valley
• Marjorie Green is the author of these books
•        You Can Combat Computer Stress!
•        The Busy Executives Database Guide
• Burt Gringlesby is the author of these books
•        Sushi, Anyone?
• ...

56
Ejercicio con cursores

• Declarar un cursor
• declare ca_authors cursor
• for select au_lname, au_fname, state
• from pubs2..authors
• where state "CA"
• for read only
• Abrir el cursor
• open ca_authors
• Tomar tres filas y mostrarlas
• fetch ca_authors
• fetch ca_authors
• fetch ca_authors
• select _at__at_rowcount

57
Ejercicio con cursores

• Cerrar el cursor
• close ca_authors
• Cómo se pueden tomar más de una fila del
  cursor?_____________________________________
• Desasignar el cursor
• deallocate cursor ca_authors

58
Actualizar datos usando cursores

• Sintaxis simplificada
• update table_name
• set column1 expression select_statement
• , column2 expression select_statement
  ...
• where current of cursor_name
• Ejemplo
• update titles
• set title "The Executives Database Guide"
• where current of biz_book
• Actualiza la fila a la que señala el apuntador
• En la mayoría de casos, esta fila es la tomada
  más recientemente
• NO mueve el cursor a la siguiente fila
• Sólo se pueden actualiza cursores declarados en
  modo update

59
Borrar datos usando cursores

• Sintaxis simplificada
• delete from table_name where current of
  cursor_name
• Ejemplo
• delete from titles
• where current of biz_book
• Borra la fila que está siendo señalada por el
  apuntador
• En la mayoría de casos, esta fila es la tomada
  más recientemente
• Mueve el aputador del cursor a la fila siguiente
• Sólo se pueden actualiza cursores declarados en
  modo update

60
Reglas para actualizar cursores

• La tabla sobre la cual el cursor va a actuar debe
  estar declarada
• Con un índice único
• o
• Usando un esquema de bloqueo tipo Datapages o
  Datarows

61
Ejemplo de cursor

• -- Increase all prices less than the average
  price by 50
• -- Decrease all prices greater than or equal to
  the average
• -- price by 25
• declare title_update cursor
• for select title_id, price from titles
• for update
• declare _at_avg_price money, -- local
  variables _at_title_id  tid, _at_price
      money
• open title_update -- execute cursor
• begin tran
• -- calculate average price
• select _at_avg_price avg(price) from titles
  holdlock
• fetch title_update into _at_title_id, _at_price

62
Ejemplo de cursor
while _at__at_sqlstatus 0 begin if _at__at_sqlstatus
1 -- error occurred begin rollback
tran raiserror 21001 "Fetch failed in
cursor" close title_update deallocate
cursor title_update return end if _at_price lt
_at_avg_price update titles --increase by
50 set price price 1.50 where
current of title_update else update
titles -- decrease by 25 set price price
.75 where current of title_update
63
Ejemplo de cursor
if _at__at_error ltgt 0 begin rollback
tran raiserror 22001 "Update failed" close
title_update deallocate cursor
title_update return end fetch title_update
into _at_title_id, _at_price end commit tran close
title_update deallocate cursor title_update go ...

64
Cursores y transacciones

• Para cursores for update obtener bloqueos update
• Los bloqueos se promueven a bloqueos exclusivos
  cuando se ejecuta un update where current of o
  delete where current of
• Si no se promueve, el bloqueo update se libera
  cuando el cursor se mueve a la siguiente página
  de datos
• close on endtran es una opción que determina qué
  le pasa al cursor en una transacción cuando se
  llega a un rollback o commit
• Cuando está activo, el cursor se cierra después
  de un rollback o commit
• Cuando no está activo
• El cursor permanece abierto después de un
  rollback o commit
• Las modificaciones basadas en la posición de un
  cursor se pueden ejecutar fila por fila, lo cual
  puede incrementar la concurrencia
• Sintaxis
• set close on endtran on off

65
Cursor a nivel de servidor

• Un cursor a nivel de servidor es aquel creado en
  un stored procedure
• Ejemplo
• create proc proc_fetch_bookasdeclare _at_title
  char(30), _at_title_id char(6)declare
  biz_book cursor for select title, title_id from
  titles where type "business"
• for read onlyopen biz_bookfetch biz_book into
  _at_title, _at_title_id-- additional processing
  hereclose biz_bookdeallocate cursor
  biz_bookreturn

66
Alcance de cursores a nivel servidor

• Los stored procedures pueden tomar datos de
  cursores creados por un procedimiento que llama
  al procedimiento dado