Ayende @ Rahien

It's a girl

New Profiler Feature: Avoid Writes from Multiple Sessions In The Same Request

Because I keep getting asked, this feature is available for the following profilers:

This new feature detects a very interesting bad practice, write to the database from multiple session in the same web request.

For example, consider the following code:

public void SaveAccount(Account account)
{
    using(var session = sessionFactory.OpenSession())
    using(session.BeginTransaction())
    {
           session.SaveOrUpdate(account);
           session.Transaction.Commit();    
    }
}
public Account GetAccount(int id)
{
    using(var session = sessionFactory.OpenSession())
    {
        return session.Get<Account>(id);
    }
}

It is bad for several reasons, micro managing the session is just one of them, but the worst part is yet to come…

public void MakePayment(int fromAccount, int toAccount, decimal ammount)
{
    var from = Dao.GetAccount(fromAccount);
    var to = Dao.GetAccount(toAccount);
    from.Total -= amount;
    to.Total += amount;
    Dao.SaveAccount(from);
    Dao.SaveAccount(to);
}

Do you see the error here? There are actually several, let me count them:

  • We are using 4 different connections to the database in a single method.
  • We don’t have transactional safety!!!!

Think about it, if the server crashed between the fifth and sixth lines of this method, where would we be?

We would be in that wonderful land where money disappear into thin air and we stare at that lovely lawsuit folder and then jump from a high window to a stormy sea.

Or, of course, you could use the profiler, which will tell you that you are doing something which should be avoided:

image

Isn’t that better than swimming with the sharks?

Comments

Pl&#225;cido Bisneto
03/01/2011 12:40 PM by
Plácido Bisneto

Ayende,

I don't know if it's a "problem" in the NHProf, but when using LINQ to perform queries, when I use the FirstOrDefault(), the profiller shows me an alert saying that "the min/max number of records was not specified. doing it in memory". It does not happen when I use SingleOrDefault().

Is it the expected behaviour??

Ayende Rahien
03/01/2011 02:23 PM by
Ayende Rahien

Bisneto,

What SQL is generated for each?

Pl&#225;cido Bisneto
03/01/2011 04:47 PM by
Plácido Bisneto

Hi!

Sorry for the delay... The SQL statements are the same...

The LINQ query is:

            var loginResult = (from u in this.Context.Session.Query

<user()

                               where u.Login == username && u.Password == password

                               select u)

                               .FetchMany

<user,> (z => z.Wallets)

                               .SingleOrDefault();

just changing the Single for First....

This is the SQL generated using SingleOrDefault():

select user0.UserId as InvestorId40_,

   wallets1_.WalletId             as WalletId8_1_,

   user0_1_.Version               as Version4_0_,

   user0_1_.CreatedOn             as CreatedOn4_0_,

   user0_.FirstName               as FirstName6_0_,

   user0_.LastName                as LastName6_0_,

   user0_.Email                   as Email6_0_,

   user0_.LastLogOn               as LastLogOn6_0_,

   user0_.CreatedOn               as CreatedOn6_0_,

   user0_.UpdatedOn               as UpdatedOn6_0_,

   user0_.Login                   as Login6_0_,

   user0_.Password                as Password6_0_,

   user0_.RecoveryPasswordEmail   as Recover10_6_0_,

   wallets1_.CreatedOn            as CreatedOn8_1_,

   wallets1_.UpdatedOn            as UpdatedOn8_1_,

   wallets1_.WalletDescription    as WalletDe4_8_1_,

   wallets1_.WalletName           as WalletName8_1_,

   wallets1_.WaintingForOperation as Wainting6_8_1_,

   wallets1_.CreationValue        as Creation7_8_1_,

   wallets1_.CurrentBalance       as CurrentB8_8_1_,

   wallets1_.InvestorId           as InvestorId8_1_,

   wallets1_.InvestorId           as InvestorId0__,

   wallets1_.WalletId             as WalletId0__

from Users user0_

   inner join Investors user0_1_

     on user0_.UserId = user0_1_.InvestorId

   left outer join Wallets wallets1_

     on user0_.UserId = wallets1_.InvestorId

where user0_.Login = 'cidico' /* :p0 */

   and user0_.Password = '123' /* :p1 */

This is the SQL generated using FirstOrDefault():

select user0.UserId as InvestorId40_,

   wallets1_.WalletId             as WalletId8_1_,

   user0_1_.Version               as Version4_0_,

   user0_1_.CreatedOn             as CreatedOn4_0_,

   user0_.FirstName               as FirstName6_0_,

   user0_.LastName                as LastName6_0_,

   user0_.Email                   as Email6_0_,

   user0_.LastLogOn               as LastLogOn6_0_,

   user0_.CreatedOn               as CreatedOn6_0_,

   user0_.UpdatedOn               as UpdatedOn6_0_,

   user0_.Login                   as Login6_0_,

   user0_.Password                as Password6_0_,

   user0_.RecoveryPasswordEmail   as Recover10_6_0_,

   wallets1_.CreatedOn            as CreatedOn8_1_,

   wallets1_.UpdatedOn            as UpdatedOn8_1_,

   wallets1_.WalletDescription    as WalletDe4_8_1_,

   wallets1_.WalletName           as WalletName8_1_,

   wallets1_.WaintingForOperation as Wainting6_8_1_,

   wallets1_.CreationValue        as Creation7_8_1_,

   wallets1_.CurrentBalance       as CurrentB8_8_1_,

   wallets1_.InvestorId           as InvestorId8_1_,

   wallets1_.InvestorId           as InvestorId0__,

   wallets1_.WalletId             as WalletId0__

from Users user0_

   inner join Investors user0_1_

     on user0_.UserId = user0_1_.InvestorId

   left outer join Wallets wallets1_

     on user0_.UserId = wallets1_.InvestorId

where user0_.Login = 'cidico' /* :p0 */

   and user0_.Password = '123' /* :p1 */
Pl&#225;cido Bisneto
03/01/2011 04:49 PM by
Plácido Bisneto

Your blog has removed the "greater than" and "less than" from my post.

I was using the generic method Query() and FetchMany()

:)

NC
03/01/2011 09:24 PM by
NC

Don't use Query <t, it's highly buggy.

Using MAX/MIN causes it to do casting, using the Criteria stuff it works perfectly. Theres too many bugs around Query, it's definitely not ready for production.

Rafal
03/02/2011 08:55 AM by
Rafal

If someone doesn't understand how to handle transactions he shouldn't be allowed to write any code. So maybe the next useful function would be to make profiler find such guys and exterminate them. Ayende, how does this fit into your design?

flukus
03/02/2011 09:56 AM by
flukus

I find this everywhere people are using MS Enterprise Library. Every method of every DAO will contain Database.CreateConnection().

I much prefer having the connection passed around explicitly instead of hidden away,

Govind
03/10/2011 02:57 PM by
Govind

Should this not be closer to db in a stored proc where multiple connection problem does not arise?

Ayende Rahien
03/10/2011 09:02 PM by
Ayende Rahien

Govind,

If you are using the ORM properly, it is not an issue

RichB
03/21/2011 12:36 PM by
RichB

What would happen if a single web request updated data in multiple sessions, but they were against different data sources? eg different servers, different databases or even different DB schemas?

I would expect that scenario to not report an error.

Having multiple schemas in the same database is very useful for partitioning the security domain from application domain in a defense-in-depth way. Yet both domains may need to be updated in the same request.

Comments have been closed on this topic.