Imagine a data warehouse where you have many etl process from either a staging area or other external systems direclty.There are also many reading tasks that fill data marts for reporting purposes.
Imagine now you have to update many records because you discovered a bug and you cannot create a maintenance window since the DW has to be online 24/7.
So let us just do the update straight away, right? For that purpose we create a large table i.e. an eample fact table
CREATE TABLE dbo.demotable ( id INT IDENTITY NOT NULL, val1 BIGINT NOT NULL, val2 BIGINT NOT NULL, val3 BIGINT NOT NULL, val4 BIGINT NOT NULL, val5 BIGINT NOT NULL, val6 BIGINT NOT NULL, val7 BIGINT NOT NULL, val8 BIGINT NOT NULL, val9 BIGINT NOT NULL, val10 BIGINT NOT NULL, val11 BIGINT NOT NULL, val12 BIGINT NOT NULL );....
The default transaction level in sql server 2012 is READ COMITTED. That transaction level uses a pessimistic approach meaning readers can block writers, writers can block writers and writers can block readers.
Is it basically the opposite approach of optimistic approaches like providing row versions i.e. snapshot isolation level.
However in this session we want to focus on how SQL Server maintains consistency in a multi-user environment. The answer is simple: locks.
But how are locks actually handled. Well there is a bunch of information about locking.
First there are locks and latches. Most locks are being held until the end of transactions.
Latches are being held until the specific operation is over. Locks are used to maintain logic consistency. Latches are used to maintain physical consistency.
We will focu ....