Sql-server – Setting isolation level at database level

configurationdata-warehouseisolation-levelsql server

We have a data warehouse that's being dropped and reproduced every night. We have lots of SSRS reports that use the warehouse. Sometimes an SSRS report goes wrong and will never execute, thus preventing our warehouse from being filled up.

I can easily go with dirty reads for that one (data isn't changing anyway) so I figured out we can just go to READ UNCOMMITTED transaction level. And now I've encountered a problem I simply cannot believe:

Is it really not possible to change a database's default isolation level? What would be the reason for that?

It just doesn't make sense. I want my default isolation level to be READ UNCOMMITTED. I really don't want to go to each SSRS report and set it's transaction isolation level separately. We have lots and lots of SSRS reports.

I could enable RCSI but it will only unnecessarily slow the database down. Handling the reports in such a way is pretty much impossible. We have lots of SSRS reports that are written very poorly (in the graphical designer…) and we don't have time to fix them all.

I'm going through the process when in abundance of free time but that doesn't happen often. Having the warehouse not fill up is a very serious problem to us. For now all our problems could be fixed by changing the default isolation level.

The data warehouse is being filled up at night when nobody is using it
The whole day no data gets into the DW and we work with offline data up to -1 day.

So in our model there's really no concurrent writing and reading unless a query gets stuck and somebody forgets about it and leaves it trying to run.

Any solutions?

Best Answer

There are better ways to handle this than dirty reads. READ UNCOMMITTED queries will still block DDL, like TRUNCATE TABLE, DROP TABLE and ALTER TABLE … SWITCH, so it might not even help.

First, of course, is RCSI which is the basic correct solution for concurrently loading and reading from a database (even a Data Warehouse). It does not unnecessarily slow down your loads. It does what is necessary to enable concurrent readers and writers. And RCSI only creates row versions for UPDATE and DELETE. INSERT does not generate row versions unless the table has a trigger.

Second, if you want to reload the data warehouse while reports are running READ UNCOMMITTED, they will get wrong results. So you might as well just KILL the connected sessions, or have your ETL job alter database current set single_user with rollback immediate.

Third, you can use a Database Snapshot to keep the DW available for reads while you are performing ETL. You can additionally use the snapshot to roll back (restore) a failed ETL process even if it didn't run in a single transaction.

Related Solutions

Db2 – Isolation Level for a Data Warehouse

For most databases including Data Warehouses Read Committed is a sufficient isolation level. I wouldn't use Read Uncommitted due to the possibility of incorrect answers. On SQL Server we can also add Read Committed Snapshot Isolation. This versions rows and avoids queries being blocked by updates. Don't know if you have something similar on DB2.

Postgresql – Read Committed Isolation Level

The difference lies between a query and a transaction. A transaction can contain any number of queries. To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'. On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.) Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.

Best Answer

Related Solutions

Db2 – Isolation Level for a Data Warehouse

Postgresql – Read Committed Isolation Level

Related Question