问：
两周前我遇到了以下问题，考虑到性能问题、数据完整性，我不知道如何处理它。
我所做的是：
我想将数据从XML 文件迁移到数据库中的类似表。
例如：
-
我有两个节点（XML 文件）：
courses 、 teachers
-
两个表（数据库）
课程、教师。
我允许用户将 XML 文件上传到我的服务器上的文件夹，然后我开始读取 XML 文件并将数据插入到我的数据库中。
问题是：
如果在插入操作过程中发生故障，我想删除所有表中所有插入的记录。（或回滚）。
我开始考虑 transaction ，每个实体的插入将通过事务执行，但我面临两个问题：
-
我应该将所有实体的所有插入放在一个事务中还是
事务中一个实体一个实体？（所有实体数据必须全部插入或根本不插入）对于每个上传者。
-
当我有大量记录时（1500 条记录）。以下
出现异常：
此 IfxTransaction 已完成；它不再可用，不
一个修复它。
-
我的团队领导告诉我不要使用交易，因为它会
锁定表并且许多用户使用这些表。他想要其他的
机制。
请我想要一个解决我的问题的方法（详细解释），如何处理这种情况并维护性能问题以及数据的完整性和一致性。
Q:
I face the following problem two weeks ago , and i don't know how to handle it taking the performance issues , the data integrity in consideration.
What I do is:
I wanna to migrate the data from the XML file to its similar tables in my database.
for example :
I allow the user to upload the XML file to a folder on my server and i begin to read the XML file and insert the data into my database .
The problem is:
if some failure happens during the insertion operation , i wanna to delete all the inserted records in all tables.(or roll back).
I begin to think about transaction , the insertion of each entity will be performed through transaction but i face two problems:
-
Should i put all insertions of all entities in one transaction or
one entity by one in transaction?(all entities data must be all inserted or no insertion at all )for each uploader.
-
When i have huge number of records say(1500 record).the following
exception appear:
This IfxTransaction has completed; it is no longer usable,no
one fixes it.
-
My team leader told me not to use the transaction , because it will
lock the tables and many users use those tables. he wanna some other
mechanism .
please i wanna a solution to my problem(detailed explanation), How to handle this case and maintain the performance issues and the data integrity and consistency .
发布评论
评论（3）
我建议使用 SqlBulkCopy。您可以谷歌，或阅读这两篇文章：
I would suggest using SqlBulkCopy. You can google, or read these 2 articles:
如果我理解正确的话，你正在执行批量插入。为什么不使用 Spring Batch，它具有从最后一个故障点重新启动、重试、分块、数据分区等功能...
我知道您已经标记了 asp.net ，但是数据加载可以以独立于技术的方式进行并解耦方式。不是吗？
If I understand this correctly you are doing a batch insert. Why not use Spring Batch, which have facilities of Restart from last failure point , Retry , Chunking , Partitioning of data etc...
I am aware you have tagged asp.net , but loading of data can happen in a technology independent way and decoupled way. Isn't it?
这是我们在同一个问题中使用的一种机制，开始将数据保存在临时表中，如果插入临时表后没有异常，则运行一个存储过程将这些临时表的内容复制到真实表中，然后之后从临时表中删除*。
这样，您在使用事务机制时就不会锁定对表的访问。
还有另一种机制，但如果你使用它，你必须重新考虑所有数据库结构，它称为 CQRS（对于.NET，有一个称为 NCQRS 的 API）
Here is a mechanism that we used in the same issue, start saving the data in temporary tables , if there is no exeption after inserting in the temporary tables, run a stored procedures that copy the content of these temporary tables to the real tables, and after that delete * from temporary tables.
In this way you don't lock the access to tables when using transaction mecanism.
There is another mechanism but if you use it you have to rethink all the database structure, it is called CQRS ( for .NET there is an API called NCQRS)