Understanding Row Locking
The Microsoft SQL Server 2005 JDBC Driver uses SQL Server row locks. These implement concurrency control among multiple users who are performing modifications in a database at the same time. By default, transactions and locks are managed on a per-connection basis. For example, if an application opens two JDBC connections, locks that are acquired by one connection cannot be shared with the other connection. Neither connection can acquire locks that would conflict with locks held by the other connection.
If row locking is used, all rows in the fetch buffer are locked, so a very large setting for the fetch size can affect concurrency.
Locking is used to assure transactional integrity and database consistency. Locking prevents users from reading data that is being changed by other users, and prevents multiple users from changing the same data at the same time. If locking is not used, data within the database might become logically incorrect, and queries run against that data might produce unexpected results.
For more information about row locking in SQL Server, see "Locking in the Database Engine" in SQL Server Books Online.