0

When we run the CASE:A & B one line by line (means one line CASE:A then one line CASE:B) MS-SQL Hangs on select statement but PostgreSQL executes it very well

CASE:A

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
begin transaction

    insert into mytable (id, name) values (98,'person-1')
    select * from mytable order by id

CASE:B

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
begin transaction

    insert into mytable (id, name) values (99,'person-2')
    select * from mytable order by id

Why MS-SQL blocking mytable to run "select *" query while we are using isolation level READ-COMMITTED

We are having same project on MSSQL & PostgreSQL with more or less same queries and architecture.

PostgreSQL project is running fine but MS-SQL project is randomly giving DEADLOCKS, CHOCKING SYSTEM etc.

Viny
  • 13
  • 2
  • SQL Server doesn't use [MVCC](https://www.postgresql.org/docs/current/mvcc-intro.html) by default so readers block writers and writers block readers. This _can_ be changed in modern Versions, but it seems a lot of SQL Server DBAs are scared by that. –  Nov 11 '22 at 16:44
  • "This can be changed in modern Versions" how can we change it – Viny Nov 11 '22 at 17:25
  • Read up on snapshot isolation, specifically the `READ_COMMITTED_SNAPSHOT` setting. – Jeroen Mostert Nov 11 '22 at 17:38
  • 1
    @JeroenMostert enabling RCSI in MS-SQL leads to different [PROBLEM](https://stackoverflow.com/posts/comments/130906169?noredirect=1) when we enable RCSI we face **[inconsistent behaviour](https://stackoverflow.com/posts/comments/130906169?noredirect=1) ** on heavy load. But on normal load it works fine – Viny Nov 11 '22 at 18:30
  • There's no free lunch. With snapshot isolation you get different behavior for concurrent data modifications that you have to take into account. The code in the linked question operates using the very bad pattern of holding a transaction for too long, but if you insist you'll have to use `SERIALIZABLE`, and moreover you should ensure the `UPDATE` and `INSERT` in that question actually lock things -- *first* perform the insert with a `WHERE NOT EXISTS` so a range lock is taken, then do the `UPDATE`. In any case RCSI isn't directly relevant since `READ COMMITTED` is too low a level anyway. – Jeroen Mostert Nov 11 '22 at 18:56

0 Answers0