wintergreen has asked for the wisdom of the Perl Monks concerning the following question:
I have a problem with a SQL Server 7 database completely locking up when doing a write using the DBD::ODBC module.
The database needs to be updated on a periodic basis from flat text files. These files are parsed and after some data munging their information is merged into the database. To do the merging, I make two connections to the database: one to read the data currently in the database for comparison to the flat file data, and one to do a database write (update, delete, or insert). I make two connections to the same database because I need the cursor open during a select while I'm updating the database. Both connections read and write to the same two tables.
Sometimes when running my script, the database completely locks up such that no connections can be made to any database running on the server, even through the MMC administration tool. The lockups occur sporadically and only at a database write statement.
I can't figure out why the database locks up. I'm not sure if the DBD::ODBC module or ODBC itself was meant to handle having two connections being used in the manner I described above. Does anyone have any suggestions on fixing or diagnosing this problem?