MS SQL Data Warehousing - Select And Insert

Author

Select And Insert

Travis

2005-09-01, 7:23 am

Hi ,

I am creating a report for user from our live database , if user are
quering on my reports and some of the user are insert or updating on the live
database , will the table lock from any insert or updations ? Any solutions
of that if the db do lock the table .. TQ

--
Travis Tan

Guilbert

2005-09-02, 1:23 pm

Two things:

1) If there is a lock the table should still be available for reading.

2) You should not really be creating reports against live data.

I assume this is a transaction system, if so doing end user queries
will slow the system down and impact your business.

You should create a copy, a data mart or data warehouse, for
doing end user queries.

Travis

2005-09-05, 7:23 am

Hi Guilbert ,

This is the live database. Will it affect the data updating or insert
when the report is running ?

--
Travis Tan

"Guilbert" wrote:

> Two things:
>
> 1) If there is a lock the table should still be available for reading.
>
> 2) You should not really be creating reports against live data.
>
> I assume this is a transaction system, if so doing end user queries
> will slow the system down and impact your business.
>
> You should create a copy, a data mart or data warehouse, for
> doing end user queries.
>
>
>

Darren Gosbell

2005-09-07, 3:23 am

It depends on a number of factors like the Isolation levels and the
nature of your query, but the short answer is yes, it is possible that a
select statement can lock records/tables that will affect updates and
inserts.

If you absolutely must query the live data, but don't want minimal
impact on inserts and updates AND you are willing to accept the
possibility of slight data inconsistancies, then you can do what is
known as a "dirty read". You do this by adding a (WITH NOLOCK) hint
after the table reference(s).

eg. SELECT *
FROM <table1> (WITH NOLOCK)

WARNING: The locking behaviour of SQL Server is there for your
protection. Turning it off can lead to incosistant results.

For example, if you inner join 2 tables to produce a list of contacts
and halfway through the operation a company name gets updated in the
company table, what you can end up with is rows that were resolved
during the first part of the query are linked to the old company name
and those resolved during the second half of the query linking to the
new company name. This would make it appear like there were 2 different
companies. Currently the locking behaviour will protect you from this by
blocking the completion of the update statement until the select
statement has completed.

PS. SQL 2005 gives you another option here by introducing snapshot
isolation, but SQL 2005 is not due to RTM until Nov 7th.

--
Regards
Darren Gosbell
< dgosbell_at_yahoo_do
t_com>

In article <A6EE89A6-CA5A-4004-9862- 8DE93B7DCEC6@microso
ft.com>,
Travis@discussions.microsoft.com says...
> Hi Guilbert ,
>
> This is the live database. Will it affect the data updating or insert
> when the report is running ?
>
>

Jesse O

2005-09-14, 8:23 pm

This will create dirty reads.

Always best to try and never report on a table like this.

"Travis" <Travis@discussions.microsoft.com> wrote in message
news:52D662FC-6120-4D29-B31E- 1A083C9657C0@microso
ft.com...
> Hi ,
>
> I am creating a report for user from our live database , if user are
> quering on my reports and some of the user are insert or updating on the
> live
> database , will the table lock from any insert or updations ? Any
> solutions
> of that if the db do lock the table .. TQ
>
> --
> Travis Tan

Sponsored Links