locked
Best isolation for high volume of transactions RRS feed

  • Question

  • User-466819921 posted

    when there is high volume of many transactions then what isolation one should use ?

    i know serializable is best one but it has some performance issue. i want when trans doing insert update and delete until it is not committed then trans2 will not be able to see the changes.

    discuss best guidance to handle this scenario. thanks

    Monday, June 4, 2018 8:42 PM

Answers

  • User475983607 posted

    i try to mean suppose in a minute 1000 data inserted and update from many transaction and also other transaction is fetching data using select clause. so there is a chance that select clause may fetch data which is not committed yes. so i want to know what would be best approach i should take as a result when many data is inserting / updating in table then select will fetch only committed data. if rows are locked then fetch old data.

    so guide me what would be best approach or isolation i should use which help me to achieve it with good performance. because i read serializable and snapshot decrease performance.

    waiting for further help and suggestion. thanks

    Again, your question is too open to answer accurately.  If your requirement is absolutely no dirty reads then either SNAPSHOT or SERIALIZABLE.  

    Please read the openly published reference documentation and pick an isolation level consistent with your requirement. 

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 6, 2018 8:32 PM

All replies

  • User475983607 posted

    The requirement, "high volume of many transactions", is hard to conceptualize.   Pick an isolation level that best fits your application/current connection.

    The SQL Docs explain isolation level in depth.  You should be able to align your requirements with the docs.

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017

    Monday, June 4, 2018 11:29 PM
  • User-466819921 posted

    i try to mean suppose in a minute 1000 data inserted and update from many transaction and also other transaction is fetching data using select clause. so there is a chance that select clause may fetch data which is not committed yes. so i want to know what would be best approach i should take as a result when many data is inserting / updating in table then select will fetch only committed data. if rows are locked then fetch old data.

    so guide me what would be best approach or isolation i should use which help me to achieve it with good performance. because i read serializable and snapshot decrease performance.

    waiting for further help and suggestion. thanks

    Wednesday, June 6, 2018 7:59 PM
  • User475983607 posted

    i try to mean suppose in a minute 1000 data inserted and update from many transaction and also other transaction is fetching data using select clause. so there is a chance that select clause may fetch data which is not committed yes. so i want to know what would be best approach i should take as a result when many data is inserting / updating in table then select will fetch only committed data. if rows are locked then fetch old data.

    so guide me what would be best approach or isolation i should use which help me to achieve it with good performance. because i read serializable and snapshot decrease performance.

    waiting for further help and suggestion. thanks

    Again, your question is too open to answer accurately.  If your requirement is absolutely no dirty reads then either SNAPSHOT or SERIALIZABLE.  

    Please read the openly published reference documentation and pick an isolation level consistent with your requirement. 

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 6, 2018 8:32 PM