Long transactions not comitting fully.

[Ruairi]

I am having problems with long transactions not comitting fully. I am working on an accounting system for sawmills. One of the functions of this system is to import data that is sent in the form of text files from scaling bureaus. These files contain information about loads and logs (each log record refers back to a load record via a foreign key) that the mill recieves from vendors. There are conversion tables in the system to relate text information (trucker names, logger names, species, etc..) to primary key values of the related tables. There can be errors (the system cannot find a conversion for a piece of information and does not have a default available) or warinings (the system does not have a conversion, but can set the field to a default value) during this import. The loads and logs are added to the DB inside a transaction. In the case of an Error the entire transaction is automatically rolled back. If there are warnings the user can choose to rool back the transaction or commit itbased on a report of the warnings. In the case of warnings but no errors there is always enough data to commit the transaction (all the foreign key values in the load and log tables are related to a valid record, although some may just be a default). The problem is that SQL Server seems to lose some of the transaction. I will issue the commit command and not all the loads will be in the database. I know the loads were all imported during the transaction because as they are imported i run a stored procedure on each load to set it's cost based on prices in the system. I am reading the connection's error object after each run of the Stored Procedure and printing any errors to the error/warning report so i know there are no errors occuring at the server as these loads are added. I started with a bureau file containing 88 loads. Whenever i would go through the process and chosse to commit the loads only the last 15 would be in the database. I then cut and pasted these loads into 6 seperate text files. The fist had 14 loads in it and all 14 were imported. The next had 19 loads but after comitting it to the database only 17 of these showed up. I cut the remaining 2 out of the text file and pasted them into their own file. After processing this file the 2 loads showed up. That tells me that there is not actually a problem with the data or the import would have failed when these 2 loads were imported alone. Am i missing some setting or settings on the server that would allow these transactions to fully commit? Any help would be greatly appreciated. I have been at this a long time trying to figure out what's wrong but everything i see points to SQL Server 'losing' part of the transactions. TIA Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[tlbroadbent]

You refer to loads and logs. Do these come in separate files? You indicate that the logs relate to the loads via a foreign key. Is there a one-to-one or one-to-many relationship?

You also say that SQL fails to import some of the loads. Does it also fail to import some of the logs or all of the logs related to the loads not imported?

Is it possible that something was missing in one or more tables during the 1st import and that prevented the record load. And then perhaps the necessary piece was added during the 1st import which is the reason the 2nd import succeeded?

This is really a stretch. Is there any additional info that could help narrow the problem? Terry

X-) "Life would be easier if I had the source code." -Anonymous

 

[Ruairi]

Terry,
Thanks for your response. All logs are imported for the loads that are imported. No logs are imported for loads that aren't imported. There is a one to many relationship between loads and logs. Think of a load as a logging truck driving into the mill yard. There can be many logs in that load. They don't have to worry about weighing or measuring the logs on the truck because they alreay received this text file that tells them who sold them those logs, who logged them, the species, grade, length, diameter, etc.. of each log. A log cannot exist in the database without refrencing a valid load. There is one text file, laid out like this

1
4
6
7
7
7
7
the 1, 4, and 6 lines are information for the load, each 7 line has information for one log.
There is no possibility that any of the data changed between the import attempts, with the exception of the loads and logs that imported successfully. As i said, i am reading the connection's error object throughout this entire process. If there were errors returned during the process they would be printed on the error report. The only information i can think of that i havent already stated is that the identity column is incrementing the correct number of times. For example if the last value of 'rec' (the identity in the loads table) was 219,000 and i process 15 loads only the last one may show up in the database. That loads 'rec' value will be 219,015. That tells me that each load was added to the database during the trasaction. One other thing i noticed is that if i monitor the number of locks in performance monitor it will grow at a rate approximately 100 locks for each load that is being imported. At some seemingly arbitrary point during the transaction the locks will drop to 0, then start building again. This drop seems to coincide with the part of the transaction that is 'lost'. I have checked and double checked and the only place i am issuing a rollback or commit is at the very end of the process, outisde of the loop. I also ran it with breakpoints at the commit and rollback. They were never reached until the entire process was finished. In my tests the number of locks have never been anywhere near the configured limit. Thanks again for your input, i hope for some more. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com