Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 07-09-2009
Member
 
Join Date: Jan 2009
Posts: 88
How to lock tables in SQL Server

I have created a huge database using SQL Server. I want to lock certain tables so that any person connecting to the database would not get access to those tables. Is this possible? What all things do I need to implement to lock the tables of my SQL Server database?
Reply With Quote
  #2  
Old 07-09-2009
Member
 
Join Date: Nov 2008
Posts: 1,221
Re: How to lock tables in SQL Server

The following kind of locks are available in SQL Server which you can apply with SELECT, INSERT, UPDATE, and DELETE statements:
  • HOLDLOCK: Hold a shared lock until completion of the transaction instead of releasing the lock as soon as the required table, row, or data page is no longer required. HOLDLOCK is equivalent to SERIALIZABLE.
  • NOLOCK: Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
  • PAGLOCK: Use page locks where a single table lock would usually be taken.
  • READCOMMITTED: Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server 2000 operates at this isolation level.
  • READPAST: Skip locked rows. This option causes a transaction to skip rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
  • READUNCOMMITTED: Equivalent to NOLOCK.
  • REPEATABLEREAD: Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.
  • ROWLOCK: Use row-level locks instead of the coarser-grained page- and table-level locks.
  • SERIALIZABLE: Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.
  • TABLOCK: Use a table lock instead of the finer-grained row- or page-level locks. SQL Server holds this lock until the end of the statement. However, if you also specify HOLDLOCK, the lock is held until the end of the transaction.
  • TABLOCKX: Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.
  • UPDLOCK: Use update locks instead of shared locks while reading a table, and hold locks until the end of the statement or transaction. UPDLOCK has the advantage of allowing you to read data (without blocking other readers) and update it later with the assurance that the data has not changed since you last read it.
  • XLOCK: Use an exclusive lock that will be held until the end of the transaction on all data processed by the statement. This lock can be specified with either PAGLOCK or TABLOCK, in which case the exclusive lock applies to the appropriate level of granularity.
Reply With Quote
  #3  
Old 07-09-2009
Member
 
Join Date: Nov 2008
Posts: 1,054
Re: How to lock tables in SQL Server

There are mainly 3 kinds of locks, namely Shared lock, Update lock and Exclusive lock. The shared locks are those operations that do not change or update data (e.g. SELECT statements). The update locks are those that intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes. Whereas the exclusive locks are those that modifies the data (e.g. UPDATE, INSERT, or DELETE).
Reply With Quote
  #4  
Old 07-09-2009
Member
 
Join Date: Nov 2005
Posts: 1,323
Re: How to lock tables in SQL Server

Simply use the follow SELECT statement to lock your tables so that, whoever connects to the database, would not get access to those tables:

Code:
SELECT * FROM table_name WITH TABLOCKX
In short, TABLOCKX prevents others from reading or updating the table table_name.
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to lock tables in SQL Server"
Thread Thread Starter Forum Replies Last Post
HP Pavilion dv4 not getting charged only Scroll/Num Lock and Caps Lock LED's blinking Pillars Portable Devices 5 31-01-2012 10:06 PM
Issue with Dinovo Mini backslash, caps lock and num lock keys Jarini Hardware Peripherals 7 02-11-2010 02:27 AM
Set warning sound for Caps lock,Num lock in Windows 7 rajmalhotra Operating Systems 3 13-05-2009 02:49 PM
Windows 2000 /2003 server -Screen lock timeout criechton Windows Server Help 6 13-05-2009 12:50 AM
Account lock out frequently on Windows Server Kaysel Window 2000 Help 3 14-02-2008 07:22 PM


All times are GMT +5.5. The time now is 01:45 AM.