7

When I right-click on a table in SQL Server Management Studio and choose 'Edit top 200 rows', at what point, if ever, would the data that I'm looking at be locked?

A colleague of mine stated that when viewing data in this way, the data rows can be locked (I think when he said if you put a cursor on a row and start editing data).

Is this the only time the data can be locked in this context?

Jason Evans
  • 28,906
  • 14
  • 90
  • 154

4 Answers4

5

It's not true. Run this script in one window (and leave it running):

create table Tx (
    ID int not null,
    Val1 varchar(20) not null
)
go
insert into Tx (ID,Val1)
select 1,'abc'
go
set nocount on
while 1=1
begin
    update Tx set Val1 = CASE WHEN Val1='abc' then 'def' else 'abc' end
    RAISERROR('Updated',10,1) WITH NOWAIT
    WAITFOR DELAY '00:00:05'
end

Every 5 seconds, it flips the value of the Val1 column, and prints Updated in the messages tab (assuming you're set to "results in grid").

Then, in another instance of SSMS, open the Tx table and start editing the single row in there. Observe that the script continues to perform updates.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Thanks to both Damien and gbn for your answers. I would like to mark both as accepted. I will accept Damien's because of the provided SQL script which can help me prove what happens in SSMS when editing data. But thanks to both of you equally. – Jason Evans Dec 22 '11 at 11:01
3

It can be locked. In the Edit Panel, try to insert a row and violate the Primary Key constraint. You will get an error dialog popup message. The table will now be locked while you are reading that message. If you walk away without confirming the message, that table will be locked indefinitely. I just confirmed this with both SSMS 2008R2 and SSMS 2012.

TTT
  • 22,611
  • 8
  • 63
  • 69
  • 1
    That's a bit scary. Though luckily we did not have anyone on our team who would leave a dialog open like that! (I hope :) ) Thanks for going to the effort to prove this though. Much appreciated. – Jason Evans Feb 24 '14 at 08:49
2

The data won't be locked by default in the SSMS GUI editor.

If you make changes and try to save, you'll get errors/warning ato overwrite/discard etc. I can't test right now to see the exact messages, sorry.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • It seems that some of the errors/warning dialogs actually can cause a lock- I detailed this in a new answer. – TTT Feb 23 '14 at 19:37
-2

It does not. Many people will try to tell you it does, however it does not. Tested and confirmed.

Chris
  • 1