1. Optimistic concurrency control
2. Pessimistic concurrency control
In pessimistic model, transactions lock the rows that they are reading or modifying so that other transactions have to wait till this transaction completes to access the rows.
In optimistic model, the rows are not locked. Two transactions can simultaneously read/update a row. Only if there are any conflicting updates identified, then one of the transactions is rolled back. Since there is no locks involved in this model, this performs much better than the former.
Postgres database by default has optimistic model. SQL server on the other hand was originally designed using pessimistic model but due to performance problems in it an option was later provided in SQL server to enable optimistic concurrency control. This is called read committed snapshot isolation.
How to verify / enable RCSI
To check whether RCSI has been enabled:
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE [name] = '<db_name>'
If this returns 1 then RCSI is enabled. If not, follow the below instructions to enable it.
Instructions to enable RCSI:
This process might take considerable amount of time to complete. Please do this on a weekend / during off hours as it requires downtime.
1. Shut down the ServiceDesk Plus service. If any other application (maybe reporting tools) connects to servicedesk database, you need to stop them too.
2. Please login to SQL Management studio and execute the below queries...
i. take the database offline
ALTER DATABASE <db_name> SET OFFLINE WITH ROLLBACK IMMEDIATE
ii. enable read committed snapshot
ALTER DATABASE <db_name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE
iii. bring database back online
ALTER DATABASE <db_name> SET ONLINE
3. Please verify whether RCSI is enabled using the query mentioned at the top. You may start ServiceDesk Plus now.