Select query block inset query

All replies

• 

  

  1

  Sign in to vote

   
  

  In oracle select is compatible with insert and both can run together. Please let me know is it in different in SQL server? 

  Unlike Oracle, SQL Server (stand alone version, not Azure SQL Database) uses locking rather than row versioning by default to ensure read consistency in the READ_COMMITTED isolation level. This typically maximizes performance but with the side-effect that readers may block writers as in your case. Query and index tuning is often the way to improve concurrency (and performance). This facilitates fine-grain locks of short duration that can mitigate the impact of readers blocking writers. 
  
  Another technique to improve concurrency is with a row versioning isolation level. Row versioning allows SQL Server to use the row version store instead of locking to provide read consistency. This can be done by either 1) using the SNAPSHOT isolation level explicitly for the SELECT query session (which also requires the ALLOW_SNAPSHOT_ISOLATION database option) or 2) by turning on the READ_COMMITTED_SNAPSHOT database option.
  
  When the READ_COMMITTED_SNAPSHOT database option is turned on, all READ_COMMITTED isolation level sessions use row versioning instead of locking (similar to the Oracle behavior you're familiar with). While this is beneficial for concurrency, be aware that existing applications may rely on locking behavior (e.g. a table used as a FIFO queue) so it shouldn't be turned on indiscriminately without due diligence. 
  
  Also be aware that when either the ALLOW_SNAPSHOT_ISOLATION or READ_COMMITTED_SNAPSHOT database options are turned on, additional storage space (14-bytes per row) will be incurred as well as increased tempdb storage and usage (for the version store). However, concurrency benefits often outweigh these costs.

  
  

  ---

  Dan Guzman, Data Platform MVP, http://www.dbdelta.com

  
  

  • Edited by Dan GuzmanMVP, Editor Saturday, July 18, 2020 1:22 PM
  • Proposed as answer by Laxmidhar sahoo Sunday, July 19, 2020 5:23 AM

  Saturday, July 18, 2020 1:08 PM

  Answerer
• 

  

  1

  Sign in to vote

  This is more related to volume of data you Select query is pulling. If you are reading the whole table and with some TEXT based condition, SQL may put a Lock for better query optimization to get the data faster. At times, this may cause locking issue for Insert or Update activities. 

  Keep your SELECT query to smaller amount of data, use NOLOCK hint for your select statement queries.

   

  Saturday, July 18, 2020 9:51 PM
• 

  

  0

  Sign in to vote

  Hi

  @Dan and @DBA_Atul described everything .just I put a little

  It is documented that READ_COMMITTED_SNAPSHOT is more efficient than ALLOW_SNAPSHOT_ISOLATION
  
  For more details follow the link
  
  https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188277(v=sql.105)?redirectedfrom=MSDN
  
  Here are the commands to change them.
  
  ALTER DATABASE SQLAuthority SET READ_COMMITTED_SNAPSHOT ON
  ALTER DATABASE SQLAuthority SET ALLOW_SNAPSHOT_ISOLATION ON

  Thanks and regards

  Sunday, July 19, 2020 5:24 AM
• 

  

  0

  Sign in to vote

  Hi Arif,

  For the SQL Server Transaction Locking and Row Versioning, please refer to this article.

  The default transaction isolation level in SQL Server is the READ COMMITTED isolation level, in which retrieving the changing data will be blocked until these changes are committed. The NOLOCK hint allows SQL to read data from tables by ignoring any locks and therefore not being blocked by other processes. This can improve query performance and concurrency, but also introduces the possibility of dirty reads, nonrepeatable reads or phantom reads.

  Read more:
  https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/
  

  https://stackoverflow.com/questions/12608780/understanding-sql-server-locks-on-select-queries
  

  Best Regards,
  Cris
  

  ---

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

  Monday, July 20, 2020 2:44 AM
• 

  

  1

  Sign in to vote

  In addition to Dan's reply

  Read_committed_snapshot is the database option that affects behavior of the readers in READ COMMITTED isolation level. When you enable this option on database level (requires exclusive db access to change it), all your readers in READ COMMITTED isolation level will start to use row-versioning reading old(committed) versions from the version store rather than being blocked by (S)/(X) locks incompatibility. This option does not require any code changes (assuming you readers are using READ COMMITTED). It would not change behavior of writers (writers still block each other) nor behavior of readers in any other transaction isolation level.

  Snapshot isolation level is full blown transaction isolation level. It needs to be explicitly specified in the code. Enabling snapshot isolation level on DB level does not change behavior of queries in any other isolation levels. In that option you are eliminating all blocking even between writers (assuming they do not update the same rows) although it could lead to 3960 errors (data has been modified by other sessions).

  Speaking of consistency, RCSI gives you statement level consistency – readers ignores the data changes done after statement has been started. Snapshot – transaction level consistency – session deals with “snapshot” of the data at the moment transaction started. Again, it could lead to error 3960 in the system with volatile data.

  Downsides:

  1. Excessive tempdb usage due to version store activity. Think about session that deletes 1M rows. All those rows must be copied to version store regardless of session transaction isolation level and/or if there are other sessions that running in optimistic isolation levels at the moment when deletion started.
  2. Extra fragmentation – SQL Server adds 14-byte version tag (version store pointer) to the rows in the data files when they are modified. This tag stayed until index is rebuild
  3. Development challenges – again, error 3960 with snapshot isolation level. Another example in both isolation levels – trigger or code based referential integrity. You can always solve it by adding with (READCOMMITTED) hint if needed.

  While switching to RCSI could be good emergency technique to remove blocking between readers and writers (if you can live with overhead AND readers are using read committed), I would suggest to find root cause of the blocking. Confirm that you have locking issues – check if there are shared lock waits in wait stats, that there is no lock escalations that block readers, check that queries are optimized, etc.  

  ---

  Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
  
  MS SQL optimization: MS SQL Development and Optimization
  MS SQL Consulting: Large scale of database and data cleansing
  Remote DBA Services: Improves MS SQL Database Performance
  SQL Server Integration Services: Business Intelligence
  

  • Proposed as answer by Laxmidhar sahoo Saturday, July 25, 2020 9:04 AM

  Monday, July 20, 2020 4:04 AM
• 

  

  0

  Sign in to vote

  Hi Arif,

  Is there any update on this case?

  If you have resolved your issue, please mark the useful reply as answer. This can be beneficial to other community members reading the thread.

  Thanks for your contribution.

  Best regards,
  Cris
  

  ---

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

  Friday, July 24, 2020 1:20 AM