start - rollback mysql

MySQL AUTO_INCREMENT no ROLLBACK

Question

Estoy usando el campo AUTO_INCREMENT de MySQL e InnoDB para admitir transacciones. Me di cuenta cuando revertir 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?

Answer 1

¿Por qué te importa si se revierte? Los campos clave AUTO_INCREMENT no deben tener ningún significado, por lo que no debería importar qué valor se use.

Si tiene información que intenta preservar, tal vez se necesite otra columna que no sea clave.

Answer 2

La respuesta concreta a este dilema específico (que también tuve) es la siguiente:

1) Cree una tabla que contenga diferentes contadores para diferentes documentos (facturas, recibos, RMA, etc.); Inserte un registro para cada uno de sus documentos y agregue el contador inicial a 0.

2) Antes de crear un nuevo documento, haga lo siguiente (para facturas, por ejemplo):

UPDATE document_counters SET counter = LAST_INSERT_ID(counter + 1) where type = ''invoice''

3) Obtenga el último valor que acaba de actualizar, de esta forma:

SELECT LAST_INSERT_ID()

o simplemente use su función PHP (o lo que sea) mysql_insert_id () para obtener lo mismo

4) Inserte su nuevo registro junto con la identificación primaria que acaba de obtener de la base de datos. Esto anulará el índice de incremento automático actual y se asegurará de que no haya espacios de identificación entre sus registros.

Todo esto tiene que ser envuelto dentro de una transacción, por supuesto. La belleza de este método es que, cuando revierte una transacción, su declaración de ACTUALIZACIÓN del Paso 2 se retrotraerá, y el contador ya no cambiará. Otras transacciones concurrentes se bloquearán hasta que la primera transacción se haya comprometido o revertido, por lo que no tendrán acceso al contador anterior O a uno nuevo, hasta que todas las demás transacciones se finalicen primero.

Answer 3

No puede funcionar de esa manera. Considerar:

• programa uno, abre una transacción e inserta en una tabla FOO que tiene una clave primaria autoinc (arbitrariamente, decimos que obtiene 557 para su valor clave).
• Programe dos inicios, abre una transacción e inserta en la tabla FOO obteniendo 558.
• Programe dos inserciones en la tabla BAR que tiene una columna que es una clave foránea para FOO. Entonces ahora el 558 está ubicado tanto en FOO como en BAR.
• El programa dos ahora se compromete.
• El programa tres comienza y genera un informe de la tabla FOO. El registro 558 está impreso.
• 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 teclas principales mayores que 557? ¿Cómo arregla BAR? ¿Cómo se borra el 558 impreso en el programa de informe de tres resultados?

Los números de secuencia de Oracle también son independientes de las transacciones por el mismo motivo.

Si puede resolver este problema en tiempo constante, estoy seguro de que puede ganar mucho dinero en el campo de la base de datos.

Ahora, si tiene un requisito de que su campo de incremento automático nunca tenga espacios vacíos (para fines de auditoría, por ejemplo). Entonces no puede deshacer sus transacciones. En su lugar, debe tener un indicador de estado en sus registros. En la primera inserción, el estado del registro es "Incompleto", luego comienza la transacción, hace su trabajo y actualiza el estado para "competir" (o lo que sea que necesite). Luego, cuando te comprometes, el registro es en vivo. Si la transacción retrocede, el registro incompleto todavía está allí para la auditoría. Esto le causará muchos otros dolores de cabeza, pero es una forma de lidiar con los registros de auditoría.

Answer 4

No sé de ninguna manera de hacer eso. De acuerdo con la documentación de MySQL , este es el comportamiento esperado y sucederá con todos los modos de bloqueo innodb_autoinc_lock_mode . El texto específico es:

En todos los modos de bloqueo (0, 1 y 2), si una transacción que genera valores de incremento automático retrocede, esos valores de incremento automático se "pierden". Una vez que se genera un valor para una columna de incremento automático, no se puede revertido, independientemente de si se completa o no la instrucción "INSERT-like" y si la transacción que contiene se revierte o no. Tales valores perdidos no son reutilizados. Por lo tanto, puede haber lagunas en los valores almacenados en una columna AUTO_INCREMENT de una tabla.

Answer 5

Permítanme señalar algo muy importante:

Nunca debe depender de las características numéricas de las claves autogeneradas.

Es decir, además de compararlos para igualdad (=) o desigualdad (<>), no debe hacer nada más. Sin operadores relacionales (<,>), sin ordenar por índices, etc. Si necesita ordenar por "fecha agregada", tenga una columna de "fecha agregada".

Trátelos 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, sí, pero entiendes mi punto).

Si se atiene a esta regla, las brechas en la continuidad de los índices autogenerados no causarán problemas.

Answer 6

SOLUCIÓN:

Usemos ''tbl_test'' como una tabla de ejemplo, y supongamos que el campo ''Id'' tiene el atributo AUTO_INCREMENT

CREATE TABLE tbl_test (
Id int NOT NULL AUTO_INCREMENT ,
Name varchar(255) NULL ,
PRIMARY KEY (`Id`)
)
;

Supongamos que la tabla tiene miles o más filas ya insertadas y que ya no quiere usar AUTO_INCREMENT; porque cuando restituye una transacción, el campo ''Id'' siempre agrega +1 al valor AUTO_INCREMENT. Para evitar eso, podrías hacer esto:

• Vamos a eliminar el valor AUTO_INCREMENT de la columna ''Id'' (esto no eliminará las filas insertadas):

ALTER TABLE tbl_test MODIFY COLUMN Id int(11) NOT NULL FIRST;

• Finalmente, creamos un desencadenante BEFORE INSERT para generar un valor ''Id'' automáticamente. Pero el uso de esta forma no afectará su valor Id, incluso si revierte cualquier transacción.

CREATE TRIGGER trg_tbl_test_1 BEFORE INSERT ON tbl_test FOR EACH ROW BEGIN SET NEW.Id= COALESCE((SELECT MAX(Id) FROM tbl_test),0) + 1; END;

¡Eso es! ¡Terminaste!

De nada.

Answer 7

Si establece auto_increment en 1 después de una reversión o eliminación, en la próxima inserción, MySQL verá que 1 ya está utilizado y obtendrá el valor MAX() y le agregará 1.

Esto asegurará que si se borra la fila con el último valor (o se revierte la inserción), se reutilizará.

Para establecer auto_increment en 1, haz algo como esto:

ALTER TABLE tbl auto_increment = 1

Esto no es tan eficiente como simplemente continuar con el siguiente número porque MAX() puede ser costoso, pero si elimina / revierte con poca frecuencia y está obsesionado con reutilizar el valor más alto, entonces este es un enfoque realista.

Tenga en cuenta que esto no evita que se eliminen los espacios vacíos de los registros en el medio o si debe ocurrir otro inserto antes de volver a establecer auto_increment en 1.

Answer 8

Si necesita tener los identificadores asignados en orden numérico sin espacios vacíos, entonces no puede usar una columna de autoincremento. Tendrá que definir una columna de enteros estándar y usar un procedimiento almacenado que calcule el siguiente número en la secuencia de inserción e inserte el registro dentro de una transacción. Si la inserción falla, la próxima vez que se llame al procedimiento recalculará la próxima identificación.

Habiendo dicho eso, es una mala idea confiar en que los identificadores estén en un orden particular sin lagunas. Si necesita conservar el orden, probablemente debería marcar el tiempo en la fila al insertar (y posiblemente en la actualización).

Answer 9

Tenía un cliente que necesitaba la identificación para volver a colocarla en una tabla de facturas, donde el pedido debe ser consecutivo

Mi solución en MySQL fue eliminar el INCREMENTO AUTOMÁTICO y extraer el último Id de la tabla, agregar uno (+1) y luego insertarlo manualmente.

Si la tabla se llama "Tabla A" y la columna de incremento automático es "Id"

INSERT INTO TableA (Id, Col2, Col3, Col4, ...)
VALUES (
(SELECT Id FROM TableA t ORDER BY t.Id DESC LIMIT 1)+1,
Col2_Val, Col3_Val, Col4_Val, ...)

Answer 10

$masterConn = mysql_connect("localhost", "root", '''');
mysql_select_db("sample", $masterConn);
for($i=1; $i<=10; $i++) {
mysql_query("START TRANSACTION",$masterConn);
$qry_insert = "INSERT INTO `customer` (id, `a`, `b`) VALUES (NULL, ''$i'', ''a'')";
mysql_query($qry_insert,$masterConn);
if($i%2==1) mysql_query("COMMIT",$masterConn);
else mysql_query("ROLLBACK",$masterConn);
mysql_query("ALTER TABLE customer auto_increment = 1",$masterConn);
}
echo "Done";

Answer 11

INSERT INTO prueba(id)
VALUES (
(SELECT IFNULL( MAX( id ) , 0 )+1 FROM prueba target))

Si la tabla no contiene valores o cero filas

agregar destino para el error tipo mysql actualizar FROM en SELECT