Buscar este blog

domingo, 25 de octubre de 2020

Generación de ID Único para una Tabla - Distintos Métodos -

 Buenas tardes,


    Aquí estoy luego de un largo período de tiempo en el que no le daba feed al blog. Varios fueron los motivos. Básicamente nuevas responsabilidades profesionales que requirieron de mi dedicación absoluta y cierto agotamiento que me llevó a este marcado paréntesis.

    Quiero agradecerles a todos y cada uno de Uds. los distintos mensajes, correos y consultas recibidos. Me han hecho ver que el blog sin dudas cumple con su objetivo, que no es otro que el de poder retroalimentar con mis pequeños aportes esta inmensa red profesional  de colegas  con ganas de aprender y  a la vez aportar sus conocimientos.

     Sin más introducciones vamos a este nuevo posteo

Hoy les traigo un tema con el que seguramente han tenido que lidiar en más de una oportunidad...

Se trata de aquel que tiene que ver con la necesidad de generar  un ID único que identifique los registros de una tabla “cuore” del sistema.

Cuando hablamos de tabla “cuore” hablamos de aquellas tablas bases del sistema que suelen ser referenciadas por varias tablas secundarias precisamente a partir de su ID.

Pensemos por ejemplo en un ID de una tabla de “Operaciones” donde registramos todas las transacciones de los clientes de un banco. Dicho Id tendrá luego que sincronizarse como parte del registro de las operaciones en otras tablas secundarias.

El desafío, y de ahí el origen de este post, es que, en la horas “pico”, donde la tabla de “Operaciones” está siendo objeto de numerosas inserciones casi paralelas, la generación del nuevo ID.

1)  No produzca contención por demoras en el proceso de inserción.

2)  No genere intentos de inserción de registros duplicados cuando dicho Id es insertado en tablas secundarias.

 

Es así que les propongo transitar distintos métodos con sus pros y sus contras.

 

METODO 1.  Generación de ID Autoincremental a partir campo Indentity

Es el método tal vez más utilizado.

Se define una tabla con un campo Id que tendrá un valor único al ser definido como “Identity”. Dicho campo  incrementará su valor cada vez que la tabla es insertada.

Luego, se recupera el valor del ID insertado por medio de la función del sistema @@IDENTITY  para su eventual sincronización en tablas secundarias.

 

Ejemplo:

 

--  1) Creación Tabla con "Id" tipo identity --

Create Table Operaciones

(Id int NOT NULL IDENTITY(1, 1),

 Detalle varchar(50) NULL)

  

-- 2) Inserción de registro en Tabla  ---

Insert Into Operaciones( Detalle)

Values ( 'Articulo 234-e venta')

 

-- 3) Obtención del Id para insertar en tablas secundarias ---

Declare @Id integer

Select @Id = @@identity

 

Ventajas

La facilidad de implementación. Tal vez acorde a ello sea la solución más implementada.

Desventajas

Eventualmente puede causar varios problemas veamos:

 

Ø “Problema de Inserción en la Última Página“ 

He podido observar a lo largo de mi trayectoria como DBA un problema  que se repite una y otra vez en tablas “cuore” definidas bajo esta modalidad en sistemas con una alta carga transaccional.

Este problema deviene de la definición de la Primary Key sobre el campo “Id” identiy.

Pero por qué habría de representar un problema la definición de la Pk sobre dicho campo?

Porque normalmente para la definición de dicho PK se utiliza la interface del Sql Server la cual termina generando un Indice Clustered sobre el campo Id por default.

  

Ejemplo seteo de Pk por Consola:








Código que se implementa en la práctica:

Alter Table [dbo].[Operaciones]

Add  Constraint [PK_Operaciones] Primary Key Clustered

([Id] ASC)

GO

Es así pues, que nuestra tabla “Operaciones”, pasa a tener un campo ID con las siguientes características (yo la llamo la “tripleta del problema”):

-         Identity

-         Primary Key

-         Clustered

 

Contando con estas tres características la tabla es firme candidata a sufrir lo que se conoce como “Problema de Inserción en la Ultima Página

Este problema afecta a tablas que están siendo intensamente insertadas con hilos de procesamiento paralelos debido a que todas las inserciones son dirigidas hacia el mismo sector de la tabla produciéndose entonces una fuerte contención que se traduce en notorias demoras en los procesos de inserción de registros en horarios pico.

La solución sería definir la Pk como nonclustered si las reglas de negocio así lo permiten.

 

Ø Eventual duplicación de IDs  

Otro inconveniente en este tipo de esquemas tiene que ver con aquellos escenarios en los que el nivel de inserciones es muy significativo y el ID recuperado por medio de la función @@identity,  y almacenado en la variable @Id,  se duplica al no llegar a refrescarse la variable @Id por la extrema concurrencia del hilos de inserción, casi paralelos en horas pico.

 Como consecuencia de esta eventual incidencia inmediatamente nos encontraremos con intentos de inserción fallidos por intento de violación de PK de tablas secundarias.

Hay una solución para evitar duplicaciones?

Si, el incluir la inserción en la tabla y la obtención de @ID en una transacción.

Veamos:

Begin Transaction

   ---  Inserción de registro en Tabla  ---

   Insert Into Operaciones( Detalle)

   Values ( 'Articulo 234-e venta')

 

   --- Obtención del @Id ---

   Declare @Id integer

   Select @Id = @@identity

 

   -- Si todo ok Commiteo Transacción/else Rollback  --

   If @@ERROR = 0

     Commit Transaction

   Else

     Rollback Transaction

Pero está la solución de incluir en una transacción el proceso de insertado en la tabla cuore es una alternativa exenta de eventuales inconvenientes?

Pues no … veamos dos posibles consecuencias adicionales:


Ø Huecos en la Correlatividad de IDs

En caso de ROLLBACK,  el id correspondiente a la transacción “rolbackeada” se perderá,  generando un hueco en la correlatividad de IDs que debemos solucionar con un costo adicional asociado a ello…

 

Ø Problemas de Contención

Adicionalmente y volviendo a las horas pico donde varios hilos intentan insertar registros en nuestra tabla de Operaciones, encontraremos que dichos hilos se serializan esperando por el cierre de transacción precedente. Por lo tanto, los lockeos se prolongan más de lo tolerable,  pudiendo producirse bloqueos y hasta deadlocks….

 

Utilidad

Diría que este método es el más útil y sencillo de implementar en escenarios donde la concurrencia es escasa a moderada y los volúmenes de la tablas no son tan importantes. Siempre teniendo en cuenta las precauciones que hemos descripto a la largo de la explicación del mismo.

 

METODO 2.    Generación de ID por Cálculo

Se trata de una solución que no recomiendo en absoluto pero que he visto en muchos sistemas aún productivos.

El método  consiste en tomar el máximo ID de la tabla y sumarle un dígito… para así generar el ID para el nuevo registro.

Ejemplo:

 

--  1) Creación Tabla --

CREATE TABLE Operaciones

(Id int NOT NULL,

 Detalle varchar(50) NULL)

 

-- 2) Seteo de la Pk al campo "Id" --

ALTER TABLE [dbo].[Operaciones]

ADD  CONSTRAINT [PK_Operaciones] PRIMARY KEY CLUSTERED

([Id] ASC)

GO

 

 -- 3) Cálculo del @id e Inserción en Tabla  --

  Begin Transaction

     Declare @Id integer

    ---  Obtención de id por cálculo --

   Select  @Id =  coalesce (max(id),0) + 1 from Operaciones

    ---  Inserción de registro en Tabla  ---

   Insert into Operaciones(id,  Detalle)

   values (@Id,  'Articulo 234-e venta')


Ventajas

No encuentro ventaja alguna  más allá de cierta facilidad en el grado de implementación y comprensión  de la rutina en programadores menos experimentados.


Desventajas

Ø Alto grado de contención / Bloqueos / Deadlocks

En términos de performance esta elección es por lejos la peor debido a la baja performance que resulta de la utilización constante de la función MAX().

A esto se le debe sumar que la contención complementaria por estar esta solución embebida en una transacción en lo referente a la obtención del id y su inserción. 

 

Utilidad

---

 

METODO 3.   Generación de ID mediante un GUID  (Global Unique Id)

Ante todo recordemos que un GUID es un tipo de datos binario de 16 bytes globalmente único en tablas, bases de datos y servidores.

EL método consiste en generar el ID de tipo uniqueidentifier e insertarlo como nuevo Id de la tabla.

Ejemplo:

 

--  1) Creación de Tabla --

Create Table Operaciones

(Id uniqueidentifier NOT NULL,

 Detalle varchar(50) NULL)

 

-- 2) Obtención @Id por medio de función --

Declare @Id uniqueidentifier

Select  @id =  NEWID()

 

-- 3) Inserción del registro en la tabla --

Insert into Operaciones(id,  Detalle)

values (@id,  'Articulo 234-e venta')

 

(*) Existe una opción de generar el id utilizando como valor por default el NEWID() o NEWSEQUENTIALID() , pero  no es considerada en este contexto en el cual debemos recuperar el id recién insertado para poder seguir insertando el mismo en otras tablas.

 

Ventajas

Se trata de un  método que nos garantiza la unicidad del ID generado para todos los objetos de nuestra base de datos.

Al ser el @Id único, no hace falta embeber el proceso de inserción dentro de una transacción evitando desde ese lado contención.

 

Desventajas

Ø Tipo de Datos poco performante y no apto para ciertas lógicas de negocio

El tipo de datos uniqueidentifier con sus 16 bytes definitivamente es un problema de performance en puerta para la buena performance de un sistema fuertemente transaccional.

Por las características típicas de este tipo de datos es poco amigable con la mayoría de las lógicas de negocio, al menos con las que uno está acostumbrado a ver.

Al tratarse de datos “amplios” necesitaremos mayor almacenamiento y mayores recursos. Recordemos también que cuando más “pesado” sea un registro menos de este tipo de registros cabrán en memoria..con las consecuencias de performance…

 

Utilidad

Es el único método que  garantiza unicidad de código en toda la base de datos.

 

 

METODO 4.   Generación de ID “on the fly”

Se trata de un método similar al anterior en el cual se obtiene un ID que, se pretende, sea único por su concepción. Se calcula a partir del uso de funciones del sistema.

Se trabaja para ello con una variable de tipo Numeric  amplia la cual permite jugar en este caso hasta con los microsegundos de la fecha del sistema para evitar de ese modo posibles duplicaciones.

 

Ejemplo:

--  1) Creación Tabla  --

Create Table Operaciones

(Id NUMERIC(19,0) NOT NULL,

 Detalle varchar(50) NULL)


-- 2) Seteo de la Pk al campo "Id"--

Alter Table [dbo].[Operaciones]

Add  Constraint [PK_Operaciones] Primary Key Clustered

([Id] ASC)

GO

 --3) Obtención de @Id “On the Fly” -–

Declare @id numeric(19,0)

Select @id = (select cast((((datediff(SECOND,'1970-01-01', getdate())

+datepart(ms,getdate())/1000.0)*1000)+datepart(mcs,getdate())/1000.2)*1000

as numeric(16,0)))


-- 4) Inserción del registro en la tabla --

Insert into Operaciones(id,  Detalle)

Values (@id,  'Articulo 234-e venta')

 

Ventajas

Es un método que obtiene muy buena performance en la obtención del ID y permite asegurar la unicidad del mismo. Recordemos que al trabajar con precisión de microsegundos es imposible que se repita el Id .

No hace falta embeber la inserción dentro de una transacción hecho que permite asegurar la buena performance.


Desventajas

Ø Tipo de Datos poco performante y no apto para ciertas lógicas de negocio

El tipo de datos tiene que tener una importante amplitud para poder asegurar la unicidad  creciendo en bytes… La performance, si bien al trabajar con un dato de tipo numérico será marcadamente mejor que la del caso anterior, no será igual de buena que trabajando con tipo de datos más pequeños.

Adicionalmente la lógica de negocios tendría que permitirnos trabajar con datos de esta amplitud.

Por último al igual que en el método anterior tendremos registros más pesados y “grandes”, malgastando recursos y reduciendo el espacio disponible en RAM.

 

Utilidad

Es un método válido para entornos con alta carga transaccional teniendo en cuenta sus “desventajas”

 

 

METODO 5.   Generación de ID en  “Dummy Table”

Este método es a mi juicio el más apto para soportar grandes cargas transaccionales con la mejor performance y sin duplición IDs

Se crea para la obtención del ID una tabla “dummy”, la cual tendrá como único objetivo el brindar el último ID secuencial.

 Esta tabla permancerá siempre sin registros. Simplemente se inserta un registro para “ mover” el identity , pasarlo a la variable @Id y borrarlo.

Ejemplo:

 

--  1) Creación Tabla “Operaciones” --

       Create Table Operaciones

       (Id int NOT NULL,

        Detalle varchar(50) NULL)

 -- 2) Seteo de la Pk al campo "Id" --

       Alter Table [dbo].[Operaciones]

       Add  Constraint [PK_Operaciones] Primary Key Clustered

       ([Id] ASC)

       GO

 -- 3) Creación de la "Dummy Table" --

       CREATE TABLE Operaciones_kEYTABLE

       (Id int NOT NULL IDENTITY(1, 1))

 -- 4) Obtención del ID desde la tabla "Dummy Table" --

       Declare @Id int   

       Begin Transaction

          Insert Operaciones_kEYTABLE DEFAULT VALUES;

          Select @Id = SCOPE_IDENTITY();

          Delete From Operaciones_kEYTABLE

       Commit Transaction

 -- 5) Inserción de un registro nuevo en la tabla “Operaciones”--

       Insert into Operaciones(id,  Detalle)

       Values (@id,  'Articulo 234-e venta')

 

Ventajas

Se trata de un  método que nos garantiza la unicidad del ID generado y la mejor performance en el proceso de inserción de la tabla “cuore”.

Al estar embebido en la transacción sólo la obtención del id en la tabla dummy no tiene casi contención.

Al ser la tabla dummy una tabla sin registros la velocidad de obención del Id es la mejor de todos los métodos tratados en este artículo.

 

Desventajas

--

Utilidad

Es el métedo más apropiado para su implementación en ambientes como el descripto al comienzo de este post.


METODO 6.   Generación de ID por medio de Sequence Object

Se trata de un método válido cuando se necesite mantener la secuencialidad de los valores de un id en forma estricta acorde a reglas de negocio. 

Se obtiene un valor para una columna Id a partir un objeto introducido en Sql Server 2012 que permite gestionar el mismo. Con alguna similaridad con la columna "identity" se diferencia de la misma en que el objeto identity no está "atado" a una tabla y por lo tanto tiene sus ventajas y desventajas que luego detallaremos.

No está dentro del scope de este artículo el explicar paso a paso su sintaxys sin embargo veremos un ejemplo extraído de la página https://www.sqlshack.com/sequence-objects-in-sql-server/ - página de un querido colega que desde ya recomiendo -.

Manos a la obra, lo primero que debemos hacer es crear un objeto sequence al que llamaremos "IdCounter", dentro de la base de datos en la que precisemos de id único y secuencial:

Use Padron

CREATE SEQUENCE [dbo].[IdCounter]

AS INT

START WITH 1

INCREMENT BY 1


Ahora procedereos a insertar registros en la tabla Students utilizando el Id provisto por el objeto creado en el paso anterior de la siguiente forma:

1
NEXT VALUE FOR [dbo].[IdCounter]

USE School
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter], 'Sally', 20 )
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter],'Edward', 36 )
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter],'Jon', 35)
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter],'Scot', 41 )
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter],'Ben', 35 )

Luego haremos un select de la tabla y veremos como se han insertado correctamente los ids ssecuenciales generados a partir del objeto "IdCounter"

SELECT * FROM Students


Ventajas

Es un método que BIEN IMPLEMENTADO garantiza la secuencialidad de un Id , algo imposible de ser garantizado con un campo identity.


Desventajas

- Al ser el valor de un sequence updeteable, es una enorme tentación de hacerlo para alguien que tienen los privilegios dentro de la base. Luego vienen los reclamos del tipo"no se qué pasó se perdió la correlatividad"...

- Ante casos de apagados repentinos del motor, y dado que la mayoría suele hacer uso del sequence cacheado, el mismo se pierde. Se vacía el caché y se pierde..

- El sequence, al igual que el identity, no es garantía de unicidad. Por definición no
garantiza la misma y me ha ocurrido de ver duplicaciones en procesos cíclicos de obtención de id secuencial o en updates complejos.

- No se suele usar con criterio el valor default del sequence. Cuando tu vas revisar te sueles encontrar con valores por defecto definidos como integer o biginteger cuando en verdad con un small integer hubiera sido suficiente . Esto trae problemas de peformance y espacio mal utilizado.

Utilidad

Si lo utilizamos con criterio, es decir evitando las malas prácticas que devienen en sus eventuales desventajas, es un excelente método a la hora de cumplir con estrictas reglas de negocio que exigen secuencialidad sin fisuras en el  id de una tabla (ejemplo nº factura).


Amigos, es todo por ahora. Espero como siempre vuestras preguntas, apuntes y todo aquello que nos enriquezca.

Sigamos en contacto, saludos desde Argentina.