my problem is that locking 1 row in a table prevent other application instances to lock other rows in the same table.
The first application executes the following select statement to gain an update lock before editing data:
SELECT * FROM FL_KONTROLLE WITH (UPDLOCK,ROWLOCK)
WHERE INSTALLATION_ID=? AND KONTROLLE_ID=?
The 2 columns in the where clause are the primary key columns of that table.
While the transaction is still open (no commit executed), a second instance of the same application executes the same statement, using different parameter values. In my understanding, the second application should be able to to gain the lock to its row because it uses different values for the primary key columns, thus requesting locks for a different row.
But in result, the second instance is blocked, waiting on a lock. According to the monitoring tools, the lock in question is an "KEY" "Update" lock on the primary key index object for that table. That lock is held by the first application, and required by the second one.
If I'm doing the same selects with another table, it works and both application instances gain the requested locks.
We are using a MSSQL Server 2000 accessed thru the microsoft jdbc driver.
Any ideas what can cause (and solve) that? Are there hidden table options to control the row locking behavior?
The main goal of "select for update" is to make sure that other connections can not update the locked row before the locking connection commits or rollbacks the transaction. That behavior is absolutely essential here because otherwise a second users could accidentally override changes made by the first user.
If it is possible to set row locks without the locking hints, tell me how.
The table allready has a version column, but that is no solution.
What should a user do if the applications tells him that the row has changed by another user since he began to edit it? Should he discard all his work? Due to the complexity of the data model below that single row, it could easily take an hour until he has finished entering all data that need to be saved at once.
As said in the previous post, it is REQUIRED to set a lock before editing a row.
Ah! So that explains why you are acting so heavy handed on locking.
I can't replicate this in SSMS. Row locks on PK values do not interfere with other row locks. Is it to do with the provider? For example, are you sure it is not taking a key range lock as that would explain the behaviour?
A question I asked and got lost earlier:
"So to understand - if you do EXACTLY the same process on other tables, everything works fine?" And by exactly the same, I mean passing PK parameters via the java driver.