UPDATE STatement

Question

UPDATE STatement

bvsc

Ten Centuries

Points: 1288
More actions
August 4, 2011 at 12:59 pm

#241704

I have a table with 50 columns(col1,col2....col50)
If I want to update col5....So, Can I get col10,col15 values without x-lock???
Like update table set col5= 'updating' where col1= 10
at the same time from other query window
select col10,col15 from table where col1 = 10
Will this select statement impacted by the update statement???
Can some one please explain this?
I would appreciate.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

bvsc (8/4/2011)
I have a table with 50 columns(col1,col2....col50)
If I want to update col5....So, Can I get col10,col15 values without x-lock???
Like update table set col5= 'updating' where col1= 10
at the same time from other query window
select col10,col15 from table where col1 = 10
Will this select statement impacted by the update statement???
Can some one please explain this?
I would appreciate.

SQL handles locks for you automatically... so i'm not sure what the question is.

if you update the table where row=10, an exclusive lock for that row will get created for the picoseconds it takes to perform the operation. a select that happens at that exact moment will wait for the lock to clear.

the moment it is done, the select will get the data it requested.

now, if you had an explicit begin transaction command, the select statement would appear to hang until the update command is explicitly commited with (COMMIT TRAN/ROLLBACK TRAN, or if the connection is dropped, where it would be rolled back.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bvsc Ten Centuries Points: 1288 More actions · Answer 2

Hi Lowell, I got it now... I storngly make sure that It would be row lock rather table lock for single row updation.

Thank you very much for the quick response though.

Lowell SSC Guru Points: 323547 More actions · Answer 3

gotcha; an exclusive table lock might occur if you are updating the whole table (SET name = UPPER(name))

or if you were updating on a range(' WHERE name like 'S%') ....the compiler might decide that rather than taking out a bunch of row locks, it's cheaper to take out a table lock instead.

in any case, SQL would only keep the locks as long as needed, and then other operations would be able to continue...it's typically pretty short term...pico and microsoeconds.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!