Guruji Point - Code You Want To Write

  • Home
  • Asp.Net
  • WCF
  • SQL
  • Type Script
  • AngularJS
  • Tech News
  • Blog
  • Earn Online

Tuesday, 25 July 2017

Lock A SQL Table Manually / Forcefully

 Lock a Table in sql server, SQL     No comments   

Introduction
In this article we will learn how to lock a sql table and why do we need to lock a table , in which scenario we need to lock a table. What happens when any sql table locked.

Previous Updates
In previous articles we have learnt what is connection pooling in c# . How high quality content affects your Website. Why every business needed digital Marketing and Why digital marketing Required. Authorization in Asp.Net.

What is Lock on a table
When you working with database then it may be possible you are working with looping and your procedure executes your tables in loop and many times it also possible that the procedure plunge your table into a lock condition. Once your table locked you will not able to perform any operation on that table until the lock not released.
Means when any table Locks it means you are no longer to work with that table like DML operation (Insert, Update, Delete operations). You need to release the table from Lock after that it will work like a normal table.


Types of Lock in Sql server 
Many types of locks in sql server that can occur while performing operation in table such  as exclusive locks, shared locks, DML locks, transaction locks and backup-recovery locks.
              But there are a number of problems that can be caused by database locking. They can generally be broken down into 4 categories: Lock Contention, Long Term Blocking, Database Deadlocks, and System Deadlocks. 

Why do we need to lock a table
If you are randomly reading this topic then now may be think why do i need to lock a table if it will be bad for us. As per your point of view  Yes we don't need this . But many times when we need to test the table and restrict the operation performed on table. 
In my case i have faced the lock condition on table and i released the lock and then it works fine but  i have to reproduce the same scenario for testing purpose that's why i think that locking a table is also a good practice many times.

In real time you will not lock the table but you can delay the table using WAITFOR DELAY sql function. By giving time of delay you will restrict the table for the given time period.


BEGIN TRAN 
SELECT 1 FROM YourTable_Name WITH (TABLOCKX)
WAITFOR DELAY '00:0:10'
ROLLBACK TRAN  
GO

-- Delay for 10 Seconds

Topics you may also interested  Cursor and Triggers in SQL. Difference Between SCOPE_IDENTITY(), @@IDENTITY and IDENT_CURRENT.


Now no one can perform operation on this table until the time delay will no elapsed. Here i execute this delay query by given time for 10 seconds and run the select statement for the same table in sql window but it will not return table data until the delay time not elapsed.. You can better understand by below given output image.



Read more about the Lock.

  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to XShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Facebook Updates

Guruji Point

Categories

Comman table Expression in SQL Dynamic Grid Row IQueryable in c# Learn AngularJS Scope_Identity() Use of indexes comma seperated string in sql row_number() and dense_rank() salary table structure. while loop in sql why do we need cursor why tuple used

About Us

This blog is about the Programming ,Tech News and some intresting facts related Contents. Here you can find fundamental and expert level topic of programming languages very practically related to C# , Asp.Net ,Sql-Server, JavaScript, Jquery, WebServices And also Web-Api, WCF ,WPF, Angular Js.

Contact Us

Email Us - gurujipoints@gmail.com
Contact- 8077657477

Reach Us

Copyright © Guruji Point - Code You Want To Write