SQL Server's Read Committed Snapshot Isolation

Last update 10 Sep 2017.

I got familiar with Oracle and PostgreSQL databases before I got familiar with SQL Server. When I started using SQL Server I wondered why a condition that would be fine in PostgreSQL would cause problem in SQL Server. Here is an example.

First let’s set up the environment.

create table Product(
    ID      int,
    Code    nvarchar(20),
    Price   bigint
)

insert into Product(ID, Code, Price) values(1, 'A', 200)
insert into Product(ID, Code, Price) values(2, 'B', 100)

Suppose there are two active sessions. One of them is starting first, running this:

begin transaction

-- pretend this is an update process that runs a long time
update Product set Price = 300 where ID = 2

-- should have commit at the end but not yet there

While the other session is running this:

select * from Product where ID = 1

In PostgreSQL the select query would return immediately while in SQL Server it would wait until the update is committed before returning.

This would cause problem if we need to generate reports when the database is actively used for transactions. Either the report would take a very long time to finish or the transactions would have to wait for the report to finish. This last one can be really bad on a busy day.

Back then I found several workarounds from the internet. One suggestion is to use the with (nolock) hint. So the select query becomes:

select * from product with (nolock) where ID = 1

That would work in this case, but the select query would do something called “dirty” read, which in this case would include data modifications from other sessions even if the transaction is not yet committed. Continuing our example, if the first session is running:

begin transaction
update Product set Price = 300 where ID = 2

-- select query from second session would start here

update Product set Price = 500 where ID = 2
commit

And the second session starts between the first update and the second update:

select * from product with (nolock)

It would show the price of product 2 as 300 instead of 100 if the transaction on the first session is rolled back or 500 if the transaction is committed.

By the way PostgreSQL would show 100 as the price of product 2.

Also, sprinkling nolock hint all over the source code is considered bad practice by a lot of people.

Another workaround I found from googling was to create archive database. So we create a second database, copy the data from the original database to this second database periodically, and all report generation should use the second database, leaving the first database dedicated for transaction. This is, of course, a MUCH more complicated solution that requires a lot of effort.

What I found next was the REAL solution.

It turned out SQL Server actually has a feature called read committed snapshot isolation, which by default is not turned on! After I turned on snapshot isolation, SQL Server behaved just like PostgreSQL on situation like above.

With read committed snapshot isolation switched on, SQL Server will save the original data in tempdb when a transaction starts modifying data, and if another session sends a select query, as long as the transaction in the first session is not yet done, SQL Server will return the unmodified data from tempdb. This is oversimplification of course. Better consult the official documentation if you want to know the exact mechanism.

Bottom line is, select queries and data modifications no longer block one another.

Read committed snapshot isolation can be turned on like so:

-- kick out all other users with 2 minutes grace period
alter database mydb set single_user with rollback after 120 seconds

-- turn on read committed snapshot isolation
alter database mydb set read_committed_snapshot on

-- allow everyone else to reconnect
alter database mydb set multi_user

To check whether read committed snapshot isolation is turned on:

select name, is_read_committed_snapshot_on from sys.databases

If you are creating a new database, I strongly suggest turning on read committed snapshot isolation from the beginning. But for a database in production, you’d better learn more about it before turning it on since for certain condition update result will be different.

Comments


Mengurus Akta Kelahiran yang Hilang 2017

Last update 22 Aug 2017.

Catatan pribadi pada saat saya harus membuat ulang akta anak karena aslinya hilang, barangkali di masa depan hilang lagi :)

Saya mencoba mengurus di Suku Dinas Kependudukan dan Catatan Sipil Jakarta Barat tetapi ternyata tidak bisa karena akta nya tahun 2007. Untuk tahun 2007 harus ke Dinas Kependudukan dan Pencatatan Sipil Provinsi DKI Jakarta.

Yang harus dibawa:

Tidak butuh meterai 6000.

Membuat surat pernyataan kehilangan bisa di polsek (Kepolisian Sektor). Waktu itu saya membuat di Kepolisian Sektor Tanjung Duren karena lumayan dekat dengan kantor DisDukCaPil nya. Biaya pembuatan surat hilang menurut polisinya ‘terserah’.

Menurut petugas waktu proses 2 minggu lebih. Saya menyerahkan berkas tanggal 22 Agustus 2017 dan diberi semacam kuitansi untuk mengambil dokumen waktu sudah jadi. Menurut petugas saya bisa kembali untuk mengambil akta yang baru tanggal 7 September 2017. Petugas memberikan nomor handphone yang menurut dia bisa ditanya melalui whatsapp apakah dokumen sudah bisa diambil (untuk menghindari kita datang tetapi dokumen belum bisa diambil), tetapi saya coba tanya tanggal 6 September 2017 melalui whatsapp tidak mendapat jawaban.

Tanggal 7 September 2017 saya datang ke kantor catatan sipil. Ambil nomor antrian. Waktu dipanggil menyerahkan kuitansi. Disuruh menunggu sebentar, kemudian dipanggil dan diberi dokumen akta yang baru. Saya disuruh fotokopi selembar untuk keperluan arsip mereka. Tempat fotokopi ada di kantin di belakang gedung. Saya fotokopi satu, kembalikan ke petugas, oleh petugas disuruh tanda tangan dan tulis nama dan nomor handphone di fotokopi, dan urusan selesai. Tidak dimintai biaya.

Comments


The Many Ways to Call Linq SelectMany

Last update 27 Jul 2017.

I just found out c#’s Enumerable.SelectMany has 4 overloads! I only knew one.

Now figuring out the differences.

Setting up a playing field:

public class Team
{
    public string Name { get; set; }
    public IEnumerable<string> Members { get; set; }
}

public class MatchResult
{
    public Team Team { get; set; }
    public int Score { get; set; }
}

And then let’s create a list of match results:

var results = new List<MatchResult>
{
    new MatchResult
    {
        Team = new Team {Name = "Red", Members = new[] {"Wedge", "Luke"}},
        Score = 8
    },
    new MatchResult
    {
        Team = new Team {Name = "Gold", Members = new[] {"Evaan"}},
        Score = 7
    }
};

Now if I want to get a list of team member with their team score, previously I would do it like this:

var memberScores = results.SelectMany(
    r => r.Team.Members.Select(m => new { Member = m, Score = r.Score })).
    ToList();

And I would get:

[
    { Member = "Wedge", Score = 8 },
    { Member = "Luke", Score = 8 },
    { Member = "Evaan", Score = 7 }
]

But it turned out there is another way to do it:

var memberScores = results.
    SelectMany(
        result => result.Team.Members,
        (result, member) => new { Member = member, Score = result.Score }).
    ToList();

Which is easier to read I think.

The other two overloads are just to add item index if you need it:

var memberScores = results.SelectMany(
    (r, idx) => r.Team.Members.Select(m => new { Member = m, Score = r.Score })).
    ToList();

And

var memberScores = results.
    SelectMany(
        (result, idx) => result.Team.Members,
        (result, member) => new { Member = member, Score = result.Score }).
    ToList();

Comments


Changing Generator Oil

Last update 18 Dec 2016.

I have a diesel generator. The type is Maxtron MX 6500 D, capacity 5 KVA. Recently I had it serviced, oil change and cleaning the filter, and was quite shocked by the bill.

This is my personal note on how to change the oil so next time I’ll do it myself. Perhaps other owners of MX 6500 D may find this useful too. Maybe.

diagram

First put a tray under the oil drain (under A).

Open the oil intake cap (see B).

Open the oil drain cap (see C). It needs wrench size 17 and 22 if I’m not mistaken.

Let the oil drain.

For cleaning the oil filter, open the lid (see D) with wrench and pliers. Wash the filter with carburetor cleaner spray, then put it back on.

The type of oil used is SAE 40.

Put funnel in the intake hole (B), pour a small amount of oil. Pour until the oil coming out of the drain is clean.

Close drain (C).

Pour oil while regularly checking the oil level with the stick. If you put too much oil, you have to reopen the drain to let some oil out.

Close intake cap (B).

Remove tray, dispose the oil waste.

That’s it for oil change.

Other thing that needs to be maintained is the battery. It uses 35 ampere battery. If you use lead acid battery, don’t forget to check the water and refill as necessary.

Comments


FirstMedia Schedule

Last update 06 May 2016.

My cable and internet provider FirstMedia offered me an upgrade for the internet connection from 10 Mbps to 18 Mbps, with bonus opening all TV channels for 6 months. I said yes.

So now I have all these channels and can’t find a convenient way to check what’s on. The program guide on the TV has to be manipulated by remote. FirstMedia has a TV guide online but the display is hard to read, difficult to search, and really not mobile device friendly.

Determined to have an easy way to check the schedule, I spent this weekend building a web page that displays the shows according to my preferences. The data is taken from the original online TV guide :)

It was also a chance for me to brush up my skills. I used bootstrap 3 to help with the CSS, angular 1 for the javascript framework, font awesome 4 for the icons, lodash 4 to help with the javascript, moment.js 2 to help taming javascript date, angular ui router 0.2 for routing, and angular ui bootstrap 1.3 to connect angular to bootstrap. For the back end I used Nancy 1.4.

The result is not too shabby if I may say so myself :)

You can create several lists, each list contains a bunch of channels you want to display. For example you create a list called ‘Movies’, containing channels like HBO and Cinemax, and another list called ‘News’, containing CNN and Al Jazeera. You can choose which date to show, and whether to show past shows. The list can be grouped by channel or ordered by time. Also, it looks quite acceptable in mobile devices.

Check it out!

Comments