Is there a way to not to lock the entire table in a large number of records updated by UPDATE SQL.

For example, a table with 3 million records,
and i want to update a field:

update report
set reported='1'
where branch='00001'

the updated records are about 300.000 (i have index on branch)

and the entire table is locked it during the process, until the update finish.


i would appreciate your comments.

good day.

Jorge,

<< Is there a way to not to lock the entire table in a large number of records updated by UPDATE SQL. >>

Not at this time, no.  However, if I removed the EDB table locking completely, then the performance of the updates would be pretty poor and, overall, would result in quite a bit more locking/unlocking.  The way that it works is that EDB tunes the "transaction" size for the updates to be in-line with the available buffer size for the session/table, and batch updates the rows while using as much of that buffer as possible.  This could probably be change to accommodate a middle ground in terms of locks/performance.

Is this update happening in production ?

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<Is this update happening in production ?>>

not so often, not all the time, the solution will be do those updates after ours, where nobody is using those specific tables, i understand your point, it keeps integrity too, in that way the clients are reading the current updated rows always.

thanks for your help.

For us comming from other DB Systems, we have to change our mind in some things about EDB, but like i said before, i like the simplicity, but at the same time, the power of your EDB. And i like the support a lot! and the best of all, is DELPHI INTEGRATED.

Jorge.

Jorge Ortiz wrote:

> the solution will be do those updates after hours

FWIW, I had an issue like this, in that I had to do some daily maintenance, and I didn't like the short pause that it caused. So I just changed the code to get the list of rows to be deleted, and then did them one at a time. The lock still happens, but it is so much shorter and the other threads were now unaffected.

Obviously it is much slower, and may not be totally suitable, but it might help in a lot of situations. And only worth doing when you know you have a specific problem, not just generally.

--

Matthew Jones

Matthew


The only thing you missed was - run it in a background thread

Roy Lambert

Roy Lambert wrote:

> The only thing you missed was - run it in a background thread

True - all my operations are in threads, so there is no "foreground" thread. I should remember that when describing things!

--

Matthew Jones

Matthew


>True - all my operations are in threads, so there is no "foreground" thread. I should remember that when describing things!

I hope you missed out the word "substantial" from that statement

Roy

Roy Lambert wrote:

> > True - all my operations are in threads, so there is no "foreground" thread. I should remember that when describing things!
>
> I hope you missed out the word "substantial" from that statement

No, i did not. Pretty much everything is services nowadays, so the initial thread disappears and leaves my control thread in charge. That then creates all the worker threads.

Now, I do have a test harness that will have a main thread, but I ignore that completely. 8-)

--

Matthew Jones

Matthew


Interesting.

Roy Lambert