ayende – Challenges: Where is the optimization? – my thought

Few days ago, Ayende posted on his blog a challange.
You can read about it here http://ayende.com/Blog/archive/2010/02/16/challenge-premature-optimization-and-all-of-thathellip.aspx

[…]
In the end the question was:

bus.Send(
new MyBooksRequest{UserId = userId},new MyQueueRequest{UserId = userId},new MyRecommendationsRequest{UserId = userId});

Produces this SQL:

enlisted session in distributed transaction with isolation level: Serializable
SELECT user0_.Id          as Id2_0_, […]FROM   Users user0_
WHERE  user0_.Id = 1 /* @p0 */
SELECT currentlyr0_.[User] as User1_1_, […]FROM   UsersReadingBooks currentlyr0_
       left outer join Books book1_
         on currentlyr0_.Book = book1_.Id
WHERE  currentlyr0_.[User] = 1 /* @p0 */
SELECT queue0_.[User]  as User1_1_, […]FROM   UsersWaitingBooks queue0_
       left outer join Books book1_
         on queue0_.Book = book1_.Id
WHERE  queue0_.[User] = 1 /* @p0 */
SELECT recommenda0_.[User]  as User1_1_,[…]FROM   UsersRecommendedBooks recommenda0_
       left outer join Books book1_
         on recommenda0_.Book = book1_.Id
WHERE  recommenda0_.[User] = 1 /* @p0 */
commit transaction

That seems strange, can you figure out why?
[…]

My first answer was

# re: Challenge: Premature optimization, and all of that… 2/17/2010 12:06 AM simone basso

may be grouping all those queries into the same transaction might cause some more deadlocks?
I was asked to argument about my statement so here I’m.

I wrote a small program using the new Parallel Extensions in .net 4.0 http://blogs.msdn.com/pfxteam/ to simulate a heavily loaded system.
You can download it here http://cid-a117aa5e007a0648.skydrive.live.com/self.aspx/ProgrammingExamples/Alexandria-DeadLock.zip
It contains the script.sql from http://github.com/ayende/alexandria to generate the database

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
image

SET TRANSACTION ISOLATION LEVEL READ COMMITTEDimage

As you can see the problem is with the type of isolation used.

A transaction with Serializable Isolation places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. You can see that in the first case, the Lock time is quite high, while in the second case it’s zero.

This can have a huge impact on highly scalable applications and especially in web applications that require to often update and write on the same dataset.
I use to work at Bookarmy, where in fact we had a huge database (8+M books records) and a fair amount of users and connection between users/books/authors etc.

Almost immediately after the GOLIVE we had to update out LINQ Data Access Layer to trigger
td.ExecuteCommand(“SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED”);
for the most demanding queries, or split query batches in smaller queries, to reduce the amount of table’s areas locked by every transaction

References:
http://vyaskn.tripod.com/com_isolation_level.htm

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s