Re: Update daily one row of a whole table

You could alter the table to use PAGE level locking, which will reduce the number of locks you need. Of course, that will have potentially detrimental effects on the behavior and performance of the application, and is generally not recommended for tables that contain more than 1 row per page. As someone else has already stated, if you are looking to do this in a single transaction, you are probably not going to be able to accomplish it without a table lock. Even if you had enough locks for all the rows, you would be blocking access by other processes as you went along. Depending on the nature of the access (index vs. sequential) and how the app is written, once any query hits a locked row, it cannot proceed further (i.e. fetch the next row in the set) unless it uses a DIRTY READ. And even then, it will not be able to update the table at all, so whatever modifies this counter value won't be able to do its thing. Someone else has already offerred what, to my mind, is the best overall solution: write a program or stored procedure that fetches from the table (use a persistant cursor for this) and then updates one row at a time within a transaction. The persistant cursor will not be closed by the COMMIT. Of course, the obvious problem with this is that any row whose counter value gets incremented before the "clearing" process gets to it will essentially lose that update when the "clearing" process finally does get to it. Given the nature of the problem and the fact that you are clearing this at all implies that this data (the counter value) is not exactly mission-critical, that probably doesn't matter much. If it does, then it is time to go back to the drawing board with the application design! Dave On 20 Apr 2004 01:01:05 -0700, jacques.sanson@ferma.fr (Jacques SANSON) wrote: >Hi, > >I need to update one row of a whole table. >The row is a daily counter, so it needs to be reset >everyday. > >I need to do this on a very large table (1 to 10 million rows), >which exceeds the maximum number of locks available, >so a simple UPDATE statement will not do. > >A table lock will not do either, since this table needs >to be accessible while this update is performed. > >Is there a ready solution for this (either in SQL or ESQL-C ?) >with IDS 9.30.UC2 or above ? > >Thanks, >--Jacques--