|
|
Posted by Erland Sommarskog on 11/04/07 17:38
dhek (dhek@dhek.dk) writes:
> 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.
Just to make things clear: is this the timestamp data type, or a datetime
value? (It sounds like the latter, but I want to be sure.)
From this description, it sounds that SERIALIZABLE would do the job, but
I think there is a better solution. Do this:
SELECT @mynewtimestamp = getdate()
SELECT ... FROM tbl
WHERE regdate BETWEEN @myoldtimestamp AND @mytimestaamp
By first determining the upper limit of the span you will read,
there is no problem with rows that are added later, because they will
be outside your range.
I would strongly recommend against using serializable, because if
conflicting updates really is a seroius possibility, using serializable
is likely to wreak havoc with the concurrency of the system. Your
replication may work, but if people can place orders while your
replication is running, that can be a serious problem.
An interesting observation is that as I understand you scenario is
that snapshot isolation would *not* work, despite what I said before.
With snapshot you would miss rows that had been inserted when you started
reading, but which had not been committed.
This shows just how important it is to give a completely picture of the
problem you are trying to solve.
--
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
Navigation:
[Reply to this message]
|