Re: Using transactions to insert into to a table while allowing re

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Sean Aitken (sean.aitken_at_tekelec.spamtrap.com)
Date: 11/10/04 

Date: Wed, 10 Nov 2004 14:51:08 -0500

I must admit, that is a fascinating solution. So, then is it safe to
guess that this solution would be using a 'READ UNCOMMITTED' isolation
level?

Two problems with going that route:
  1. There are a lot of applications using this table, and unless we
could change the default transaction isolation level for the table, it's
impractical to modify all the apps to change isolation level.
  2. Wouldn't this only show the current set of data, based on how far
along the transaction is? Our process is: drop current data, insert 1600
rows. This would mean that it's possible for users to get partial data? no?

Great suggestion, and I've noted it for future use. I think all said
and done, using a temporary table is the least invasive way to deal with
the problem. We'll see.. :0)

Cheers!!
-Sean

Alejandro Mesa wrote:

> Correction,
>
> select * from t where ident_col <= (select top 1 colA from t1) and ...
>
>
> AMB
>
> "Alejandro Mesa" wrote:
>
>
>>This is an idea, could be good or bad.
>>
>>If you have an identity column in that table and another table where you
>>store the last identity inserted, you can change the lookup queries to:
>>
>>select * from t where ident_col < (select top 1 colA from t1) and ...
>>
>>and the transaction will not affect the lookup queries unless the lock scale
>>to table lock.
>>
>>Example:
>>
>>use northwind
>>go
>>
>>create table t (
>>colA int not null identity(1, 1) unique,
>>colB int null,
>>colC char(1) null
>>)
>>
>>create table t1 (
>>colA int not null
>>)
>>
>>insert into t default values
>>insert into t default values
>>insert into t default values
>>insert into t default values
>>insert into t default values
>>insert into t default values
>>insert into t default values
>>insert into t default values
>>insert into t default values
>>insert into t default values
>>insert into t default values
>>
>>insert into t1 values(scope_identity())
>>
>>go
>>
>>declare @i int
>>
>>set @i = 0
>>
>>begin transaction
>>
>>-- when executing the while, create a new connection and execute
>>-- select * from t where colA < (select top 1 colA from t1)
>>
>>while @i < 300000
>> begin
>> insert into t default values
>> set @i = @i + 1
>> end
>>
>>update t1 set colA = (select max(colA) from t)
>>
>>commit transaction
>>go
>>
>>drop table t, t1
>>go
>>
>>
>>
>>AMB
>>
>>"Sean Aitken" wrote:
>>
>>
>>>Hello,
>>>
>>>Sorry for the long subject, but this is a very intersting problem I am
>>>having. I am faced with the following situation:
>>>
>>>- Single table used by various applications for read-only lookups
>>>- Updates to that table are slow due to network latency (~5 minutes)
>>>
>>>I am updating the table by beginning a transaction, performing many
>>>inserts, then finally committing the transaction. The problem I am
>>>encountering is that during the entire transaction, this table is locked
>>>for the duration of the transaction. This is preventing the consumer
>>>apps (there are quite a few of them, over 1,000 users) from SELECT'ing
>>>any data, as there is a block on the table.
>>>
>>>I have tried using batch inserts (I am using a JDBC interface), setting
>>>various transaction isolation levels, and am about to resort to
>>>inserting into a temporary table and bulk-copying the data after the insert.
>>>
>>>What I would like to achieve is the 5 minute update, and allow users to
>>>see the data as it existed prior to the beginning of my transaction.
>>>Once I commit, suddenly, they would see new data. I can understand the
>>>situation if two transactions begun and overlapped, one would have to
>>>lose, but in this case, the table is only read only to the consumer apps.
>>>
>>>It seems that this is a feasable thing to expect, but I can't figure out
>>>if it is a limitation of the technology or my own ignorance.
>>>
>>>Any help, pointers, ideas GREATLY appreciated!
>>>
>>>Thank you,
>>>-Sean
>>>

Relevant Pages

  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... is possible for others to add other new orders to the database which I will ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Re: Transaction Isolation Level
    ... New rows must not be added to either table A and B while my transaction ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Re: transactions on reads?
    ... in the Books Online under the topic "Cursor Transaction ... Isolation Levels" it ... > column in the Invoice table, so there will never be any logical issue ...
    (microsoft.public.sqlserver.programming)
  • Re: ISOLATION LEVEL setting at different trancounts
    ... Say you have Two transaction, one on Isolation A. ... You will not need another connection, ... the ISOLATION LEVEL setting is set at the connection level. ...
    (microsoft.public.sqlserver.programming)