Respuesta.

La actualización masiva de datos en varias tablas relacionadas es una tarea clásica en la programación industrial en SQL. Con el desarrollo de aplicaciones comerciales, surgió la necesidad de actualizar grandes volúmenes de datos al mismo tiempo, garantizando su consistencia. Históricamente, se han utilizado escenarios cíclicos, lo que resulta en un bajo rendimiento y prolongados bloqueos. Posteriormente, se introdujeron operadores DML avanzados (como MERGE), construcciones de transacciones y enfoques con tablas de staging.

El problema radica en que la actualización de datos afecta a múltiples tablas con relaciones (por ejemplo, pedidos y detalles del pedido), lo que puede dar lugar a 'filas huérfanas', pérdida de rendimiento debido a bloqueos y carga impredecible en la base de datos.

La solución se basa en el uso de transacciones atómicas, operaciones UPDATE/DELETE/MERGE con condiciones JOIN, así como el procesamiento por lotes de datos. Una buena práctica es retrasar los cambios agregados en tablas de staging temporales y luego aplicarlos en lotes a través de una transacción. Ejemplo para SQL Server utilizando MERGE:

BEGIN TRANSACTION;

-- Ejemplo de actualización masiva de la tabla principal y tabla relacionada utilizando MERGE
MERGE INTO orders AS tgt
USING temp_order_updates AS src
  ON tgt.id = src.id
WHEN MATCHED THEN
  UPDATE SET tgt.status = src.status, tgt.updated_at = src.updated_at;

MERGE INTO order_details AS tgt
USING temp_detail_updates AS src
  ON tgt.order_id = src.order_id AND tgt.sku = src.sku
WHEN MATCHED THEN
  UPDATE SET tgt.price = src.price, tgt.qty = src.qty;

COMMIT;

Características clave:

Aislamiento de operaciones en una sola transacción: no hay desincronizaciones intermedias.
Uso de tablas de staging para preparar datos modificables.
Aplicación de operaciones por lotes para reducir bloqueos y optimizar la carga.

Preguntas engañosas.

¿Se puede simplemente realizar una actualización en la tabla principal y luego por separado en las relacionadas, sin transacciones, si los requisitos de velocidad son estrictos?

Las actualizaciones individuales fuera de una transacción generan una severa desincronización de datos en caso de error en cualquiera de las etapas: por ejemplo, si los pedidos se actualizan, pero no los detalles, se rompe la lógica. En las bases de datos modernas, el uso de transacciones casi no aumenta los costos en el procesamiento por lotes.

¿Se reducirá el rendimiento si se realiza una gran actualización con una subconsulta? ¿Puede esto conducir a bloqueos?

Sí, las actualizaciones monolíticas en grandes tablas pueden llevar a la escalación de bloqueos, bloqueos de tabla y paradas de otros usuarios. Es mejor dividir el procesamiento en lotes con una limitación a través de WHERE ... AND rownum/id/limit.

Ejemplo de lote:

UPDATE orders
SET status = 'closed'
WHERE status = 'pending' AND id BETWEEN 100000 AND 199999;

¿MERGE garantiza atomicidad y el orden correcto de procesamiento de las tablas relacionadas?

No, MERGE opera dentro de una sola tabla. Para actualizar tablas relacionadas se requiere un MERGE o UPDATE separado, y es imprescindible colocar ambas acciones dentro de una misma transacción.

Errores comunes y anti-patrones

La falta de transacciones en cambios masivos, lo que lleva a la desincronización de datos.
Grandes UPDATE/DELETE individuales sobre grandes conjuntos sin LIMIT/BATCH: bloqueos y paradas.
Orden incorrecto de operaciones (por ejemplo, actualizar primero los detalles y luego la tabla principal).

Ejemplo de la vida real

Caso negativo

La empresa actualizaba el estado ('Completado') en un millón de pedidos con consultas separadas fuera de la transacción: primero orders, después — detalles order_details. Bajo carga, el servidor "fallaba"; en caso de error, los detalles quedaban con el estado 'abierto'. Pros:

Fácil de implementar.
Código mínimo.

Contras:

Desincronización de datos y dificultad en la depuración posterior.
Dificultades en la reversión.

Caso positivo

Se implementaron tablas de staging y procesamiento grupal dentro de una transacción. Primero, todos los cambios se calculaban y se almacenaban en tablas temporales, luego se actualizaban por lotes ambas tablas principales. En caso de error, reversión completa. Pros:

Garantía de consistencia e integridad de los datos.
Facilidad de control y reversión.