我有两个sql插入(例如表A和B中的示例),它们在事务中,因为我希望数据库保持一致,也就是说,A中的元组必须在B中具有引用.
在第二个插入中,我需要第一个插入的ID,但是直到对事务进行提交后,我才能获得该ID.
所以我被卡住了.我不想从事务中取出第一个插入,可能会发生第一个插入正常但第二个插入没问题的情况,这使我在数据库中处于不一致状态.
在这种情况下最佳做法是什么？
编辑：这是代码：
TransactionStatus txStatus = transactionManager.getTransaction(txDefinition);
try{
Integer aId = insertIntoA();
insertIntoB(aId);
}catch(){
transactionManager.rollback(txStatus);
throw new CustomException();
}
transactionManager.commit(txStatus);
我要指出的是,在提交事务之前,我不会获得aId,因此将null插入B中.
最佳答案
在MysqL上,您可以在insertIntoA中执行以下操作：
SELECT LAST_INSERT_ID()
…在您用于插入的同一连接上,假设它是您要查找的标识列值.
编辑：如果您正在这样做并且不起作用(根据您的评论),我将查看中间层以查看正在发生的情况. MysqL可以：
MysqL> create table A (id int(11) not null auto_increment,descr varchar(64),primary key (id));
Query OK,0 rows affected (0.13 sec)
MysqL> create table B (fk int(11) not null,descr varchar(64));
Query OK,0 rows affected (0.06 sec)
MysqL> start transaction;
Query OK,0 rows affected (0.00 sec)
MysqL> insert into A (descr) values ('Testing 1 2 3');
Query OK,1 row affected (0.00 sec)
MysqL> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.03 sec)
MysqL> insert into B (fk,descr) values (1,'Test complete');
Query OK,1 row affected (0.00 sec)
MysqL> commit;
Query OK,0 rows affected (0.03 sec)
MysqL> select * from A;
+----+---------------+
| id | descr |
+----+---------------+
| 1 | Testing 1 2 3 |
+----+---------------+
1 row in set (0.02 sec)
MysqL> select * from B;
+----+---------------+
| fk | descr |
+----+---------------+
| 1 | Test complete |
+----+---------------+
1 row in set (0.00 sec)
MysqL> start transaction;
Query OK,0 rows affected (0.00 sec)
MysqL> insert into A (descr) values ('Second test');
Query OK,1 row affected (0.01 sec)
MysqL> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)
MysqL> insert into B (fk,descr) values (2,'Second test complete');
Query OK,0 rows affected (0.08 sec)
MysqL> select * from A;
+----+---------------+
| id | descr |
+----+---------------+
| 1 | Testing 1 2 3 |
| 2 | Second test |
+----+---------------+
2 rows in set (0.02 sec)
MysqL> select * from B;
+----+----------------------+
| fk | descr |
+----+----------------------+
| 1 | Test complete |
| 2 | Second test complete |
+----+----------------------+
2 rows in set (0.00 sec)
MysqL> start transaction;
Query OK,0 rows affected (0.00 sec)
MysqL> insert into A (descr) values ('We''ll roll this one back.');
Query OK,1 row affected (0.00 sec)
MysqL> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
| 3 |
+------------------+
1 row in set (0.00 sec)
MysqL> insert into B (fk,descr) values (3,'Won''t see this one.');
Query OK,1 row affected (0.00 sec)
MysqL> select * from B;
+----+----------------------+
| fk | descr |
+----+----------------------+
| 1 | Test complete |
| 2 | Second test complete |
| 3 | Won't see this one. |
+----+----------------------+
3 rows in set (0.00 sec)
MysqL> rollback;
Query OK,0 rows affected (0.03 sec)
MysqL> select * from A;
+----+---------------+
| id | descr |
+----+---------------+
| 1 | Testing 1 2 3 |
| 2 | Second test |
+----+---------------+
2 rows in set (0.00 sec)
MysqL> select * from B;
+----+----------------------+
| fk | descr |
+----+----------------------+
| 1 | Test complete |
| 2 | Second test complete |
+----+----------------------+
2 rows in set (0.00 sec)
