> > >
Use this form to set the global concurrency model to use for database changes. The concurrency model you choose can significantly affect database performance.
About concurrency strategies
You can select an optimistic concurrency model or a pessimistic concurrency model globally for the database. Concurrency settings can also be controlled at the individual table or statement level.
Optimistic concurrency
Optimistic concurrency does not lock data when the data is retrieved from the database for future modification. Therefore, no locks are held while filters and other business logic are being applied. Data is locked only when an update is performed. If any data has been changed by another transaction between the time of the retrieval and the time of the update, the change is detected and an Infolog exception is displayed.
Pessimistic concurrency
A pessimistic concurrency strategy uses an update lock to lock data when the data is retrieved from the database for future modification. Locks are held while filters and other business logic are being applied, in addition to being held during an update. Data cannot be changed by other transactions.
Acquiring an update lock for a large volume of rows increases the lock escalation from row level to table level in Microsoft SQL Server. This can block other users and reduce transaction throughput.
Table-level concurrency settings
If the global concurrency mode is set to , table-level control of concurrency settings is available using the OccEnabledproperty.
Statement-level concurrency settings
You can use the
optimisticlockor
pessimisticlockkeywords in a
SELECT
statement to override the global or table
concurrency mode settings. For detailed information, see
Select Statement Syntax.
Tasks that use this form
Navigating the form
The following table provides descriptions for the controls in this form.
Fields
Field |
Description |
---|---|
|
The default setting. Enables concurrency mode to be set per table. Optimistic concurrency is initially set for all tables, with a few exceptions. The table property OccEnabledcan be changed for any table, as appropriate. |
|
Enables optimistic concurrency mode for all tables. |
|
Enables pessimistic concurrency mode for all tables. |
|
Writes all
|