mO SharemO Share

How To: Enable snapshot isolation for POS database

Owned by
Aparajita Basu Roy
Sept 15, 2022
2 min read

Enabling Snapshot Isolation level in the POS database will significantly reduce the amount of deadlocks and blocking transactions in the system (e.g. slowness is item scanning while some report is being run). If user is using GINESYS POS in a multi-user scenario then it is advised that they enable SNAPSHOT ISOLATION in their POS database. Following is an advanced reading from MSDN. Please note that enabling SNAPSHOT ISOLATION will increase the size of the 'tempdb' database in SQL Server. So please ensure that there is adequate space in the drive where SQL Server data files are stored.


Information from MSDN


Understanding Snapshot Isolation Level and Row Versioning

Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. Newer row versions created after the transaction has begun are ignored by the transaction.


The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.


Step-by-step guide

  1. Ask all users to logout from the system.

  2. Stop all GINESYS POS Data Sync service and GINESYS License Manager service from Windows Service Manager.

  3. Open SQL Server Management Studio and login with 'sa' user.

  4. Take the database into single user mode. If the following statement does not succeed then do not proceed to next step without resolving this step.

    ALTER DATABASE <databasename> SET SINGLE_USER

    IMPORTANT

    NOTE: If the above statement gets hanged then click the 'Stop' icon at the top of the SQL Query window and run sp_who2 to identify which users/terminals are connected to the system

  5. Run the following script in SQL Server Management Studio on the POS database. Replace NPOS with the name of the database if it is different.

    ALTER DATABASE NPOS
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE NPOS
SET READ_COMMITTED_SNAPSHOT ON

  6. Verify that SNAPSHOT ISOLATION has been set to on by running the following command.

    SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name= 'NPOS'

  7. If the return value of the above statement is 1 then SNAPSHOT ISOLATION has been set successfully.

  8. Enable multi-user operation in the database by running the following command.

    ALTER DATABASE <databasename> SET MULTI_USER

Alternatively, you can put the Snapshot Isolation on by going to the Tools tab of POS AutoUpdate and click on Snapshot Isolation Mode ON.