Skip to main content

Posts

Showing posts from August, 2016

SQL Server Isolation Levels with examples

Following are the different types of isolations available in SQL Server. READ COMMITTED READ UNCOMMITTED REPEATABLE READ SERIALIZABLE SNAPSHOT Let us discuss about each isolation level in details.Before this, execute following script to create table and insert some data that we are going to use in examples for each isolation IF OBJECT_ID('Emp') is not null begin DROP TABLE Emp end create table Emp(ID int,Name Varchar(50),Salary Int) insert into Emp(ID,Name,Salary) values( 1,'David',1000) insert into Emp(ID,Name,Salary) values( 2,'Steve',2000) insert into Emp(ID,Name,Salary) values( 3,'Chris',3000) Note:   Before executing each example in this article, reset the Emp table values by executing the above script. Read Committed In select query it will take only commited values of table. If any transaction is opened and incompleted on table in others sessions then select query

How to acquire a table lock for specific time?

Recently one of my colleagues asked me how to acquire an exclusive lock on a database table for a specific amount of time. And this lock should be released once the specified time expires. You need two components to accomplish this task: 1) Table hints – that puts exclusive lock on the table 2) Delay element – that will hold the lock for a specified amount of time 1) Table hints Table hint TABLOCKX can be used to acquire exclusive lock on the table. 1 2 3 SELECT * FROM <table_name> WITH (TABLOCKX) 2) Delay element WAITFOR statement inside transaction block can be used to introduce delay. Following sample code snippet introduces a delay for 10 seconds 1 WAITFOR DELAY '00:00:10' Here is the code snippet that creates a test table, inserts some data and places an exclusive lock for 10 seconds on this table 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 CREATE TABLE test_tab