Is there any limitations on the number of parallel XML Bulk Load (SQLXML 4.0)?

Question

Hi,

I have gone through various forums and MSDN. But MS nowhere ( not even in document - Guidelines and Limitations of XML Bulk Load (SQLXML 4.0)) talks about limitation of performing parallel bulk load of XML files using SQLXML4.0.

When I perform simultaneous XML bulk load operations, I get deadlocks during BulkLoad saying -
Transaction (Process ID 259) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I would like to know is there any limitations on the number of parallel/ simultaneous bulk load operation of SQLXML?

Thank in advance,
Jabs

---

jabs

Answer 1

Have a look through these two threads to see if it helps:

http://social.msdn.microsoft.com/Forums/en/sqlxml/thread/2fd70c71-d522-4b6d-b7cb-71b7075b3755
http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/932cd26c-53fc-49c0-b082-e7f5f05a9801


Otherwise, can you tell us a bit more about your table structure?  eg do you have a clustered index or triggers on the table?

Answer 2

When you are performing parallel bulk loads into same set of target tables, then turn off ForceTableLock.
If the set of tables in the 2 bulk loads is completely different then you should not face any issues.

Regards
Shiv

Answer 3

ForceTableLock is OFF.
The set of tables in the 2 bulk loads are same.

---

jabs

Answer 4

Jabs,

We do the same stuff. But we never faced any deadlock issues. We in general avoid bulk loading data in parallel, if the data goes into same set of tables.

How complex is your bulk load operation?
There is definetly a chance of dead lock happening if you have defined parent-child relationships which the 2 bulk load processes shares.

Regards
Shiv

Answer 5

Shiv,

Yes we do have parent-child relationships in our database schema.

One thing which I have observed is, on SQL 2005 with SQLXML 4.0, these dead locks errors are not seen. But on SQL 2000 with SQLXML4.0, we are seeing it. May be timing issue, or SQL 2000 has some limitation with SQLXML4.0.

I browsed alot but, did not find any recommendation/limitation/best practice stating if XML could be uploaded parallel or not.

Although, in SQLXML3.0 there were some issues with naming of temporary tables created during bulk load. And as per Irwin Dolobowsky of MS reply's in the following link, they were suppose to fix those issues in SQLXML4.0.

http://groups.google.co.nz/group/microsoft.public.sqlserver.xml/browse_thread/thread/106f2425a60a0db6

I am not sure if they really support simultaneous uploads.

Thanks and Regards,

---

jabs

Answer 6

Ok.

This issue might be specific to the loading part in Sql 2000. Not sure how record generation process happens in SQL XML 4.0 into Sql 2000.
Any pointers would be helpful.

Regards
Shiv