Re: Transaction Isolation Level

"dhek" <dhek@xxxxxxx> wrote in message
news:472d8b03$0$90272$14726298@xxxxxxxxxxxxxxxxxx
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@xxxxxxx> wrote in message
news:472c5c63$0$90274$14726298@xxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxx> wrote in message
news:Xns99DD791FBE224Yazorman@xxxxxxxxxxxx
dhek (dhek@xxxxxxx) 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@xxxxxxxxxxxxx

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


.

Relevant Pages

  • 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
    ... 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: Asynchronous Stored Procedure Never Returns - Help?
    ... If you have the Sql Server 2000 or 2005 docs they are thorough and can be ... for Transaction SQL Reference from the drop-down or select a keyword from ... your query in Query Analyzer or Sql Server Management Studio, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Transactions - Infinite Loops
    ... This still doesn't sound like an infinite loop. ... > It was an unusual SQL Server response that we were unable to duplicate. ... > transaction if the wrong number of felds are being updated (i.e., ... > the query ran okay. ...
    (microsoft.public.sqlserver.programming)