Read Only Transactions
-
Hi, Our company writes simulation and optimisation software. Because of that, we need: 1. a consistent view of the data 2. fast access to the data Therefore all relevant data is loaded from the database at once, and all optimisation and simulation is performed on the data in memory. We do this since the late 80s on databases like Vax/Rdb, Ingres and Oracle. All of these databases offer the possibility of starting a Read-Only Transation, i.e. all queries performed in the transaction 'see' the data like it was at the start of the transaction, even if it was modified by some other user in the mean time. We are now considering Microsoft's SQL Server, but apparently it does not offer the possibility of Read-Only transactions. This means that if our application starts up and wants to load all relevant data of the database, it has to use a 'serializable' transaction (actually putting a Read lock on all the tables) to be sure that consistent data is read. This means that: - during a start-up, nobody can save - during a save, nobody can start-up I'm stunned to see that SQL Server does not offer Read-Only transactions. Probably most of the [database] world is developing OLTP-type applications, but somewhere there must be somebody writing applications that need to load big parts of the database. How do these people solve that problem? Did anybody encounter this problem also and has found a solution for it? Thanks in advance. Enjoy life, this is not a rehearsal !!! My Articles: - Implementing a Subject/Observer pattern with templates - Different ways of writing class factories - AutoRunner: a template class to automatically run start- and cleanup-code in code blocks
-
Hi, Our company writes simulation and optimisation software. Because of that, we need: 1. a consistent view of the data 2. fast access to the data Therefore all relevant data is loaded from the database at once, and all optimisation and simulation is performed on the data in memory. We do this since the late 80s on databases like Vax/Rdb, Ingres and Oracle. All of these databases offer the possibility of starting a Read-Only Transation, i.e. all queries performed in the transaction 'see' the data like it was at the start of the transaction, even if it was modified by some other user in the mean time. We are now considering Microsoft's SQL Server, but apparently it does not offer the possibility of Read-Only transactions. This means that if our application starts up and wants to load all relevant data of the database, it has to use a 'serializable' transaction (actually putting a Read lock on all the tables) to be sure that consistent data is read. This means that: - during a start-up, nobody can save - during a save, nobody can start-up I'm stunned to see that SQL Server does not offer Read-Only transactions. Probably most of the [database] world is developing OLTP-type applications, but somewhere there must be somebody writing applications that need to load big parts of the database. How do these people solve that problem? Did anybody encounter this problem also and has found a solution for it? Thanks in advance. Enjoy life, this is not a rehearsal !!! My Articles: - Implementing a Subject/Observer pattern with templates - Different ways of writing class factories - AutoRunner: a template class to automatically run start- and cleanup-code in code blocks
It looks like you need to search in Books Online for Transaction Isolation Levels. More specifically, the SERIALIZABLE isolation level that prevents dirty reads.
-
It looks like you need to search in Books Online for Transaction Isolation Levels. More specifically, the SERIALIZABLE isolation level that prevents dirty reads.
Thanks, that would seem acceptable for applications that only need to load a few records from the database. In our situation, lots of records are loaded; in some situations, the whole database is loaded. We're speaking of about 100,000 records, sometimes even millions of records. Preventing updates while loading the database, or even worse, preventing applications from starting up as long as someone is saving is not acceptable in our situation. Do you know if the next version of SQL Server (is this SQL Server 2003?) will have the concept of read-only transactions? Thanks. Enjoy life, this is not a rehearsal !!! My Articles: - Implementing a Subject/Observer pattern with templates - Different ways of writing class factories - AutoRunner: a template class to automatically run start- and cleanup-code in code blocks