|
|
Posted by Greg D. Moore \(Strider\) on 11/04/07 13:11
"dhek" <dhek@dhek.dk> wrote in message
news:472d8b03$0$90272$14726298@news.sunsite.dk...
> 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.
Why not add such a column with a timestamp then?
>
> 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.
If you're simply looking to do replication, use SQL Server's built in
replication functionality.
It may save you a lot of effort.
>
> 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
>>
>>
>
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
[Back to original message]
|