Locked tables clarification

Question

Locked tables clarification

pwalter83

SSChampion

Points: 14571
More actions
March 14, 2011 at 8:11 am

#157485

Hi,
Does somesone know if a table is been updated every 5 minutes using an SSIS job…i.e. data being inserted, updated or deleted and someone tries to access the web site linked to those tables…wud that web site be unavailable or in other words wont the web site show any data during those 5 minutes ? or putting it this way…wud the tables be locked ?
Also, is there any way of making those tables available to users ? like may be using something like NOLOCK ?
Thanks and Regards,
Paul

Viewing 11 posts - 1 through 10 (of 10 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

It may be that the entire table will be locked, it may be that rows or pages will be.

Consider snapshot isolation level (or read committed snapshot). Allows writers to not block readers and still gives you consistent results (without the dirty reads that nolock allows)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pwalter83 SSChampion Points: 14571 More actions · Answer 2

GilaMonster (3/14/2011)
It may be that the entire table will be locked, it may be that rows or pages will be.
Consider snapshot isolation level (or read committed snapshot). Allows writers to not block readers and still gives you consistent results (without the dirty reads that nolock allows)

Hi Gail,

Thanks for your answer. Is it possible to include 'read committed snapshot' within the table definition ?

Thanks,

Paul

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

No. It's an isolation level. Have a read through Books Online on Snapshot and Read Committed snapshot and see which would work better for you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pwalter83 SSChampion Points: 14571 More actions · Answer 4

GilaMonster (3/14/2011)
No. It's an isolation level. Have a read through Books Online on Snapshot and Read Committed snapshot and see which would work better for you.

Hi Gail,

Thanks again. atually there is no stored procedure to be used…I just want to add some functionality in the table that even if the records are added or deleted from it, the user is still able to access it..in other words they are not blocked when the process of updating is going on… do you know of something which can be applied to the tables ?

The thing is this table is linked to a web site. so what I want is that even if the table udpate is going on, the user is able to view the data in the web site(through the linked table). It should not become unavailable for the user while it is being updated.

Thanks and Regards,

Paul

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

There's nothing that can be added to the table. You can either change the entire database to read committed snapshot (read up on it before you do that), or you can set an isolation level in the query.

Have you tested and seen that the current behaviour is not what you want? In general, unless those updates take hours, the locking shouldn't be a problem. Short term locking for small updates is just that, short term. If the locks are held for longer periods and the queries are as optimal as possible (blocking is often caused by poorly performing queries), then consider changing the isolation levels

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

pwalter83 (3/14/2011)
Hi,
Does somesone know if a table is been updated every 5 minutes using an SSIS job…i.e. data being inserted, updated or deleted and someone tries to access the web site linked to those tables…wud that web site be unavailable or in other words wont the web site show any data during those 5 minutes ? or putting it this way…wud the tables be locked ?
Also, is there any way of making those tables available to users ? like may be using something like NOLOCK ?
Thanks and Regards,
Paul

Are you saying the it takes 5 minutes to update the table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

pwalter83 SSChampion Points: 14571 More actions · Answer 7

Are you saying the it takes 5 minutes to update the table?

Hi Jeff,

Well, thats a good point which I didnt realize. I thought maybe if the table is just being appended, it should'nt take more than a minute or two for the job to complete. May be I could be wrong.

Do you have a better idea to achieve this ?

Thanks and Regards,

Paul

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 8

Without knowing details it's hard to say.

Check the current behaviour. Is there significant blocking? Can the queries be optimised? Is there still significant blocking after doing so?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

tfifield SSCrazy Eights Points: 9655 More actions · Answer 9

pwalter83 (3/15/2011)
Thanks again. atually there is no stored procedure to be used…I just want to add some functionality in the table that even if the records are added or deleted from it, the user is still able to access it..in other words they are not blocked when the process of updating is going on… do you know of something which can be applied to the tables ?
The thing is this table is linked to a web site. so what I want is that even if the table udpate is going on, the user is able to view the data in the web site(through the linked table). It should not become unavailable for the user while it is being updated.
Thanks and Regards,
Paul

Paul,

If you have any say about how the web site operates you may want to suggest that the web site use only stored procedures to access the table. It's far cleaner, the procedures are very easy to write and debug and you can do what you want with isolation levels. It's also way more secure.

From my experience not all web programmers' queries that are optimum in any case.

Todd Fifield

AmolNaik SSCarpal Tunnel Points: 4767 More actions · Answer 10

If your concern is table getting locked during duration of the SSIS Insert, apply the inserts in small number of batches and commit often, this will avoid lock escalation on the table. Try that approach and see if it helps you.

Thanks,

Amol Naik