|
|
Posted by dhek on 11/04/07 09:04
ok, let me try to be more clear.
The thing is I have a database containing various types of information - for
instance orders.
Instead of letting each record in an order contain a timestamp indicating
the datatime the record was replicated to another system, I have a table
containing replication times for various types of objects.
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
never be aware of.
- in my transaction after having gotten new orders I set a timestamp
indicating the datetime of my last replication. If someone has added new
records meanwhile I executed my select statement and performed the update
statement, then these new records will never be replicated because their
timestamp will be less than than the one I set in my update-statement when
finished.
Hope this clearfies my issue.
"dhek" <dhek@dhek.dk> wrote in message
news:472c5c63$0$90274$14726298@news.sunsite.dk...
> Well, my issue is that I'm reading data from tables A, B, C and D and
> updates table E.
>
> What I need to prevent is 2 things:
>
> 1) New rows must not be added to either table A and B while my transaction
> is executing
> 2) Existing rows must not be modified
>
> My query is executed on a SQL server 2005. I do not have the option to
> change the configuration of it.
>
> Am I supposed to use table locks in my query
>
> SELECT A.c1, B.c1, C.c1, D.d1
> FROM ...
> WHERE ...
> WITH (TABLOCK, UPDLOCK, HOLDLOCK)
>
> UPDATE E.1
> SET ...
> WHERE ...
>
>
>
> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message
> news:Xns99DD791FBE224Yazorman@127.0.0.1...
>> dhek (dhek@dhek.dk) writes:
>>> I have 1 SQL statement selecting data from various tables and updating
>>> other tables.
>>>
>>> The question then is how do I prevent other applications from modifying
>>> the tables that I'm working on (that is while my transaction is being
>>> executed)?
>>>
>>>
>>> I know that the isolation level should be either REPEATABLE READ or
>>> SERIALIZABLE. But I need confirmation on if one of these actually solve
>>> my
>>> issue - prevents other applications/threads from modifying/inserting
>>> data
>>> into the same tables that I'm working on.
>>
>> It's difficult to give a single answer, since I don't know your exact
>> requirements, so I have to answer in genric terms.
>>
>> If you want a consistent snapshot of how the data looks in this precise
>> moment, the isolation level you should use is snapshot isolation.
>> Snapshot
>> isolation is available only in SQL 2005 and later. Furthermore the
>> database
>> must be configured to permit snapshot isolation. When you have snapshot
>> is
>> created when the transaction starts, or at latest when you start to read
>> data. If data is updated while your query runs, you will not see these
>> updates. This gives you a consistent view - but it may also give you
>> outdated data, depending on how you look at it.
>>
>> On SQL 2000, snapshot isolation is not available, and the only foolproof
>> way to get consistent data, is to set the database in single-user mode.
>>
>> In the default isolation level, READ COMMITTED, if you read the same
>> row twice, you may get different results in different accesses. For
>> instance, if you run:
>>
>> SELECT O.OrderID, E.EmployeeID, E.LastName
>> FROM Orders O
>> JOIN Employees E ON O.EmployeeID = E.EmployeeID
>>
>> You may see different last names for the same employee ID if the query
>> plan uses a loop join, and the last name is updated while the query is
>> running.
>>
>> In the next level, REPEATABLE READ locks are held, and you are guaranteed
>> that reading the same row twice will yield the same result. However, if
>> the last name of employee 8 was Grψnkjζr when the query started, and
>> updated to Gravesen before you have read any orders with employee 8,
>> you would see Gravesen in the result set.
>>
>> SERIALIZABLE adds protection against "phantom insert", so if you read the
>> same range twice, you will get the same result. That is, if you run
>> SELECT MAX(OrderID) FROM Orders twice in the same serializable
>> transaction,
>> you will get the same result. But if a order is added after you started
>> the
>> transaction, but before your query runs, the order will show up.
>>
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>>
>> Books Online for SQL Server 2005 at
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> Books Online for SQL Server 2000 at
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
>
[Back to original message]
|