3 replies to this topic

[williamevanl]

I have a process (actually a number) of processes that need to be running at the same time and read from and write to the same tables. As an example I have a process that is procedurally figuring out about a million records that need to be inserted into a table. While this is running I need to be able to select and insert into this table with another process.

Currently the first process just locks the entire table OR deadlocks the second process. What can I do about this?

:thumbup::thumbup::thumbup:

[gregwarner]

Unless you're inserting a whole bunch of rows at once, (that is to say, if you're trying to insert a row, then perform some calculations and insert the next row, and so on), you might try locking the table only long enough to insert your one record, then release the lock while you're performing your calculations for the next insert. That way, your other process will have a chance to jump in there and acquire the lock once in a while.

[lespauled]

You have processes that are inserting a million records on a constant basis? Can it be done off-hours?

In any case, you should make sure that your select statements are not locking the DB. For instance, some dabases have a NOLOCK clause that is added to sql statements.

[williamevanl]

Thanks for the response guys, actually we deal with Millions of elements repeatedly (and actually 4X times that at one step) Things are actually going on round the clock so there isn't really any downtime. So in the beginning its just a matter of creating these millions of things procedurally and inserting them. Following that we these are processed in a number of additional ways, these things are also being broken up but its just turning into a nightmare of one thing locking another.

Our process is already broken up into 4 minute cycles but at this rate we end up with 3.5 hour of straight processing just to get the records in the database. Realistically we need to be able to be able to do other things while this is going. We have certainly tried locking hints but with that number of inserts going into a table the table is completely locked and everything else just has to wait.

I guess I figured this was a common problem with a common solution. (also our selects are not locking anything but they are of course being locked by inserts)