1

The suggested duplicate doesn't answer the question in the title.

I want to lock a table so that it can't be written to, but can be read. Is that possible?

Is that what happens in a transaction with IsolationLevel.Serializable? Or is that only for the rows affected? (And is that a lock on read's as well?)

If it matters: I'm doing this in C#.

ispiro
  • 26,556
  • 38
  • 136
  • 291
  • i think you might want readuncommitted but im not sure – undefined Apr 16 '13 at 08:51
  • 3
    Always be very careful using read uncommitted. It doesn't take out shared locks. In his case it might be the right thing to use, but he has to be aware of dirty reads. – Andy Nichols Apr 16 '13 at 09:03
  • @AndyNichols agreed what it means is that you might get data returned that never gets committed. – undefined Apr 16 '13 at 09:07
  • @LukeMcGregor In addition, he might not get data that has already been committed. See http://stackoverflow.com/a/7355121/806549 –  Apr 16 '13 at 09:16
  • 3
    The main question is really: ***why!?!?*** What are you trying to achieve? You could just **deny** the insert, update and delete permissions from everyone - but what good is that table then?? – marc_s Apr 16 '13 at 09:24
  • It is possible. `SELECT TOP 0 * FROM YourTable WITH (TABLOCK, HOLDLOCK)` will do this for the duration of the transaction it is contained in. But explain why you need to do this. – Martin Smith Apr 16 '13 at 09:27
  • @MartinSmith Is it possible with an `INSERT`? My goal: I want to prevent two identical rows being written by different 'writers'. Every 'writer' checks that a row like it wants to write doesn't exist (before it writes) - but what if both check simultaneously and then write simultaneously - I'll get two identical rows. – ispiro Apr 16 '13 at 09:32
  • 1
    @ispiro the suggested duplicate answers the actual *task* that your comment says is what you want to do, so the fact that it does it by a different mechanism than what your title suggests isn't really relevant. – AakashM Apr 16 '13 at 09:51
  • As for the question in the title I answered that 22 hours ago in the comment two above. – Martin Smith Apr 17 '13 at 07:45
  • @MartinSmith I checked TABLOCK and HOLDLOCK on [MSDN](http://msdn.microsoft.com/en-us/library/ms187373.aspx) but it doesn't seem to say the locks are writing-locks only. – ispiro Apr 17 '13 at 14:32

3 Answers3

0

You should create a UNIQUE constraint on the table. Even if you manage to lock the table by the first process, the other process will most likely only be blocked until the first process exists.

Then, you might start fiddling with the timeouts to ensure that the second process times out before getting access to the table, and you are opening yourself to a world of pain.

A UNIQUE constraint covering whatever fields constitute a duplicate are by far your best option - and will ensure that no duplicates are entered, which as I understand your question (and elaborating comments) is the ultimate goal.

-1

No its not possible.

Only the session that holds the lock can access the table. No other session can access it until the lock is released.

Refer following link:

http://msdn.microsoft.com/en-gb/library/ms173763.aspx

Freelancer
  • 9,008
  • 7
  • 42
  • 81
-1

You might want to look for NOLOCK / ReadUncommitted option: http://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

It will let you read data from a table that is currently locked, but beware of the implications: you might end up reading "ghost" data, i.e. data that has been inserted and then rolled back in a transaction.

Lâm Tran Duy
  • 804
  • 4
  • 8
  • So you're saying the only way is to really lock the table, and then ReadUncommitted (as opposed to a selective lock)? – ispiro Apr 16 '13 at 09:07
  • @ispiro If the table is locked, then you effectively wouldn't be able to write to it, but you would be able to read from it using ReadUncommitted. What are you exactly trying to achieve, though? Are you looking into intentionally locking the table, if so, for what reason and for how long? There might be some other ways to look into. – Lâm Tran Duy Apr 16 '13 at 09:10
  • I want to prevent two identical rows being written by different 'writers'. Every 'writer' checks that a row like it wants to write doesn't exist (before it writes) - but what if both check simultaneously and then write simultaneously - I'll get two identical rows. – ispiro Apr 16 '13 at 09:14
  • 1
    @ispiro Why don't you create a unique constraint on the table? Then the database will reject the potential duplicate. –  Apr 16 '13 at 09:23