MySQL AUTO_INCREMENT no retrocede
Estoy usando el campo AUTO_INCREMENT de MySQL e InnoDB para admitir transacciones. Me di cuenta de que cuando revertí la transacción, ¿el campo AUTO_INCREMENT no se revierte? Descubrí que fue diseñado de esta manera, pero ¿hay alguna solución para esto?
No puede funcionar de esa manera. Considerar:
- En el programa uno, abre una transacción y la inserta en una tabla FOO que tiene una clave primaria autoinc (arbitrariamente, decimos que obtiene 557 por su valor de clave).
- Se inicia el programa dos, abre una transacción y la inserta en la tabla FOO obteniendo 558.
- Programe dos inserciones en la tabla BAR que tiene una columna que es una clave externa para FOO. Ahora el 558 está ubicado tanto en FOO como en BAR.
- El programa dos ahora se compromete.
- El programa tres se inicia y genera un informe a partir de la tabla FOO. Se imprime el registro 558.
- Después de eso, el programa uno retrocede.
¿Cómo recupera la base de datos el valor 557? ¿Entra en FOO y disminuye todas las demás claves primarias mayores que 557? ¿Cómo soluciona BAR? ¿Cómo se borra el 558 impreso en la salida tres del programa de informes?
Los números de secuencia de Oracle también son independientes de las transacciones por la misma razón.
Si puede resolver este problema en un tiempo constante, estoy seguro de que podrá ganar mucho dinero en el campo de las bases de datos.
Ahora, si tiene el requisito de que su campo de incremento automático nunca tenga espacios (para fines de auditoría, por ejemplo). Entonces no podrá revertir sus transacciones. En su lugar, necesita tener un indicador de estado en sus registros. En la primera inserción, el estado del registro es "Incompleto", luego inicia la transacción, hace su trabajo y actualiza el estado para "competir" (o lo que necesite). Luego, cuando te comprometes, el registro está en vivo. Si la transacción se revierte, el registro incompleto todavía está allí para ser auditado. Esto le causará muchos otros dolores de cabeza, pero es una forma de abordar las pistas de auditoría.
Déjame señalar algo muy importante:
Nunca debes depender de las características numéricas de las claves generadas automáticamente.
Es decir, aparte de compararlos por igualdad (=) o desigualdad (<>), no debes hacer nada más. Sin operadores relacionales (<, >), sin clasificación por índices, etc. Si necesita ordenar por "fecha de adición", tenga una columna "fecha de adición".
Trátelas como manzanas y naranjas: ¿Tiene sentido preguntar si una manzana es lo mismo que una naranja? Sí. ¿Tiene sentido preguntar si una manzana es más grande que una naranja? No. (En realidad lo es, pero entiendes mi punto).
Si cumple con esta regla, las brechas en la continuidad de los índices generados automáticamente no causarán problemas.
