Understanding concurrency control

Download JDBC driver

Concurrency control refers to the various techniques that are used to preserve the integrity of the database when multiple users are updating rows at the same time. Incorrect concurrency can lead to problems such as dirty reads, phantom reads, and non-repeatable reads. The Microsoft JDBC Driver for SQL Server provides interfaces to all the concurrency techniques used by SQL Server to resolve these issues.

Poznámka

For more information about SQL Server concurrency, see "Managing Concurrent Data Access".

Remarks

The JDBC driver supports the following concurrency types:

Concurrency Type	Characteristics	Row Locks	Description
CONCUR_READ_ONLY	Read Only	No	Updates through the cursor are not allowed, and no locks are held on the rows that make up the result set.
CONCUR_UPDATABLE	Optimistic Read Write	No	Database assumes row contention is unlikely, but possible. Row integrity is checked with a timestamp comparison.
CONCUR_SS_SCROLL_LOCKS	Pessimistic Read Write	Yes	Database assumes row contention is likely. Row integrity is ensured with row locking.
CONCUR_SS_OPTIMISTIC_CC	Optimistic Read Write	No	Database assumes row contention is unlikely, but possible. Row integrity is verified with a timestamp comparison. For SQL Server 2005 (9.x) and later, the server will change this to CONCUR_SS_OPTIMISTIC_CCVAL if the table does not contain a timestamp column. For SQL Server 2000 (8.x), if the underlying table has a timestamp column, OPTIMISTIC WITH ROW VERSIONING is used even if OPTIMISTIC WITH VALUES is specified. If OPTIMISTIC WITH ROW VERSIONING is specified and the table does not have timestamps, OPTIMISTIC WITH VALUES is used.
CONCUR_SS_OPTIMISTIC_CCVAL	Optimistic Read Write	No	Database assumes row contention is unlikely, but possible. Row integrity is checked with a row data comparison.

Result sets that are not updateable

An updatable result set is a result set in which rows can be inserted, updated, and deleted. In the following cases, SQL Server cannot create an updatable cursor. The exception generated is, "Result set is not updatable."

Cause	Description	Remedy
Statement is not created by using JDBC 2.0 (or later) syntax	JDBC 2.0 introduced new methods to create statements. If JDBC 1.0 syntax is used, the result set defaults to read-only.	Specify result set type and concurrency when creating the statement.
Statement is created by using TYPE_SCROLL_INSENSITIVE	SQL Server creates a static snapshot cursor. This is disconnected from the underlying table rows to help protect the cursor from row updates by other users.	Use TYPE_SCROLL_SENSITIVE, TYPE_SS_SCROLL_KEYSET, TYPE_SS_SCROLL_DYNAMIC, or TYPE_FORWARD_ONLY with CONCUR_UPDATABLE to avoid creating a static cursor.
Table design precludes a KEYSET or DYNAMIC cursor	The underlying table does not have unique keys to enable SQL Server to uniquely identify a row.	Add unique keys to the table to provide unique identification of each row.

See also

Managing result sets with the JDBC driver