lockandrun

johnny_job
Posts: 6
Joined: Wed Oct 13, 2004 2:58 am

lockandrun

Post by johnny_job »

I tried to use lockandrun to run shell comand (tsql) for database re-creation. My code is like this: monitor.exe -c lockandrun -d "my_database_path" -c "tsql -i <path_to_sql_script>" -m WRITE , but it seems that it leads to the deadlock situation. Where I'm wrong?
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

lockandrun

Post by mark »

The purpose of lockandrun is to prevent database modifications for the duration. Then you're trying to modify it while it's locked. Bad plan. What are you trying to accomplish?
johnny_job
Posts: 6
Joined: Wed Oct 13, 2004 2:58 am

lockandrun

Post by johnny_job »

I simply need to lock database during it's purging (re-creation).
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

lockandrun

Post by mark »

Why? Just recreate it. Lockandrun is mainly intended for running non-database operations such as snapshots, backups etc.

That somewhat begs the question why you're purging and recreating regularly?
tony.malandain
Posts: 57
Joined: Sat Mar 27, 2004 6:39 pm

lockandrun

Post by tony.malandain »

In some situations, we may have to reinitialize the database and deleting all the rows takes way too much time (I'm assuming because the full text index needs to be refreshed).
The problem is that if another process is accessing the database while we attempts to recreate it, we end up with the following error the next time we query the table
000 Insufficient permissions on XXX in the function ipreparetree
000 SQL failed

Any idea what is going on?
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

lockandrun

Post by mark »

If you want to start over with a table you should just "drop" it and recreate it. Then load your data and create indices.

Regardless of method, when rebuilding a table from scratch the search will not work properly. You should either put some logic into the search to tell it when the table is offline. Or build to a new table or database and have the search use the "good" one whichever that is.
tony.malandain
Posts: 57
Joined: Sat Mar 27, 2004 6:39 pm

lockandrun

Post by tony.malandain »

This is actually what we do. Sorry for the confusion.

The script we run delete the indices and tables if they already exist and then recreate them.

The problem is, as I mentioned before, that doing this corrupts the database (Insufficient permissions on XXX), which is why we looked at lockandrun in the first place.
User avatar
mark
Site Admin
Posts: 5513
Joined: Tue Apr 25, 2000 6:56 pm

lockandrun

Post by mark »

No need separately drop indices. Dropping the table will automatically drop all if it's indices. "drop table" followed by "create table" should work fine. Maybe if you outline what you're doing and exactly where the error occurs in more detail we can spot the problem.
tony.malandain
Posts: 57
Joined: Sat Mar 27, 2004 6:39 pm

lockandrun

Post by tony.malandain »

We are accessing the Texis database from 2 differents tools.
The 1st too is an import tool that grabs office docs, do some preprocessing and add them into the DB using a vortex script (the vortex script may take several seconds to process during it is not always using the database).
The plain text version of the document is provided to Texis (ie, we don't use the anytoTx tool). The vortex script is adding the document into 2 tables: 1 is the 'PropertyStore' table which contains metadata about the document and the other one, 'FileStore', contains both the binary and plain text versions of the document.

The 2nd tool is a web service that allows management of the database.
As I mentioned earlier, the user may want to wipe out the content of the datatable. We do that by dropping the tables and recreating them. I wrote an sql script that does exactly this. It is executing using the tsql program.

Now, the administrator can decide to erase the database at any time, including when the import tool is running, which means that both the texis program and the tsql program may run at the same time. One trying to add rows to the tables while the other one is removing them.
The result I get is that the tables have been recreated and all the data is gone, but it also corrupts the database as I get these error messages after the 'purge'
000 2004-10-15 11:47:34 import:68: Insufficient permissions on FileStore in the function ipreparetree
000 2004-10-15 11:47:34 import:68: SQLExecute() failed with -1 in the function execntexis
000 2004-10-15 12:18:41 import:35: Insufficient permissions on PropertyStore in the function ipreparetree
000 2004-10-15 12:18:41 import:35: SQLExecute() failed with -1 in the function execntexis

How can we lock the database so that nobody else is trying to modify it?
Any idea?
tony.malandain
Posts: 57
Joined: Sat Mar 27, 2004 6:39 pm

lockandrun

Post by tony.malandain »

I forgot to mention.
The error sometimes occurs on 1 table or on the other (note the time on each error is different), which lead me to believe that it's a race condition issue where the texis import script attempts to write in a table while it is not fully operational.
If I manually shut down the importer and any texis program it spawn and then run the purge, it works fine. But unfortunately this is not something I can easily translate into a programming sequence.
Post Reply