Search
Subscribe
Categories
- Backup and Restore
- Capacity Planning
- Compression
- Configure
- Contact me
- CPU
- Data Architecture
- Data Import and Export
- Database Mirroring
- DBA
- DBA Scripts
- DBCC
- Deadlocks
- Default Trace
- Development
- Disaster Recovery
- Disk Storage
- DMV
- Documentation
- Event Log
- Execution Plan
- Function
- High Availability
- Indexes
- Install & Setup
- Interviews
- IO Subsystem
- Isolation levels
- Jobs
- Latches
- Links
- Locking
- Memory
- Monitor Performance
- MS DTC
- MSDB
- Network
- Object Management
- Pagination
- Partitioning
- Performance
- Powershell
- Security Management
- SQL Agent
- SQL Azure
- SQL Blocking
- SQL Cachestore
- SQL DAC
- SQL Default Trace
- SQL DTS
- SQL Error Logs
- SQL Execution Plan
- SQL Optimizer
- SQL problems
- SQL Query Tuning
- SQL Server Error Tips
- SQL Server Management Studio
- SQL Server Profiler
- SQL Server Tuning
- SQL Trigger
- SQL View
- SQLServer-DBA News
- SSIS
- Statistics
- Stored Procedure
- T-SQL
- TempDB
- Tools
- TPC-H
- Transaction Logs
- View
- Virtualization
- Wait Stats
SQLServer-DBA.com Links
Recent Posts
- Forgotten sa password
- Sql server installation is either corrupt or has been tampered with error getting instance id from name
- Find Weak passwords in SQL Server
- Event ID 55 : The file system structure on the disk is corrupt and unusable on volume
- Career in DBA
- Error 3254 – The volume on device BACKUP_PATH is empty
- SQL auto close - Keep it turned off
- Windows Pagefile size for dedicated SQL Servers
- SQL Database Corruption
- max server memory and sql server memory – why is total memory greater than max memory?
10 December,2012 by Jack Vamvas
Question: A workflow application requires an unobtrusive lock on data in a transactional database. It’s a critical transactional system, so I don’t want to create blocking to INSERT or UPDATE statements by acquiring Shared Locks. What are the advantages\ disadvantages of using the READUNCOMMITED table hint?
Answer: Using the READUNCOMMITED or NOLOCK table hint for applications should be used with caution. Analysis should be based on comparing the Concurrency levels required versus data access. Read more on SQL Server – Concurrency Control – Optimistic and Pessimistic
Advantages
1) Allows dirty reads
2) A Shared Lock is not issued. This allows UPDATE and DELETE statements to acquire Exclusive Locks.
3) Exclusive Locks don’t block the READUNCOMMITTED requests
4) Higher concurrency
Disadvantages
1) Risk of other transactions rolling back a transaction while READUNCOMMITTED in process. Data may not be valid by the end of the Dirty Read.
2) The application may return invalid data
3) READUNCOMMITTED captures a Sch-S (Schema Stability Lock) , which conflicts with the Sch-M (Schema Modified Lock) - such as a schema change. This causes blocking. READUNCOMMITTED does not guarantee a lock is obtained
4) The hint substitutes the query optimizer . Review the impact of the READUNCOMMITED before applying in Production
Read More
SQL Server – Concurrency Control – Optimistic and Pessimistic
SQL Server ISOLATION LEVELs ,UDF and table hints
Author: Jack Vamvas (http://www.sqlserver-dba.com)
Verify your Comment
Previewing your Comment
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |