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.
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.
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.
0 comments:
Post a Comment