Keep in mind most of the work I do is with PostgreSQL so this may or may not be 100% on the money but I think this should be close enough to be helpful.
The basic issue is that in an environment like this you are going to have a great deal of trouble managing the locks. It seems you can either do some sort of conflict resolution or conflict prevention on the lock level. Conflict prevention seems like despite the performance difficulties, it would reduce the level of user frustration significantly.
My approach here would be indeed to do the locking on the central server in a pl/sql stored procedure, which would insert into the locking table if possible, returning a value indicating success, or if that is not possible, either returning a value indicating failure, or raising an exception (what I have done in the past, for example, is to return something identifying who has the lock if it is already locked).
What I would omit is actually checking with the local server. If you have a high read to write ratio and the chance of collisions is otherwise relatively small, you are going to have to check the central server most times anyway, so there isn't much to be gained by checking the local server also. Certainly you wouldn't want to write to both the local and remote servers for locking. Keep locking simple. It is likely to be a source of pain no matter what you do.
The second thing I would suggest here is that I highly recommend expiring locks like this, perhaps after 2 hours or something. There are two major reasons for doing so. The first is that bugs in the application-layer of the code can cause locks not to be released, and secondly if this is over a web interface, HTTP is stateless and therefore you have no real way of knowing that state has dropped. In this way you can give a lock which is valid for a certain period of time, can be renewed pre-emptively (in the background if needed), and times out if the individual closes the browser window and goes home for the day. Some sort of administrative utility to free locks is also something I would recommend.
I share your sense that 7 sec to acquire a lock is a significant performance cost, but in the end I don't see any better way to do this. Your options are limited significantly by the CAP theorem and a single central locking system is probably what's needed.
I suppose another option is that one could have the central server merely lock to branch location, and have the branch location release the lock as soon as no valid locks have been held for a certain period of time. This might have the advantage of allowing faster collaboration by a team, meaning that only the first editor on a team would have to incur that cost.
You will need to lock all the rows yourself before each UPDATE.
See the MySQL Documentation on SELECT ... LOCK FOR UPDATE. This performs an exclusive lock on all the rows you pass through. Then, you can follow up with the needed UPDATE against the table.
In your particular case, you would do this:
SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 100
INDEXING
- You should index the table fully to support all possible ways you will be querying the data. Notwithstanding, you must alternate between
SELECT ... FOR UPDATE and UPDATE.
- Since you have both
row1 and row2 in the WHERE clause, you should have an index with both columns in it.
- There is one warning: If these columns are indexed, expect some slowness because the column is being updated and the BTREE index pages are being updated per row. You should also expect rapid growth of the insert buffer section of ibdata1 (See InnoDB Map)
I have many posts on the subject of SELECT ... FOR UPDATE and SELECT ... LOCK IN SHARED MODE.
UPDATE 2013-03-17 19:21 EDT
Since you have 9 WebServers hitting the DB Server, try this
On WebServer1 run
SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 0,100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 0,100;
On WebServer2 run
SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 100,100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 100,100;
On WebServer3 run
SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 200,100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 200,100;
All the way to WebServer9, run
SELECT * FROM <some_table>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 800,100;
UPDATE <some_table> SET row1=<some value>
WHERE row1 IS NULL AND row2 > <some value>
ORDER BY row2 LIMIT 800,100;
You will have to place some PHP header file that unique identifies which machine runs which version of the query.
Best Answer
The short answer is that it depends on the isolation level of your connection and/or statement being run.
If you are taking the default isolation level (which is usually Cursor Stability - CS) in DB2, then I would assume it only puts a lock on the actual rows being modified (as an update lock) and it puts a read lock on the row currently being held by the cursor. All other rows are not technically locked in any sort of way.
Now if you go up higher into Read Stability (RS) or Repeatable Read (RR), then you start to get into where it will lock more rows in the result set (and even the whole table in a join case)....
You may wish to check out the Isolation Level settings in the DB2 Information Center, as this will give you a better picture of how it will act.
Also note, even though there is a default isolation level for the connection, this can be overridden in the session, as well as at the statement level, including the underlying statement that builds the view. So if the default is CS, but the view was built with a SELECT that uses RR, then you are using RR and probably locking on all rows in both the result set and all rows utilized by the engine to create the result set. I'm guessing this is not likely (or at least hopefully someone didn't write the view that way....) and you are using CS, in which what I explained at the beginning is more likely how it behaves.