وحید نصیری وحید نصیری
مطالب
شروع به کار با EF Core 1.0 - قسمت 10 - استفاده از امکانات بومی بانک‌های اطلاعاتی
در قسمت بعد، ارتباطات self referencing را بررسی خواهیم کرد و چون EF Core هیچ راه حل بهینه‌ای را برای کوئری گرفتن از این نوع روابط سلسله مراتبی ارائه نمی‌دهد (درEF 6.x نیز به همین ترتیب)، نیاز است مستقیما SQL نویسی کرد. به همین جهت در این قسمت نحوه‌ی نوشتن کوئری‌های مستقیم SQL و اجرای آن‌ها را در EF Core بررسی می‌کنیم.


اجرای کوئری‌های خام SQL بر روی بانک اطلاعاتی، توسط EF Core

گاهی از اوقات نیاز به استفاده‌ی قابلیت خاصی از بانک اطلاعاتی مدنظر وجود دارد که توسط LINQ پشتیبانی نمی‌شود و یا کوئری SQL حاصل از LINQ to Entities آنچنان بهینه نیست. در یک چنین حالاتی راهی بجز نوشتن کوئر‌ی‌های خام SQL وجود ندارد. امکان اجرای یک چنین کوئری‌هایی توسط EF Core پیش بینی شده‌است؛ اما با این محدودیت‌ها:
 - خروجی کوئری SQL، تنها باید معادل یکی از کلاس‌های موجودیت‌های شما باشد. قرار است این محدودیت در نگارش 1.1 برطرف شود.
 - کوئری SQL نوشته شده باید تمام خواص موجودیتی را که قرار است به آن نگاشت شود، بازگشت دهد.
 - نام ستون‌های بازگشت داده شده‌ی توسط کوئری SQL باید با نام خواص موجودیت در حال کار، یکی باشند و برخلاف EF 6.x، از یک چنین عدم تطابق‌هایی صرفنظر نخواهد شد.
 -  کوئری SQL نوشته شده نباید به همراه اطلاعات ارتباطات موجودیت‌ها باشد.

در اینجا برای نوشتن کوئری‌های خام SQL می‌توان از متد FromSql مرتبط با یکی از DbSetهای برنامه استفاده کرد:
var blogs = context.Blogs
    .FromSql("SELECT * FROM dbo.Blogs")
    .ToList();
و یا حتی می‌توان از رویه‌ی ذخیره شده‌ای استفاده کرد که خروجی ستون‌های آن، معادل تمام خواص کلاس Blog باشد:
var blogs = context.Blogs
  .FromSql("EXECUTE dbo.GetMostPopularBlogs")
  .ToList();

بنابراین رفتار EF Core اندکی متفاوت است با EF 6.x. در اینجا اگر می‌خواهید از عبارت SQL خود خروجی بگیرید، باید از یکی از DbSetهای خود شروع کنید و متد FromSql را بر روی آن فراخوانی نمائید. همچنین کوئری نوشته شده باید اولا تمام ستون‌های آن DbSet رابازگشت دهد و به علاوه این ستون‌ها دقیقا با نام‌های خواص آن کلاس، تطابق داشته باشند.
علت این مسایل نیز به این دلیل است که بتوان نتیجه‌ی کوئری را به صورت خودکار وارد سیستم change tracking کرد و همچنین کوئری‌های ترکیبی LINQ را نیز در اینجا فعال کرد.


ارسال پارامترها به کوئری‌های خام SQL

تنها حالتی در EF Core که مستعد به حملات تزریق SQL است، دقیقا همین مورد دور شدن از LINQ و نوشتن عبارات مستقیم SQL است. در اینجا برای نوشتن کوئری‌های پارامتری دو حالت پیش بینی شده‌است:
الف) روش parameter place holders
در اینجا متد FromSql، بسیار شبیه به متد String.Format است، اما در عمل اینطور نیست و تمام place holders آن به صورت خودکار تبدیل به پارامتر می‌شوند:
var user = "johndoe";

var blogs = context.Blogs
  .FromSql("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
  .ToList();
ب) روش ساخت دستی DbParameterها
اگر می‌خواهید از پارامترهای نام دار استفاده کنید، با وهله‌ای از SqlParameter شروع کرده و سپس آن‌را به متد FromSql ارسال کنید:
var user = new SqlParameter("user", "johndoe");
var blogs = context.Blogs
  .FromSql("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
  .ToList();
و یا این حالت را به شکل ساده شده‌ی ذیل نیز می‌توان مورد استفاده قرار داد:
 var results = _context.Contacts.FromSql(
@"SELECT Id, Name Address, City, State, Zip 
    FROM Contacts 
    WHERE Name IN (@p0, @p1)", name1, name2);
که در اینجا p0@ به name1 و p1@ به name2 نگاشت خواهد شد.
مزیت کار کردن با SqlParameter این است که می‌توان برای مثال Direction و SqlDbType را نیز صریحا ذکر کرد (بسته به نوع پارامترهای رویه‌ی ذخیره شده):
var nameParameter = new SqlParameter
{
  ParameterName = "@name",
  Value = "doc",
  Direction = ParameterDirection.Input,
  SqlDbType = SqlDbType.NVarChar
};


امکان ترکیب کوئری‌های SQL و LINQ نیز پیش بینی شده‌است

در کوئری ذیل، قسمت select از جدولی به صورت SQL و قسمت where و order by آن توسط LINQ تهیه شده‌اند که در نهایت به یک کوئری ترجمه شده و بر روی بانک اطلاعاتی اجرا می‌شوند.
یک مثال جالب آن، امکان کوئری گرفتن از Table Value Function‌ها و سپس ترکیب آن‌ها با LINQ است (این ترکیب، تنها یک کوئری SQL نهایی را تولید می‌کند):
var posts = context.Posts
  .FromSql("SELECT * FROM dbo.GetMatchingPostByTitle({0})", searchTerm)
  .Where(p => p.BlogId == 1)
  .OrderByDescending(p => p.CreateDate)
  .ToList();


واکشی ارتباطات یک موجودیت توسط SQL و LINQ

در ابتدای بحث در قسمت محدودیت‌های کوئری‌های SQL نوشته شده، ذکر شد «کوئری SQL نوشته شده نباید به همراه اطلاعات ارتباطات موجودیت‌ها باشد». برای رفع این محدودیت می‌توان از ترکیب SQL و LINQ به صورت ذیل استفاده کرد:
var searchTerm = ".NET";
var blogs = context.Blogs
  .FromSql("SELECT * FROM dbo.SearchBlogs {0}", searchTerm)
  .Include(b => b.Posts)
  .ToList();
در اینجا برای واکشی ارتباطات یک موجودیت از متد Include استفاده شده‌است.


اجرای عبارات SQL، بدون بازگشت مقداری

تا اینجا در مورد عبارات SQL از نوع Select و یا اجرای رویه‌های ذخیره شده، بحث شد. برای اجرای عبارات SQL ایی مانند update و delete می‌توان از متد ExecuteSqlCommand مربوط به  context.Database استفاده کرد:
  context.Database.ExecuteSqlCommand("UPDATE dbo.People SET FirstName = 'Jane' WHERE PersonId = 30");
و یا برای ارسال پارامترها به آن می‌توان به این صورت عمل کرد (اجرای یک رویه‌ی ذخیره شده با دو پارامتر ارسالی به آن):
context.Database.ExecuteSqlCommand("usp_CreateShipper @p0, @p1",
  parameters: new[] { "hello", "world" });


اجرای عبارات SQL و دریافت خروجی‌هایی به غیر از موجودیت‌های برنامه

در ابتدا بحث عنوان شد که محدودیت فعلی کوئری‌های FromSQL که می‌توانند خروجی را نیز ارائه دهند، مقید بودن آن‌ها به DbSet در حال استفاده است و محدود بودن آن‌ها به خواص کلاس متناظر تعریف شده. در این حالت اگر بخواهیم یک محاسبه‌ی عددی را بازگشت دهیم چه باید کرد؟
متد ExecuteSqlCommand تنها وضعیت نهایی اجرای عملیات را بازگشت می‌دهد و FromSQL مقید است به DbSet متناظر. برای رفع این محدودیت‌ها می‌توان مستقیما به DbConnection دسترسی یافت و سپس کوئری گرفت؛ به نحو ذیل:
using (var connection = context.Database.GetDbConnection())
{
    connection.Open();
 
    using (var command = connection.CreateCommand())
    {
        command.CommandText = "SELECT COUNT(*) FROM Contacts";
        var result = command.ExecuteScalar().ToString();
    }
}
به عبارتی در اینجا امکان بازگشت به حالت ADO.NET خام نیز پیش بینی شده‌است.
‫۸ سال و ۲ ماه قبل، یکشنبه ۷ شهریور ۱۳۹۵، ساعت ۱۹:۰۰
برات جوادی برات جوادی
مطالب
MongoDB #13
توابع جمعی در MongoDB
عملگرهای جمعی، رکوردهای اطلاعات را پردازش می‌کنند و نتیجه‌های محاسبه شده را برمی‌گردانند. عملیات جمعی مقادیر چندین سند را باهم گروه بندی می‌کند و می‌تواند یک نوع از عملگرها را روی اطلاعات دسته بندی شده انجام دهد تا یک نتیجه‌ی واحد را برگرداند. در sql، دستور (*)count همراه Group by معادل یک تابع جمعی در MongoDB است.
متد ()aggregate
برای توابع جمعی در MongoDB باید از متد ()aggregate استفاده کنید.
گرامر
گرامر پایه متد ()aggregate به صورت زیر است:
>db.COLLECTION_NAME.aggregate(AGGREGATE_OPERATION)
مثال
در این مجموعه، داده‌های زیر را دارید:
{
   _id: ObjectId(7df78ad8902c)
   title: 'MongoDB Overview', 
   description: 'MongoDB is no sql database',
   by_user: 'user1',
   url: 'http://www.site.com',
   tags: ['mongodb', 'database', 'NoSQL'],
   likes: 100
},
{
   _id: ObjectId(7df78ad8902d)
   title: 'NoSQL Overview', 
   description: 'No sql database is very fast',
   by_user: 'user1',
   url: 'http://www.site.com',
   tags: ['mongodb', 'database', 'NoSQL'],
   likes: 10
},
{
   _id: ObjectId(7df78ad8902e)
   title: 'Neo4j Overview', 
   description: 'Neo4j is no sql database',
   by_user: 'Neo4j',
   url: 'http://www.neo4j.com',
   tags: ['neo4j', 'database', 'NoSQL'],
   likes: 750
},
حالا اگر بخواهید از مجموعه‌ی بالا یک لیست را که تعداد دوره‌های نوشته شده توسط هر کاربر را نمایش می‌دهد، استخراج کنید، باید ار متد () aggregate به صورت زیر استفاده نمائید:
> db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$sum : 1}}}])
{
   "result" : [
      {
         "_id" : "user1",
         "num_tutorial" : 2
      },
      {
         "_id" : "Neo4j",
         "num_tutorial" : 1
      }
   ],
   "ok" : 1
}
>

معادل کوئری بالا در sql بصورت زیر خواهد بود: 
select by_user, count(*) from mycol group by by_user
در مثال بالا، سندهای گروه بندی شده‌ی توسط فیلد by_user را داریم و در هر اجرای by_user مقدار قبلی جمع کلی افزایش می‌یابد. در اینجا لیست عبارت‌های جمعی موجود، آمده است.
عبارت  توضیحات   مثال
 $sum  مقدار تعیین شده از همه سندهای مجموعه را جمع می‌کند. 
 db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$sum : "$likes"}}}])
 $avg میانگین همه مقادیر بدست آمده از سندهای مجموعه را محاسبه می‌کند. 
 db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$avg : "$likes"}}}])
 $min کمترین مقادیر مشابه را از همه سندهای مجموعه، بر می‌گرداند. 
 db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$min : "$likes"}}}])
 $max بیشترین مقادیر مشابه را از همه سندهای مجموعه، بر می‌گرداند. 
 db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$max : "$likes"}}}])
 $push یک مقدار را در سند نتیجه، در یک آرایه درج می‌کند. 
 db.mycol.aggregate([{$group : {_id : "$by_user", url : {$push: "$url"}}}])
 $addToSet یک مقدار را در سند نتیجه در یک آرایه درج می‌کند، اما مقدار تکراری ایجاد نمی‌کند. 
 db.mycol.aggregate([{$group : {_id : "$by_user", url : {$addToSet : "$url"}}}])
 $first اولین سند از اسناد را برطبق گروه بندی بر می‌گرداند. معمولا این عبارت بعد از عبارت‌های مرتب سازی مرحله‌ای استفاده می‌شود. 
 db.mycol.aggregate([{$group : {_id : "$by_user", first_url : {$first : "$url"}}}])
 $last آخرین سند از اسناد را برطبق گروه بندی بر می‌گرداند. معمولا این عبارت بعد از عبارت‌های مرتب سازی مرحله‌ای استفاده می‌شود. 
 db.mycol.aggregate([{$group : {_id : "$by_user", last_url : {$last : "$url"}}}])

مفهوم Pipeline
در Command shell یونیکس، خط لوله (Pipeline) به معنی امکان اجرای یک عملیات روی چندین ورودی و استفاده از خروجی بعنوان ورودی برای دستور بعدی و ادامه‌ی آن است. MongoDB نیز این مفهوم را در چارچوب توابع جمعی پشتیبانی می‌کند. یک مجموعه از مراحل وجود دارند که هرکدام از آنها یک مجموعه از اسناد را بعنوان ورودی می‌گیرند و یک مجموعه از سند را بعنوان نتیجه (یا نتیجه را بعنوان سند JSON در پایان خط لوله) ارائه می‌دهند. این عمل به نوبه خود می‌تواند برای مرحله بعد و یا مراحل بعدی، استفاده شود.
مراحل ممکن در چارچوب توابع جمعی در زیر آمده اند:
  • $project : برای انتخاب چندین فیلد از یک مجموعه استفاده می‌شود.
  • $match : این یک عملگر فیلترگذاری است که می‌تواند میزان اسنادی را که بعنوان ورودی در مرحله بعد گرفته می‌شوند، کاهش دهد.
  • $group : این همان تابع جمعی است که در بالا توضیح داده شد.
  • $skip : توسط این عبارت، در یک لیست بدست آمده (نتیجه)، می‌توانید از لیست اسناد بصورت روبه جلو صرفنظر کنید. 
  • $limit : این عبارت تعداد اسناد را توسط عدد گرفته شده، از موقعیت فعلی برای نمایش محدود می‌کند. 
  • $unwind : این عبارت برای باز کردن (unwind) سندی که از آرایه‌ها بهره گیری می‌کند استفاده می‌شود. وقتی از آرایه استفاده می‌کنید، داده از نوع پیش پیوست (Pre-joined) است و با این نوع داده، این عمل برای داشتن سندهای اختصاصی نا تمام خواهد ماند. بنابراین با این مرحله می‌توانید میزان اسناد را برای مرحله بعد افزایش دهید. 
‫۹ سال و ۹ ماه قبل، دوشنبه ۲۰ بهمن ۱۳۹۳، ساعت ۰۲:۰۵
وحید نصیری وحید نصیری
مطالب
بررسی مقدار دهی اولیه متغیرها در T-SQL

یکی از موارد مشکل ساز حین استفاده از T-SQL ، مقدار دهی اولیه متغیرها به نال است و اگر اسکریپت تهیه شده کمی طولانی باشد، خطایابی مشکلات مرتبط با آن بسیار مشکل می‌شود. برای مثال:
Declare
@x int,
@y int

Set @x = 1
If (@x + @y = 1)
BEGIN
  print 'yes!'
End

Set @y = (select sum(id) from Account)
If @x + @y = 1
BEGIN
  print 'yes!'
End

کد فوق بدون هیچگونه خطایی اجرا می‌شود و هیچ وقت هم yes را چاپ نمی‌کند. مشکل هم همینجا است. خطایابی قسمت دوم این اسکریپت کمی مشکل‌تر از حالت قبل است. چون در اینجا به نظر متغیر y صریحا مقدار دهی شده است؛ اما در عمل ممکن است برای مثال به دلیل عدم وجود رکوردی در جدول Account، باز هم null به آن نسبت داده شود.

بنابراین سؤال این است که چگونه این نوع مشکلات را در یک پروژه با تعداد زیادی رویه ذخیره شده، تابع و غیره می‌توان تشخیص داد؟
پاسخ:
در این مورد قبلا مطلبی در این سایت منتشر شده [+] (البته اگر از نگارش کامل VS 2010 استفاده می‌کنید نیازی به نصب چیزی نخواهید داشت) و نکته‌ی آن بررسی SR0007 است.



‫۱۳ سال و ۵ ماه قبل، پنجشنبه ۲ تیر ۱۳۹۰، ساعت ۱۷:۵۰
وحید محمدطاهری وحید محمدطاهری
مسیرراه‌ها
SQL Server
آخرین تاریخ بروزرسانی 93/10/21


SQL Server 2005

SQL Server 2008

SQL Server 2012

SQL Serve 2014


‫۹ سال و ۹ ماه قبل، یکشنبه ۲۱ دی ۱۳۹۳، ساعت ۱۱:۱۷
وحید نصیری وحید نصیری
مطالب
مقدار دهی کلیدهای خارجی در NHibernate و Entity framework

ORM های NHibernate و Entity framework روش‌های متفاوتی را برای به روز رسانی کلید خارجی با حداقل رفت و برگشت به دیتابیس ارائه می‌دهند که در ادامه معرفی خواهند شد.

صورت مساله:
فرض کنید می‌خواهیم برنامه‌ای را بنویسیم که ریز پرداخت‌های روزانه‌ی ما را ثبت کند. برای اینکار حداقل به یک جدول گروه‌های اقلام خریداری شده، یک جدول حساب‌های تامین کننده‌ی مخارج، یک جدول فروشنده‌ها و نهایتا یک جدول صورتحساب‌های پرداختی بر اساس جداول ذکر شده نیاز خواهد بود.

الف) بررسی مدل برنامه



در اینجا جهت تعریف ویژگی‌ها یا Attributes تعریف شده در این کلاس‌ها از NHibernate validator استفاده شده (+). مزیت اینکار هم علاوه بر اعتبارسنجی سمت کلاینت (پیش از تبادل اطلاعات با بانک اطلاعاتی)، تولید جداولی با همین مشخصات است. برای مثال Fluent NHibernate بر اساس ویژگی Length تعریف شده با طول حداکثر 120 ، یک فیلد nvarchar با همین طول را ایجاد می‌کند.

public class Account
{
       public virtual int Id { get; set; }

       [NotNullNotEmpty]
       [Length(Min = 3, Max = 120, Message = "طول نام باید بین 3 و 120 کاراکتر باشد")]
       public virtual string Name { get; set; } 
}

public class Category
{
       public virtual int Id { get; set; }

       [NotNullNotEmpty]
       [Length(Min = 3, Max = 130, Message = "طول نام باید بین 3 و 130 کاراکتر باشد")]
       public virtual string Name { get; set; } 
}

public class Payee
{
       public virtual int Id { get; set; }

       [NotNullNotEmpty]
       [Length(Min = 3, Max = 120, Message = "طول نام باید بین 3 و 120 کاراکتر باشد")]
       public virtual string Name { get; set; }
}

public class Bill
{
       public virtual int Id { get; set; }

       [NotNull]
       public virtual Account Account { get; set; }

       [NotNull]
       public virtual Category Category { get; set; }

       [NotNull]
       public virtual Payee Payee { get; set; }

       [NotNull]
       public virtual decimal Amount { set; get; }

       [NotNull]
       public virtual DateTime BillDate { set; get; }

       [NotNullNotEmpty]
       [Length(Min = 1, Max = 500, Message = "طول توضیحات باید بین 1 و 500 کاراکتر باشد")]
       public virtual string Description { get; set; } 
}




ب) ساختار جداول متناظر (تولید شده به صورت خودکار توسط Fluent NHibernate در اینجا)


در مورد نحوه‌ی استفاده از ویژگی AutoMapping و همچنین تولید خودکار ساختار بانک اطلاعاتی از روی جداول در NHibernate قبلا توضیح داده شده است. البته بدیهی است که ترکیب مقاله‌ی Validation و آشنایی با AutoMapping در اینجا جهت اعمال ویژگی‌ها باید بکار گرفته شود که در همان مقاله‌ی Validation مفصل توضیح داده شده است.
نکته‌ی مهم database schema تولیدی، کلید‌های خارجی (foreign key) تعریف شده بر روی جدول Bills است (همان AccountId، CategoryId و PayeeId تعریف شده) که به primary key جداول متناظر اشاره می‌کند.
    create table Accounts (
       AccountId INT IDENTITY NOT NULL,
      Name NVARCHAR(120) not null,
      primary key (AccountId)
   )

   create table Bills (
       BillId INT IDENTITY NOT NULL,
      Amount DECIMAL(19,5) not null,
      BillDate DATETIME not null,
      Description NVARCHAR(500) not null,
      AccountId INT not null,
      CategoryId INT not null,
      PayeeId INT not null,
      primary key (BillId)
   )

   create table Categories (
       CategoryId INT IDENTITY NOT NULL,
      Name NVARCHAR(130) not null,
      primary key (CategoryId)
   )

   create table Payees (
       PayeeId INT IDENTITY NOT NULL,
      Name NVARCHAR(120) not null,
      primary key (PayeeId)
   )

   alter table Bills
       add constraint fk_Account_Bill
       foreign key (AccountId)
       references Accounts

   alter table Bills
       add constraint fk_Category_Bill
       foreign key (CategoryId)
       references Categories

   alter table Bills
       add constraint fk_Payee_Bill
       foreign key (PayeeId)
       references Payees

ج) صفحه‌ی ثبت صورتحساب‌ها

صفحات ثبت گروه‌های اقلام، حساب‌ها و فروشنده‌ها، نکته‌ی خاصی ندارند. چون این جداول وابستگی خاصی به جایی نداشته و به سادگی اطلاعات آن‌ها را می‌توان ثبت یا به روز کرد.
صفحه‌ی مشکل در این مثال، همان صفحه‌ی ثبت صورتحساب‌ها است که از سه کلید خارجی به سه جدول دیگر تشکیل شده است.
عموما برای طراحی این نوع صفحات، کلیدهای خارجی را با drop down list نمایش می‌دهند و اگر در جهت سهولت کار کاربر قدم برداشته شود، باید از یک Auto complete drop down list استفاده کرد تا کاربر برنامه جهت یافتن آیتم‌های از پیش تعریف شده کمتر سختی بکشد.



اگر از Silverlight یا WPF استفاده شود، امکان بایند یک لیست کامل از اشیاء با تمام خواص مرتبط به آن‌ها وجود دارد (هر رکورد نمایش داده شده در دراپ داون لیست، دقیقا معادل است با یک شیء متناظر با کلاس‌های تعریف شده است). اگر از ASP.NET استفاده شود (یعنی یک محیط بدون حالت که پس از نمایش یک صفحه دیگر خبری از لیست اشیاء بایند شده وجود نخواهد داشت و همگی توسط وب سرور جهت صرفه جویی در منابع تخریب شده‌اند)، بهتر است datatextfield را با فیلد نام و datavaluefield را با فیلد Id مقدار دهی کرد تا کاربر نهایی، نام را جهت ثبت اطلاعات مشاهده کند و برنامه از Id موجود در لیست جهت ثبت کلیدهای خارجی استفاده نماید.
و نکته‌ی اصلی هم همینجا است که چگونه؟! چون ما زمانیکه با یک ORM سر و کار داریم، برای ثبت یک رکورد در جدول Bills باید یک وهله از کلاس Bill را ایجاد کرده و خواص آن‌را مقدار دهی کنیم. اگر به تعریف کلاس Bill مراجعه کنید، سه خاصیت آن از نوع سه کلاس مجزا تعریف شده است. به به عبارتی با داشتن فقط یک id از رکوردهای این کلاس‌ها باید بتوان سه وهله‌ی متناظر آن‌ها را از بانک اطلاعاتی خواند و سپس به این خواص انتساب داد:

var newBill = new Bill
                   {
                       Account = accountRepository.GetByKey(1),
                       Amount = 1,
                       BillDate = DateTime.Now,
                       Category = categoryRepository.GetByKey(1),
                       Description = "testestest...",
                       Payee = payeeRepository.GetByKey(1)
                   };
یعنی برای ثبت یک رکورد در جدول Bills فوق، چهار بار رفت و برگشت به دیتابیس خواهیم داشت:
- یکبار برای دریافت رکورد متناظر با گروه‌ها بر اساس کلید اصلی آن (که از دراپ داون لیست مربوطه دریافت می‌شود)
- یکبار برای دریافت رکورد متناظر با فروشند‌ه‌ها بر اساس کلید اصلی آن (که از دراپ داون لیست مربوطه دریافت می‌شود)
- یکبار برای دریافت رکورد متناظر با حساب‌ها بر اساس کلید اصلی آن (که از دراپ داون لیست مربوطه دریافت می‌شود)
- یکبار هم ثبت نهایی اطلاعات در بانک اطلاعاتی

متد GetByKey فوق همان متد session.Get استاندارد NHibernate است (چون به primary key ها از طریق drop down list دسترسی داریم، به سادگی می‌توان بر اساس متد Get استاندارد ذکر شده عمل کرد).

SQL نهایی تولیدی هم به صورت واضحی این مشکل را نمایش می‌دهد (4 بار رفت و برگشت؛ سه بار select یکبار هم insert نهایی):
SELECT account0_.AccountId as AccountId0_0_, account0_.Name as Name0_0_
FROM Accounts account0_ WHERE account0_.AccountId=@p0;@p0 = 1 [Type: Int32 (0)]

SELECT category0_.CategoryId as CategoryId2_0_, category0_.Name as Name2_0_
FROM Categories category0_ WHERE category0_.CategoryId=@p0;@p0 = 1 [Type: Int32 (0)]

SELECT payee0_.PayeeId as PayeeId3_0_, payee0_.Name as Name3_0_
FROM Payees payee0_ WHERE payee0_.PayeeId=@p0;@p0 = 1 [Type: Int32 (0)]

INSERT INTO Bills (Amount, BillDate, Description, AccountId, CategoryId, PayeeId)
VALUES (@p0, @p1, @p2, @p3, @p4, @p5);
select SCOPE_IDENTITY();
@p0 = 1 [Type: Decimal (0)],
@p1 = 2010/12/27 11:48:33 ق.ظ [Type: DateTime (0)],
@p2 = 'testestest...' [Type: String (500)],
@p3 = 1 [Type: Int32 (0)],
@p4 = 1 [Type: Int32 (0)],
@p5 = 1 [Type: Int32 (0)]

کسانی که قبلا با رویه‌های ذخیره شده کار کرده باشند (stored procedures) احتمالا الان خواهند گفت؛ ما که گفتیم این روش کند است! سربار زیادی دارد! فقط کافی است یک SP بنویسید و کل عملیات را با یک رفت و برگشت انجام دهید.
اما در ORMs نیز برای انجام این مورد در طی یک حرکت یک ضرب راه حل‌هایی وجود دارد که در ادامه بحث خواهد شد:

د) پیاده سازی با NHibernate
برای حل این مشکل در NHibernate با داشتن primary key (برای مثال از طریق datavaluefield ذکر شده)، بجای session.Get از session.Load استفاده کنید.
session.Get یعنی همین الان برو به بانک اطلاعاتی مراجعه کن و رکورد متناظر با کلید اصلی ذکر شده را بازگشت بده و یک شیء از آن را ایجاد کن (حالت‌های دیگر دسترسی به اطلاعات مانند استفاده از LINQ یا Criteria API یا هر روش مشابه دیگری نیز در اینجا به همین معنا خواهد بود).
session.Load یعنی فعلا دست نگه دار! مگر در جدول نهایی نگاشت شده، اصلا چیزی به نام شیء مثلا گروه وجود دارد؟ مگر این مورد واقعا یک فیلد عددی در جدول Bills بیشتر نیست؟ ما هم که الان این عدد را داریم (به کمک عناصر دراپ داون لیست)، پس لطفا در پشت صحنه یک پروکسی برای ایجاد شیء مورد نظر ایجاد کن (uninitialized proxy to the entity) و سپس عملیات مرتبط را در حین تشکیل SQL نهایی بر اساس این عدد موجود انجام بده. یعنی نیازی به رفت و برگشت به بانک اطلاعاتی نیست. در این حالت اگر SQL نهایی را بررسی کنیم فقط یک سطر زیر خواهد بود (سه select ذکر شده حذف خواهند شد):
INSERT INTO Bills (Amount, BillDate, Description, AccountId, CategoryId, PayeeId)
VALUES (@p0, @p1, @p2, @p3, @p4, @p5);
select SCOPE_IDENTITY();
@p0 = 1 [Type: Decimal (0)],
@p1 = 2010/12/27 11:58:22 ق.ظ [Type: DateTime (0)],
@p2 = 'testestest...' [Type: String (500)],
@p3 = 1 [Type: Int32 (0)],
@p4 = 1 [Type: Int32 (0)],
@p5 = 1 [Type: Int32 (0)]

ه) پیاده سازی با Entity framework

Entity framework زمانیکه بانک اطلاعاتی فوق را (به روش database first) به کلاس‌های متناظر تبدیل/نگاشت می‌کند، حاصل نهایی مثلا در مورد کلاس Bill به صورت خلاصه به شکل زیر خواهد بود:
public partial class Bill : EntityObject
{
       public global::System.Int32 BillId {set;get;}
       public global::System.Decimal Amount {set;get;}
       public global::System.DateTime BillDate {set;get;}
       public global::System.String Description {set;get;}
       public global::System.Int32 AccountId {set;get;}
       public global::System.Int32 CategoryId {set;get;}
       public global::System.Int32 PayeeId {set;get;}
       public Account Account {set;get;}
       public Category Category {set;get;}
}
به عبارتی فیلدهای کلیدهای خارجی، در تعریف نهایی این کلاس هم مشاهده می‌شوند. در اینجا فقط کافی است سه کلید خارجی، از نوع int مقدار دهی شوند (و نیازی به مقدار دهی سه شیء متناظر نیست). در این حالت نیز برای ثبت اطلاعات، فقط یکبار رفت و برگشت به بانک اطلاعاتی خواهیم داشت.

‫۱۳ سال و ۱۱ ماه قبل، دوشنبه ۶ دی ۱۳۸۹، ساعت ۱۶:۲۴
محمد رجبی محمد رجبی
مطالب
بررسی ابزار SQL Server Profiler

مقدمه

Profiler یک ابزار گرافیکی برای ردیابی و نظارت بر کارآئی SQL Server است. امکان ردیابی اطلاعاتی در خصوص رویدادهای مختلف و ثبت این داده‌ها در یک فایل (با پسوند trc) یا جدول برای تحلیل‌های آتی نیز وجود دارد. برای اجرای این ابزار مراحل زیر را انجام دهید:

Start > Programs> Microsoft SQL Server > Performance Tools> SQL Server Profiler
و یا در محیط  Management Studio از منوی Tools گزینه SQL Server Profiler را انتخاب نمائید.


1- اصطلاحات

1-1- رویداد (Event):

یک رویداد، کاری است که توسط موتور بانک اطلاعاتی (Database Engine) انجام می‌شود. برای مثال هر یک از موارد زیر یک رویداد هستند.
-  متصل شدن کاربران (login connections) قطع شدن ارتباط یک login
-  اجرای دستورات T-SQL، شروع و پایان اجرای یک رویه، شروع و پایان یک دستور در طول اجرای یک رویه، اجرای رویه‌های دور Remote Procedure Call
-  باز شدن یک Cursor
-  بررسی و کنترل مجوزهای امنیتی

1-2- کلاس رویداد (Event Class):

برای بکارگیری رویدادها در Profiler، از یک Event Class استفاده می‌کنیم. یک Event Class رویدادی است که قابلیت ردیابی دارد. برای مثال بررسی ورود و اتصال کاربران با استفاده از کلاس Audit Login قابل پیاده سازی است. هر یک از موارد زیر یک Event Class هستند.
-  SQL:BatchCompleted
-  Audit Login
-  Audit Logout
-  Lock: Acquired
-  Lock: Released

1-3- گروه رویداد (Event Category):

یک گروه رویداد شامل رویدادهایی است که به صورت مفهومی دسته بندی شده اند. برای مثال، کلیه رویدادهای مربوط به قفل‌ها از جمله Lock: Acquired (بدست آوردن قفل) و Lock: Released (رها کردن قفل) در گروه رویداد Locks قرار  دارند.

1-4- ستون داده ای (Data Column):

یک ستون داده ای، خصوصیت و جزئیات یک رویداد را شامل می‌شود. برای مثال در یک Trace که رویدادهای Lock: Acquired را نظارت می‌کند، ستون Binary Data شامل شناسه (ID) یک صفحه و یا یک سطر قفل شده است و یا اینکه ستون Duration مدت زمان اجرای یک رویه را نمایش می‌دهد.

1-5- الگو (Template):

یک الگو، مشخص کننده تنظیمات پیش گزیده برای یک Trace است، این تنظیمات شامل رویدادهایی است که نیاز دارید بر آنها نظارت داشته باشید. هنگامیکه یک Trace براساس یک الگو اجرا شود، رویدادهای مشخص شده، نظارت می‌شوند و نتیجه به صورت یک فایل یا جدول قابل مشاهده خواهد بود.

1-6- ردیاب (Trace):

یک Trace داده‌ها را براساس رویدادهای انتخاب شده، جمع آوری می‌کند. امکان اجرای بلافاصله یک Trace برای جمع آوری اطلاعات با توجه به رویدادهای انتخاب شده و ذخیره کردن آن برای اجرای آتی وجود دارد.

1-7- فیلتر (Filter):

هنگامی که یک Trace یا الگو ایجاد می‌شود، امکان تعریف شرایطی برای فیلتر کردن داده‌های جمع آوری شده نیز وجود دارد. این کار باعث کاهش حجم داده‌های گزارش شده می‌شود. برای مثال اطلاعات مربوط به یک کاربر خاص جمع آوری می‌شود و یا اینکه رشد یک بانک اطلاعاتی مشخص بررسی می‌شود.


2- انتخاب الگو (Profiler Trace Templates)

از آنجائیکه اصولاً انتخاب Eventهای مناسب، کار سخت و تخصصی می‌باشد برای راحتی کار تعدادی Template‌های آماده وجود دارد، برای مثال TSQL_Duration تاکیدش روی مدت انجام کار است و یا SP_Counts در مواردی که بخواهیم رویه‌های ذخیره شده را بهینه کنیم استفاده می‌شود در جدول زیر به شرح هر یک پرداخته شده است:
 الگو  هدف 
 Blank   ایجاد یک Trace کلی 
 SP_Counts   ثبت اجرای هر رویه ذخیره شده برای تشخیص اینکه هر رویه چند بار اجرا شده است 
 Standard   ثبت آمارهای کارائی برای هر رویه ذخیره شده و Query‌های عادی SQL که اجرا می‌شوند و عملیات ورود و خروج هر Login (پیش فرض) 
 TSQL   ثبت یک لیست از همه رویه‌های ذخیره شده و Query‌های عادی SQL که اجرا می‌شوند ولی آمارهای کارائی را شامل نمی‌شود 
 TSQL_Duration   ثبت مدت زمان اجرای هر رویه ذخیره شده و هر Query عادی SQL 
 TSQL_Grouped   ثبت تمام  login‌ها و logout‌ها در طول اجرای رویه‌های ذخیره شده و هر Query عادی SQL، شامل اطلاعاتی برای شناسائی برنامه و کاربری که درخواست را اجرا می‌کند 
 TSQL_Locks   ثبت اطلاعات انسداد (blocking) و بن بست (deadlock) از قبیل blocked processes، deadlock chains، deadlock graphs,... . این الگو همچنین درخواست‌های تمام رویه‌های ذخیره شده و تمامی دستورات هر رویه و  هر Query عادی SQL را دریافت می‌کند 
 TSQL_Replay   ثبت اجرای رویه‌های ذخیره شده و Query‌های SQL در یک SQL Instance و  مهیا کردن امکان اجرای دوباره عملیات در سیستمی دیگر 
 TSQL_SPs   ثبت کارائی برای Query‌های SQL، رویه‌های ذخیره شده و تمامی دستورات درون یک رویه ذخیره شده و نیز عملیات ورود و خروج هر Login 
 Tuning   ثبت اطلاعات کارائی برای Query‌های عادی SQL و رویه‌های ذخیره شده و یا تمامی دستورات درون یک رویه ذخیره شده 

3- انتخاب رویداد (SQL Trace Event Groups)

رویداد‌ها در 21 گروه رویداد دسته بندی می‌شوند که در جدول زیر لیست شده اند:
 گروه رویداد  هدف 
 Broker  13 رویداد برای واسطه سرویس (Service Broker) 
 CLR   1 رویداد برای بارگذاری اسمبلی‌های CLR (Common Language Runtime) 
 Cursors   7 رویداد برای ایجاد، دستیابی و در اختیار گرفتن Cursor 
 Database   6 رویداد برای رشد/کاهش  (grow/shrink) فایل های  Data/Log همچنین تغییرات حالت انعکاس (Mirroring) 
 Deprecation   2 رویداد برای آگاه کردن وضعیت نابسامان درون یک SQL Instance 
 Errors and
Warnings  		 16 رویداد برای خطاها، هشدارها و پیغام‌های اطلاعاتی که ثبت شده است 
 Full Text   3  رویداد برای پیگیری یک شاخص متنی کامل 
 Locks   9 رویداد برای بدست آوردن، رها کردن قفل و بن بست (Deadlock) 
 OLEDB   5 رویداد برای درخواست‌های توزیع شده و RPC (اجرای رویه‌های دور) 
 Objects   3 رویداد برای وقتی که یک شی ایجاد، تغییر یا حذف می‌شود 
 Performance   14 رویداد برای ثبت نقشه درخواست‌ها (Query Plan) برای استفاده نقشه راهنما (Plan Guide) به منظور بهینه سازی کارائی درخواست ها،  همچنین این گروه رویداد در خواست‌های متنی کامل (full text) را ثبت می‌کند 
 Progress Report   10 رویداد برای ایجاد Online Index 
 Query
Notifications  		 4 رویداد برای سرویس اطلاع رسان (Notification Service) 
 Scans   2 رویداد برای وقتی که یک جدول یا شاخص، پویش می‌شود 
 Security Audit   44 رویداد برای وقتی که مجوزی استفاده شود، جابجائی هویتی رخ دهد، تنظیمات امنیتی اشیائی تغییر کند،یک  SQL Instance  شروع و متوقف شود و یک  Database جایگزین شود یا از آن پشتیبان گرفته شود 
 Server  3 رویداد برای Mount Tape، تغییر کردن حافظه سرور و بستن یک فایل Trace 
 Sessions   3 رویداد برای وقتی که Connection‌ها موجود هستند و یک Trace فعال می‌شود، همچنین یک Trigger  و یک تابع دسته بندی(classification functions) مربوط به مدیریت منابع(resource governor) رخ دهد 
 Stored Procedures   12 رویداد برای اجرای یک رویه ذخیره شده و دستورات درون آن ، کامپایل مجدد و استفاده از حافظه نهانی (Cache) 
 Transactions   13 رویداد برای شروع، ذخیره ، تائید و لغو یک تراکنش 
 TSQL   9  رویداد برای اجرای Query‌های SQL و جستجوهای XQUERY (در داده‌های XML)  
 User Configurable   10 رویداد که شما می‌توانید پیکربندی کنید 
به طور معمول بیشتر از گروه رویدادهای Locks، Performance، Security Audit، Stored Procedures و TSQL استفاده می‌شود.


4- انتخاب ستون‌های داده ای ( Data Columns)

اگرچه می‌توان همه‌ی 64 ستون داده ای ممکن را برای ردیابی انتخاب کرد ولیکن داده‌های Trace شما زمانی مفید خواهند بود که اطلاعات ضروری را ثبت کرده باشید. برای مثال شماره ترتیب تراکنش‌ها را،  برای یک رویداد RPC:Completed می‌توانید برگردانید، اما همه رویه‌های ذخیره شده مقادیر را تغییر نمی‌دهند بنابراین شماره ترتیب تراکنش‌ها فضای بیهوده ای را مصرف می‌کند. بعلاوه همه ستون‌های داده ای برای تمامی رویداد‌های Trace معتبر نیستند. برای مثال Read ، Write ،CPU و Duration برای رویداد‌های RPC:Starting و SQL:BatchStarting معتبر نیستند.
ApplicationName، NTUserName، LoginName، ClientProcessID، SPID، HostName، LoginSID، NTDomainName و SessionLoginName ، مشخص می‌کنند چه کسی و از چه منشاء دستور را اجرا کرده است.
ستون SessionLoginName معمولاً نام Login ای که از آن برای متصل شدن به SQL Instance استفاده شده است را نشان می‌دهد. در حالیکه ستون LoginName نام کاربری را که دستور را اجرا می‌کند نشان می‌دهد (EXECUTE AS). ستون ApplicationName خالی است مگر اینکه در ConnectionString برنامه کاربردیمان این خصوصیت (Property) مقداردهی شده باشد. ستون StartTime و EndTime زمان سرحدی برای هر رویداد را ثبت می‌کند این ستون‌ها بویژه در هنگامی که به عملیات Correlate  نیاز دارید مفید هستند.


5- بررسی چند سناریو نمونه

•  یافتن درخواست هائی (Queries) که بدترین کارایی را دارا هستند.

برای ردیابی درخواست‌های ناکارا، از رویداد RPC:Completed از دسته Stored Procedure و رویداد SQL:BatchCompleted از دسته TSQL استفاده می‌شود.

•  نظارت بر کارایی رویه ها

برای ردیابی کارائی رویه ها، از رویدادهای SP:Starting، SP:Completed، SP:StmtCompleted و SP:StmtStaring از کلاس Stored Procedure و رویدادهای SQL:BatchStarting ، SQL:BatchCompleted از کلاس TSQL استفاده می‌شود.

•  نظارت بر اجرای دستورات T-SQL توسط هر کاربر

برای ردیابی دستوراتی که توسط یک کاربر خاص اجرا می‌شود، نیاز به ایجاد یک Trace برای نظارت بر رویدادهای کلاس‌های Sessions، ExistingConnection و TSQL داریم همچنین لازم است نام کاربر در قسمت فیلتر  و با استفاده از DBUserName مشخص شود.

•  اجرا دوباره ردیاب (Trace Replay)

این الگو  معمولاً برای debugging استفاده می‌شود برای این منظور  از الگوی Replay استفاده می‌شود. در ضمن امکان اجرای دوباره عملیات در سیستمی دیگر با استفاده از این الگو مهیا می‌شود.

•  ابزار Tuning Advisor (راهنمای تنظیم کارائی)

این ابزاری برای تحلیل کارائی یک یا چند بانک اطلاعاتی و تاثیر عملکرد آنها بر بار کاری (Workload) سرویس دهنده است. یک بار کاری مجموعه ای از دستورات T-SQL است که روی بانک اطلاعاتی اجرا می‌شود. بعد از تحلیل تاثیر بارکاری بر بانک اطلاعاتی، Tuning Advisor توصیه هائی برای اضافه کردن، حذف و یا تغییر طراحی فیزیکی ساختار بانک اطلاعاتی ارائه می‌دهد این تغییرات ساختاری شامل پیشنهاد برای تغییر ساختاری موارد Clustered Indexes، Nonclustered Indexes، Indexed View و Partitioning است.
برای ایجاد بارکاری می‌توان از یک ردیاب تهیه شده در SQL Profiler استفاده کرد برای این منظور از الگوی Tuning استفاده می‌شود و یا رویدادهای RPC:Completed، SQL:BatchCompleted و SP:StmtCompleted را ردیابی نمائید.

•  ترکیب ابزارهای نظارتی (Correlating Performance and Monitoring Data)

یک Trace برای ثبت اطلاعاتی که در یک SQL Instance رخ می‌دهد، استفاده می‌شود. System Monitor  برای ثبت شمارنده‌های کارائی(performance counters) استفاده می‌شود و همچنین از منابع سخت افزاری و اجزای دیگر که روی سرور اجرا می‌شوند، تصاویری فراهم می‌کند. توجه شود که در مورد  Correlating یک فایل ردیاب (trace file) و یک Counter Log (ابزار Performance )، ستون داده ای StartTime و EndTime باید انتخاب شود، برای این کار از منوی File گزینه Import Performance Data انتخاب می‌شود.

•  جستجوی علت رخ دادن یک بن بست

برای ردیابی علت رخ دادن یک بن بست، از رویدادهای RPC:Starting، SQLBatchStarting از دسته Stored Procedure و رویدادهای Deadlock graph، Lock:Deadlock و Lock:Deadlock Chain از دسته Locks استفاده می‌شود. ( در صورتی که نیاز به یک ارائه گرافیکی دارید از  Deadlock graph استفاده نمائید، خروجی مطابق تصویر زیر می‌شود).


5-1- ایجاد یک Trace

1-  Profiler را اجرا کنید از منوی File گزینه New Trace را انتخاب کنید و به SQL Instance مورد نظرتان متصل شوید.
2-  مطابق تصویر زیر برای Trace یک نام و الگو و تنظیمات ذخیره سازی فایل را مشخص کنید.


3-  بر روی قسمت Events Selection کلیک نمائید.
4-  مطابق تصویر زیر رویداد‌ها و کلاس رویداد‌ها را انتخاب کنید، ستون‌های TextData، NTUserName، LoginName، CPU،Reads،Writes، Duration، SPID، StartTime، EndTime، BinaryData، DataBaseName، ServerName و ObjectName را انتخاب کنید.

5-  روی Column Filters کلیک کنید و مطابق تصویر زیر برای DatabaseName فیلتری تنظیم کنید.


6-  روی Run کلیک کنید. تعدادی Query و رویه ذخیره شده مرتبط با پایگاه داده AdventureWorks اجرا کنید .


5-2- ایجاد یک Counter Log

برای ایجاد یک Counter Log  مراحل زیر  را انجام دهید:
1-  ابزار Performance را اجرا کنید (برای این کار عبارتPerfMon را در قسمت Run بنویسید).
2-  در قسمت Counter Logs یک log ایجاد کنید.
3-  روی Add Counters کلیک کرده و مطابق تصویر موارد زیر را انتخاب کنید.
Select counters from list 
Performance Object 
 Output Queue Length  Network Interface 
 % Processor Time  Processor 
 Processor Queue Length  System 
 Buffer Manager:Page life expectancy  SQLServer 
 
4-  روی Ok کلیک کنید تا Counter Log ذخیره شود سپس روی آن راست کلیک کرده و آنرا Start کنید.


5-3- ترکیب ابزارهای نظارتی (Correlating SQL Trace and System Monitor Data)

1-  Profiler را اجرا کنید از منوی File گزینه Open و سپس Trace File را انتخاب کنید فایل trc را که در گام اول ایجاد کردید، باز نمائید.
2-  از منوی File گزینه Import Performance Data را انتخاب کنید و فایل counter log  را که در مرحله قبل ایجاد کردید  انتخاب کنید.



نکته: اطلاعات فایل trc را می‌توان درون یک جدول وارد کرد، بدین ترتیب می‌توان آنالیز بیشتری داشت به عنوان مثال دستورات زیر این عمل را انجام می‌دهند.


 SELECT * INTO dbo.BaselineTrace
FROM fn_trace_gettable(' c:\performance baseline.trc ', default);
با اجرای دستور زیر جدولی با نام  BaselineTrace ایجاد و محتویات Trace مان (performance baseline.trc) در آن درج می‌گردد.
 
‫۱۰ سال و ۶ ماه قبل، شنبه ۶ اردیبهشت ۱۳۹۳، ساعت ۱۴:۴۵
وحید نصیری وحید نصیری
مطالب
نوشتن آزمون‌های واحد به کمک کتابخانه‌ی Moq - قسمت چهارم - بررسی تعامل بین سیستم در حال آزمایش و وابستگی‌های آن
علاوه بر امکان تنظیم مقدار خروجی متدها، مقدار خواص و ردیابی خواص تغییر کرده، یکی دیگر از قابلیت‌های کتابخانه‌ی Moq، بررسی مورد استفاده قرار گرفتن خواص و متدهای اشیاء Mock شده‌است، که عموما به آن Behavior based testing هم می‌گویند.


Behavior Based Testing چیست؟

آزمون‌هایی را که تاکنون بررسی کردیم از نوع state based testing بودند. در این حالت ابتدا یک Mock object را ایجاد و سپس وهله‌ای از سرویس مدنظر را توسط آن تهیه می‌کنیم. در ادامه تعدادی از متدهای این سرویس را مانند متد Process کلاس LoanApplicationProcessor، فراخوانی می‌کنیم. اینکار سبب اجرای فعالیتی در این سیستم شده و به همراه آن تعاملی با اشیاء Mock شده نیز صورت می‌گیرد. در نهایت، حالت و یا نتیجه‌ای را دریافت می‌کنیم و آن‌را با حالت یا نتیجه‌ای که انتظار داریم، مقایسه خواهیم کرد. بنابراین در این روش پس از پایان اجرای سیستم در حال اجرا، حالت و نتیجه‌ی نهایی حاصل از عملکرد آن، مورد بررسی قرار می‌گیرد.
در Behavior based testing نیز در ابتدا Mock objects مورد نیاز تهیه می‌شوند و سپس وهله‌ای از سرویس مدنظر را توسط آن‌ها تهیه می‌کنیم. همانند قبل، سیستم در حال بررسی را اجرا می‌کنیم (برای مثال با فراخوانی متدی در یک سرویس) تا سیستم، با اشیاء Mock شده کار کند. در این حالت دسترسی به متدی و یا خاصیتی بر روی Mock object صورت می‌گیرد. اکنون همانند روش state based testing که نتیجه‌ی عملیات را مورد بررسی قرار می‌دهد، در اینجا بررسی می‌کنیم که آیا خاصیت یا متد خاصی در Mock objectهای تنظیم شده، استفاده شده‌اند یا خیر؟ بنابراین هدف از این نوع آزمایش، بررسی تعامل بین یک سیستم و وابستگی‌های آن است.
برای مثال فرض کنید که می‌خواهیم کلاس ProductCache را بررسی و آزمایش کنیم. این کلاس از یک DB Provider واقعی برای دسترسی به اطلاعات استفاده می‌کند. برای مثال اگر محصول شماره‌ی 42 را از آن درخواست دهیم، اگر این محصول در کش موجود نباشد، ابتدا یک کوئری را به بانک اطلاعاتی صادر کرده و مقدار متناظری را دریافت می‌کند. سپس نتیجه را کش کرده و به فراخوان بازگشت می‌دهد. در اینجا می‌توان بررسی کرد که آیا محصول صحیحی از کش دریافت شده‌است یا خیر؟ (یا همان state based testing). اما اگر بخواهیم منطق کش کردن را بررسی کنیم، چطور می‌توان متوجه شد که برای مثال محصول دریافت شده مستقیما از کش دریافت شده و یا خیر از همان ابتدا از بانک اطلاعاتی واکشی شده، کش شده و سپس بازگشت داده شده‌است؟ برای این منظور می‌توان توسط کتابخانه‌ی Moq، یک نمونه‌ی mock شده‌ی DB Provider را تهیه و سپس از آن به عنوان وابستگی شیء Product Cache استفاده کرد. اکنون زمانیکه اطلاعاتی از Product Cache درخواست می‌شود، می‌توان Mock object تهیه شده را طوری تنظیم کرد تا اطلاعات مدنظر ما را بازگشت دهد. در این بین مزیت کار کردن با یک Mock object، امکان بررسی این است که آیا متدی بر روی آن فراخوانی شده‌است یا خیر؟ به این ترتیب می‌توان تعامل و رفتار Product Cache را با وابستگی آن، تحت نظر قرار داد (Behavior based testing).


بررسی فراخوانی شدن یک متد بدون پارامتر بر روی یک Mock object

در مثال این سری و در کلاس LoanApplicationProcessor و متد Process آن، فراخوانی سطر زیر را مشاهده می‌کنید:
_identityVerifier.Initialize();
اکنون می‌خواهیم آزمایشی را بنویسیم تا نشان دهد متد Initialize فوق، در صورت فراخوانی متد Process کلاس LoanApplicationProcessor، حتما فراخوانی شده‌است:
namespace Loans.Tests
{
    [TestClass]
    public class LoanApplicationProcessorShould
    {
        [TestMethod]
        public void InitializeIdentityVerifier()
        {
            var product = new LoanProduct {Id = 99, ProductName = "Loan", InterestRate = 5.25m};
            var amount = new LoanAmount {CurrencyCode = "Rial", Principal = 2_000_000_0};
            var applicant =
                new Applicant {Id = 1, Name = "User 1", Age = 25, Address = "This place", Salary = 1_500_000_0};
            var application = new LoanApplication {Id = 42, Product = product, Amount = amount, Applicant = applicant};

            var mockIdentityVerifier = new Mock<IIdentityVerifier>();
            mockIdentityVerifier.Setup(x => x.Validate(applicant.Name, applicant.Age, applicant.Address))
                .Returns(true);

            var mockCreditScorer = new Mock<ICreditScorer>();
            mockCreditScorer.Setup(x => x.ScoreResult.ScoreValue.Score).Returns(110_000);

            var processor = new LoanApplicationProcessor(mockIdentityVerifier.Object, mockCreditScorer.Object);
            processor.Process(application);

            mockIdentityVerifier.Verify(x => x.Initialize());
        }
    }
}
تنظیم mockIdentityVerifier.Setup را در قسمت دوم این سری «تنظیم مقادیر بازگشتی متدها» بررسی کردیم.
تنظیم mockCreditScorer.Setup را نیز در قسمت سوم این سری «تنظیم مقادیر خواص اشیاء» بررسی کردیم.

در ادامه، متد Process کلاس LoanApplicationProcessor فراخوانی شده‌است. اکنون با استفاده از متد Verify کتابخانه‌ی Moq، می‌توان بررسی کرد که آیا در سیستم در حال آزمایش، متدی که توسط آن به صورت strongly typed مشخص می‌شود، فراخوانی شده‌است یا خیر؟

پس از این تنظیمات اگر متد آزمایش واحد InitializeIdentityVerifier را بررسی کنیم با موفقیت به پایان خواهد رسید. برای نمونه یکبار هم سطر فراخوانی متد Initialize را کامنت کنید و سپس این آزمایش را اجرا نمائید تا بتوان شکست آن‌را نیز مشاهده کرد.


بررسی فراخوانی شدن یک متد پارامتر دار بر روی یک Mock object

همان متد آزمون واحد InitializeIdentityVerifier را درنظر بگیرید، در انتهای آن یک سطر زیر را نیز اضافه می‌کنیم:
mockCreditScorer.Verify(x => x.CalculateScore(applicant.Name, applicant.Address));
به این ترتیب می‌توان دقیقا بررسی کرد که آیا در حین پردازش LoanApplicationProcessor، متد CalculateScore وابستگی creditScorer آن، با پارامترهایی که در آزمون فوق مشخص شده، فراخوانی شده‌است یا خیر؟
بدیهی است اگر در این بین، متد CalculateScore با هر مقدار دیگری در کلاس LoanApplicationProcessor فراخوانی شود، آزمون فوق با شکست مواجه خواهد شد. اگر در اینجا مقدار پارامترها اهمیتی نداشتند، همانند قسمت دوم می‌توان از ()<It.IsAny<string استفاده کرد.


بررسی تعداد بار فراخوانی یک متد بر روی یک Mock object

برای بررسی تعداد بار فراخوانی یک متد بر روی یک شیء Mock شده، می‌توان از پارامتر دوم متد Verify استفاده کرد:
mockCreditScorer.Verify(x => 
        x.CalculateScore(It.IsAny<string>(), applicant.Address), 
        Times.Once);
ساختار Times، دارای متدهایی مانند AtLeast ،AtMost ،Exactly و امثال آن است که انعطاف پذیری بیشتری را به آن می‌دهند.


بررسی فراخوانی Getter و Setter خواص یک شیء Mock شده

علاوه بر امکان دریافتن وقوع فراخوانی یک متد، می‌توان از خوانده شدن و یا تغییر مقدار یک خاصیت نیز توسط کتابخانه‌ی Moq مطلع شد. برای مثال در قسمتی از کدهای متد Process داریم:
if (_creditScorer.ScoreResult.ScoreValue.Score < MinimumCreditScore)
اکنون می‌خواهیم بررسی کنیم که آیا Getter خاصیت Score فراخوانی شده‌است یا خیر؟
mockCreditScorer.VerifyGet(x => x.ScoreResult.ScoreValue.Score, Times.Once);
در اینجا بجای استفاده از متد Verify از متد VerifyGet برای بررسی وقوع خوانده شدن مقدار یک خاصیت می‌توان استفاده کرد.
جهت بررسی تغییر مقدار یک متغیر بر روی یک شیء Mock شده، می‌توان از متد VerifySet کمک گرفت:
mockCreditScorer.VerifySet(x => x.Count = It.IsAny<int>(), Times.Once);
به این ترتیب می‌توان دقیقا مقداری را که انتظار داریم مشخص کنیم و یا می‌توان هر مقداری را نیز توسط کلاس It، پذیرفت. البته در این مورد روش زیر برای بررسی تغییر مقدار یک خاصیت که در قسمت قبل بررسی شد، شاید روش بهتر و متداول‌تری باشد:
mockCreditScorer.SetupProperty(x => x.Count, 10);
Assert.AreEqual(11, mockCreditScorer.Object.Count);


روش بررسی فراخوانی تمام متدها و تمام خواص یک شیء Mock شده

با استفاده از متد زیر می‌توان از «نوشتن شده بودن» آزمایش مورد استفاده قرار گرفتن تمام متدها و خواص یک شیء Mock شده، مطمئن شد:
mockIdentityVerifier.VerifyNoOtherCalls();
اگر برای مثال این سطر را به انتهای متد InitializeIdentityVerifier اضافه کنیم، با شکست مواجه می‌شود و در پیام استثنای آن دقیقا عنوان می‌کند که چه مواردی هنوز فاقد آزمون واحد هستند و باید اضافه شوند:
 mockIdentityVerifier.Verify(x => x.Validate(It.IsAny<string>(),
                                                        It.IsAny<int>(),
                                                        It.IsAny<string>()));

کدهای کامل این قسمت را از اینجا می‌توانید دریافت کنید: MoqSeries-4.zip
‫۵ سال قبل، شنبه ۲۰ مهر ۱۳۹۸، ساعت ۱۸:۱۵
وحید نصیری وحید نصیری
مطالب
سرورهای متصل شده‌ی SQL Server و مبحث تراکنش‌ها

یکی از قابلیت‌های جالب SQL Server در یک شبکه محلی امکان link و اتصال آن‌ها به یکدیگر است. به این صورت امکان کوئری گرفتن (و یا اعمال متداول SQL ایی) از دو یا چند سرور مختلف با دستورات T-SQL میسر می‌شود؛ به نحوی که حس یکپارچگی دیتابیس‌های این سرورها را حین کوئری نوشتن خواهیم داشت.
برای مثال فرض کنید دو سرور SQL1 و SQL2 را در شبکه داریم. می‌خواهیم در سرور SQL1 اتصالی را به سرور SQL2 ایجاد کنیم.

USE master

EXEC sp_addlinkedserver
 'SQL2',
 N'SQL Server'

sp_addlinkedsrvlogin @useself='false ', @rmtsrvname = 'SQL2',
@rmtuser = 'sa',
@rmtpassword = 'pass#'

دستورات T-SQL فوق کار ثبت یک liked server جدید و اعمال مشخصات کاربری که توسط آن قرار است به سرور SQL2 دسترسی داشت، انجام می‌دهند.
اکنون جهت بررسی این اتصال در سرور SQL1 کوئری زیر را اجرا می‌کنیم:

select * from sql2.faxManager.dbo.tblErja

که نحوه‌ی فراخوانی جدول مورد نظر باید به صورت Server.DatabaseName.dbo.TableName در آن رعایت شود.
تا اینجا همه چیز خوب است. مشکل از زمانی شروع می‌شود که بخواهیم تراکنش‌ها را نیز دخالت دهیم و اصولی کار کنیم. برای مثال:

begin distributed tran
select * from sql2.faxManager.dbo.tblErja
commit tran

خطایی که در ویندوز سرور 2003 با آخرین به روز رسانی‌ها ظاهر می‌شود به صورت زیر است:

The operation could not be performed because OLE DB provider for linked server was unable to begin a distributed transaction.
OLE DB provider for linked server returned message "The partner transaction manager has disabled its support for remote/network transactions.".


به صورت پیش فرض این نوع تراکنش‌های توزیع شده غیرفعال هستند مگر اینکه فعال شوند و روش حل مشکل نیز به صورت زیر می‌باشد:
قبل از هر کاری به کنسول سرویس‌های ویندوز مراجعه کرده و از در حال اجرا بودن سرویس Distribute Transaction Coordinator اطمینان حاصل کنید.
سپس به قسمت زیر مراجعه نمائید:
Control Panel > Administrative Tools > Component Services


نود مربوط به Component Service را گشوده و سپس بر روی My Computer کلیک راست کرده و گزینه‌ی خواص را انتخاب کنید.
در صفحه‌ی بازه شده به برگه‌ی MSTDC مراجعه کرده و بر روی دکمه‌ی Security Configuration کلیک نمائید.
اکنون تنظیمات آن‌را مطابق شکل زیر تغییر دهید.


این تنظیم باید بر روی هر دو سرور SQL1 و SQL2 انجام شود.

پس از این تغییرات که شامل راه اندازی مجدد سرویس Distribute Transaction Coordinator نیز خواهد شد، مشکل خطای فوق برطرف شده و امکان استفاده از تراکنش‌ها در linked servers نیز میسر می‌شود.

مشکل دیگری که به آن برخوردم خطای زیر است:

Unable to start a nested transaction for OLE DB provider for linked server . A nested transaction was required because the XACT_ABORT option was set to OFF.
OLE DB provider for linked server returned message "Cannot start more transactions on this session.".


برای حل این مشکل یک سطر زیر را باید به ابتدای کوئری خود اضافه کرد که جزو الزامات تراکنش‌های توزیع شده است و به این صورت از rollback کامل تمامی دستورات موجود فراخوانی شده T-SQL در صورت بروز کوچکترین خطایی اطمینان حاصل می‌کند:
SET XACT_ABORT ON


برای مطالعه بیشتر:
MSDTC Troubleshooting

‫۱۴ سال و ۱۰ ماه قبل، پنجشنبه ۱ بهمن ۱۳۸۸، ساعت ۲۱:۰۰
وحید نصیری وحید نصیری
مطالب
چک لیست نصب SQL Server

عموما هنگام نصب SQL Server ، پیش و پس از آن، بهتر است موارد زیر جهت بالا بردن کیفیت و کارآیی سرور، رعایت شوند:

1- پیش فرض‌های نصب SQL Server در مورد محل قرارگیری فایل‌های دیتا و لاگ و غیره صحیح نیست. هر کدام باید در یک درایو مجزا مسیر دهی شوند برای مثال:
Data drive D:
Transaction Log drive E:
TempDB drive F:
Backup drive G:
این مورد TempDB را کسانی که با SharePoint کار کرده باشند به خوبی علتش را درک خواهند کرد. پیش فرض نصب افراد تازه کار، نصب SQL Server و تمام مخلفات آن در همان درایو ویندوز است (یعنی همان چندبار کلیک بر روی دکمه‌ی Next برای نصب). SharePoint هم به نحو مطلوبی تمام کارهایش مبتنی بر transactions است. یعنی استفاده‌ی کامل از TempDB . نتیجه؟ پس از مراجعه به درایو ویندوز مشاهده خواهید کرد که فقط چند مگ فضای خالی باقی مانده! حالا اینجا است که بدو این مقاله و اون مقاله رو بخون که چطور TempDB را باید از درایو C به جای دیگری منتقل کرد. چیزی که همان زمان نصب اولیه SQL Server باید در مورد آن فکر می‌شد و نه الان که سیستم از کار افتاده.
همچنین وجود این مسیرهای مشخص و پیش فرض و آگاهی از سطوح دسترسی مورد نیاز آن‌ها، از سر دردهای بعدی جلوگیری خواهد کرد. برای مثال : انتقال فایل‌های دیتابیس اس کیوال سرور 2008

2- پس از رعایت مورد 1 ، نوبت به تنظیمات آنتی ویروس نصب شده روی سرور است. این پوشه‌های ویژه را که جهت فایل‌های دیتا و لاگ و غیره بر روی درایوهای مختلف معرفی کرده‌اید یا خواهید نمود، باید از تنظیمات آنتی ویروس شما Exclude شوند. همچنین در حالت کلی فایل‌هایی با پسوندهای LDF/MDF/NDF باید جزو فایل‌های صرفنظر شونده از دید آنتی ویروس شما معرفی گردند.
این مورد علاوه بر بالا بردن کارآیی SQL Server ، در حین Boot سیستم نیز تاثیر گذار است. گاها دیده شده است که آنتی ویروس‌ها این فایل‌های حجیم را در حین راه اندازی اولیه سیستم، پیش از SQL Server ، جهت بررسی گشوده و به علت حجم بالای آن‌ها این قفل‌ها تا مدتی رها نخواهند شد. در نتیجه آغاز سرویس SQL Server را با مشکلات جدی مواجه خواهند کرد که عموما عیب یابی آن کار ساده‌ای نیست.

3- پیش فرض میزان حافظه‌ی مصرفی SQL Server صحیح نیست. این مورد باید دقیقا بلافاصله پس از پایان عملیات نصب اولیه اصلاح شود. برای مطالعه بیشتر: تنظیمات پیشنهادی حداکثر حافظه‌ی مصرفی اس کیوال سرور

4- آیا مطمئن هستید که از تمام امکانات نگارش جدید SQL Server ایی که نصب کرده‌اید در حال استفاده می‌باشید؟
برای مطالعه بیشتر: تنظیم درجه سازگاری یک دیتابیس اس کیوال سرور

5- بهتر است فشرده سازی خودکار بک آپ‌ها در SQL Server 2008 فعال شوند.
برای مطالعه بیشتر: +

6- از paging بیش از حد اطلاعات، از حافظه‌ی فیزیکی سرور به virtual memory و انتقال آن به سخت دیسک سیستم جلوگیری کنید. برای این منظور:
در قسمت Run ویندوز تاپیک کنید : GPEDIT.MSC و پس از اجرای آن با مراجعه به Group policy editor ظاهر شده به مسیر زیر مراجعه کنید:
windows settings -> security settings -> local policies -> user rights assignment -> lock pages in memory
در اینجا به یوزر اکانت سرویس SQL Server دسترسی lock pages in memory را بدهید.
علاوه بر آن در همین قسمت (user rights assignment) گزینه‌ی "Perform Volume Maintenance tasks" را نیز یافته و دسترسی لازم را به یوزر اکانت سرویس SQL Server بدهید.

7- به روز رسانی اطلاعات آماری SQL Server را به حالت غیرهمزمان تنظیم کنید.
اگر مطالب مرتبط با SQL Server این سایت را مرور کرده باشید حتما با یک سری DMV که دقیقا به شما خواهند گفت بر اساس اطلاعات آماری جمع شده برای مثال بهتر است روی چه فیلدهایی Index درست کنید، آشنا شده‌اید. حالت پیش فرض به روز رسانی این اطلاعات آماری، synchronous است یا همزمان. به این معنا که تا اطلاعات آماری یک کوئری ذخیره نشود، حاصل کوئری به کاربر بازگشت داده نخواهد شد که این امر می‌تواند بر روی کارآیی سیستم تاثیر گذار باشد. اما امکان تنظیم آن به حالت غیر همزمان نیز مطابق کوئری‌های زیر وجود دارد (این مورد از SQL Server 2005 به بعد اضافه شده است): 

ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE dbName SET AUTO_UPDATE_STATISTICS_ASYNC ON

8- نصب آخرین سرویس پک موجود فراموش نشود. برای مثال این سایت آمار تمام به روز رسانی‌ها را نگهداری می‌کند.

9- حتما رویه‌ای را برای تهیه بک آپ‌های خودکار پیش بینی کنید. برای مثال : +

10- میزان فضای خالی باقیمانده درایوهای سرور را مونیتور کنید. اطلاعات بیشتر: +

11- با نصب سرور جدید و تنظیم collation آن به فارسی، به نکات "یافتن تداخلات Collations در SQL Server" دقت داشته باشید.

‫۱۴ سال و ۱ ماه قبل، پنجشنبه ۲۲ مهر ۱۳۸۹، ساعت ۲۰:۳۶
وحید نصیری وحید نصیری
مطالب
مشکل همزمانی خواندن و به روز رسانی اطلاعات در برنامه‌های وب
فرض کنید در برنامه‌ی خود «کیف پولی» را طراحی کرده‌اید که بر اساس آن، کاربر می‌تواند خرید کند. این کیف پول، از Id کاربر و موجودی فعلی او تشکیل می‌شود:
CREATE TABLE accounts (
user_id INTEGER PRIMARY KEY,
balance INTEGER NOT NULL
);
و برای مثال موجودی فعلی کاربر 1، مقدار 300 است:
INSERT INTO accounts(user_id, balance)
VALUES (1, 300);
اکنون کوئری‌های متداول زیر را که از یک read و سپس update تشکیل شده‌اند، درنظر بگیرید:
DECLARE @amount INT;

SET @amount = (
SELECT balance
FROM accounts
WHERE user_id = 1
);

SELECT @amount as 'balance'

UPDATE accounts
SET balance =  @amount - 100
WHERE user_id = 1;

SELECT balance as 'balance after shopping'
FROM accounts
WHERE user_id = 1
- دو عمل read و سپس update صورت گرفته‌ی فوق، مربوط به یک درخواست خرید است.
- در اینجا مقدار متغیر amount در ابتدای کار، مساوی 300 است که مربوط به همان insert ابتدایی است.
- سپس از این مقدار در کوئری دومی (برای مثال حاصل از خرید شماره یک)، 100 واحد کم می‌شود (برای مثال قیمت کل خرید است).
- در این حالت نتیجه‌ی آن یا همان موجودی جدید کاربر، 200 خواهد بود.

معادل این عملیات در EF-Core چنین دستورات متداولی است:
var account1 =  context.Accounts.First(x => x.UserId == 1);
account1.Balance -= 100;
context.SaveChanges();

سؤال: اگر کوئری‌های فوق را در یک برنامه‌ی ذاتا چند ریسمانی وب، دوبار به صورت همزمان اجرا کنیم، یعنی دو عمل خرید موازی را شبیه سازی کنیم، چه اتفاقی رخ می‌دهد؟ آیا موجودی نهایی اینبار برای مثال 100 می‌شود (با فرض 300 بودن موجودی ابتدایی)؟
پاسخ خیر است! و آن‌را می‌توانید در تصویر زیر مشاهده کنید:



در اینجا برای شبیه سازی اجرای موازی دو کوئری، از دستور WAITFOR TIME استفاده شده‌است که برای برای آزمایش آن می‌توانید مقدار آن‌را به یک دقیقه بعد تنظیم کرده و سپس آن‌را در دو پنجره‌ی SQL server management studio اجرا کنید.
همانطور که مشاهده می‌کنید، با اجرای موازی این دو کوئری، یعنی دوبار خرید کردن همزمان، 100 واحد گم شده‌است ! به این مشکل همزمانی read و سپس update رخ داده، یک «race condition» گفته می‌شود و این روزها که مطالب منتشر شده‌ی از آسیب پذیری‌های برنامه‌های وب ایرانی را بررسی می‌کنم، این مورد در صدر آن‌ها قرار دارد!
علت اینجا است که عموما برنامه نویس‌ها، برنامه‌های وب را در یک تک سشن باز شده‌ی توسط مرورگر خود آزمایش می‌کنند و در این حالت، همه چیز خوب است و اعمال آن به ترتیب پیش می‌روند. اما فراموش می‌کنند که می‌توان قسمت‌های مختلف برنامه‌های وب را به صورت همزمان، موازی و چندباره نیز اجرا کرد؛ حتی اگر آن قسمت متعلق به یک کاربر باشد.


سؤال: آیا استفاده تراکنش‌ها این مشکل را حل نمی‌کنند؟!

عموما برنامه نویس‌ها تصور می‌کنند که می‌توانند تمام اینگونه مشکلات را با تراکنش‌ها حل کنند:



همانطور که مشاهده می‌کنید، اینبار هرچند هر دو عملیات خرید داخل BEGIN TRAN و COMMIT TRAN قرار گرفته‌اند، اما ... مشکل همزمانی هنوز پابرجا است! چون نوع پیش‌فرض تراکنش مورد استفاده، READ COMMITTED isolation level است و عدم دقت به آن ممکن است این تصور را ایجاد کند که با تعریف تراکنش‌ها، تمام مشکلات همزمانی برطرف می‌شوند.


راه‌حل‌های پیشنهادی جهت حل مشکل همزمانی عملیات read/update

برای حل مشکلات مرتبط با race condition و همزمانی درخواست‌های read/update، می‌توان از یکی از روش‌های زیر استفاده کرد:
الف) بجای اینکه یکبار کوئری read و یکبار کوئری update به صورت جداگانه صادر شوند، فقط یکبار کوئری update داشته باشیم.
ب) پیاده سازی Row level locking؛ در صورت پشتیبانی بانک اطلاعاتی مورد استفاده از آن
ج) استفاده از تراکنش‌هایی از نوع SERIALIZABLE
د) پیاده سازی optimistic locking

این موارد را در ادامه با توضیحات بیشتری بررسی می‌کنیم.


الف) پرهیز از خواندن و به روز رسانی جداگانه

بجای اینکه مانند اعمال فوق، یکبار select داشته باشیم و یکبار  update، بهتر است فقط یک دستور update بکارگرفته شود:
UPDATE accounts
SET balance =  balance - 100
WHERE user_id = 1;


اینبار با خلاصه شدن دو دستور select و update به یک دستور update، دیگر پس از دو خرید همزمان، 100 واحد گم شده مشاهده نمی‌شود (!) و موجودی نهایی صحیح است.


ب) پیاده سازی Row level locking

همیشه امکان تغییر عملیات مورد نیاز، به سادگی حالت الف نیست. در یک چنین حالت‌هایی جهت حداقل شدن تغییرات مورد نیاز، می‌توان از row level locking استفاده کرد:
WAITFOR TIME '13:47:00';

SET NOCOUNT, XACT_ABORT ON;

BEGIN TRAN;

DECLARE @amount INT;

SET @amount = (
 SELECT balance
 FROM accounts WITH (UPDLOCK, HOLDLOCK)
 WHERE user_id = 1
 );

SELECT @amount as 'initial user''s balance'

UPDATE accounts
SET balance =  @amount - 100
WHERE user_id = 1;

SELECT balance as 'user''s balance after shopping 1'
FROM accounts
WHERE user_id = 1;

COMMIT TRAN;



در اینجا اضافه شدن WITH (UPDLOCK, HOLDLOCK) را به Select تعریف شده، مشاهده می‌کنید که به آن‌ها locking hints هم گفته می‌شود و داخل BEGIN TRAN و COMMIT TRAN عمل می‌کنند (که نوع پیش‌فرض آن READ COMMITTED isolation level است). کار UPDLOCK، تبدیل shared lock پیش‌فرض، به update lock است و کار HOLDLOCK، نگه داشتن قفل صورت گرفته تا پایان کار تراکنش تعریف شده‌است.
با این تغییرات، هر تراکنش همزمان دیگری، تا زمانیکه قفل صورت گرفته‌ی بر روی ردیف select، رها نشود (یعنی تا زمانیکه تراکنش قفل کننده، به COMMIT TRAN برسد)، نمی‌تواند آن‌را تغییر دهد. به همین جهت است که در تصویر فوق، هرچند هر دو عملیات همزمان اجرا شده‌اند، اما یکی موجودی ابتدایی 300 را می‌بیند و دیگری پس از صبر کردن تا پایان تراکنش و رها شدن قفل، موجودی تغییر یافته‌ی جدیدی را مشاهده کرده و از آن استفاده می‌کند. به این ترتیب دیگر 100 واحدی که در اولین تصویر این مطلب مشاهده کردید، گم نشده‌است.


ج) استفاده از تراکنش‌هایی از نوع SERIALIZABLE

بجای استفاده از روش row level locking یاد شده، روش دیگری را که می‌توان استفاده کرد، تغییر نوع پیش‌فرض تراکنش مورد استفاده‌است. برای مثال اگر از یک SERIALIZABLE transaction استفاده کنیم؛ یعنی SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  را در ابتدای کار ذکر کنیم و برای مثال دو تراکنش همزمان را اجرا کنیم، اگر در تراکنش اول اطلاعاتی خوانده شود، در هیچ تراکنش دیگری نمی‌توان این اطلاعات خوانده شده را تا پایان کار تراکنش اول، تغییر داد:




د) پیاده سازی optimistic locking

پیاده سازی optimistic locking و یا Optimistic concurrency control عموما در سمت برنامه رخ می‌دهد و توسط ORMها زیاد مورد استفاده قرار می‌گیرد؛ مانند اضافه کردن ستون اضافی version و یا timestamp به جداول تعریف شده. در این حالت تمام updateها به همراه یک where اضافی هستند تا بررسی کنند که آیا version دریافتی در حین خواندن ردیف در حال به روز رسانی، تغییر کرده‌است یا خیر؟ اگر تغییر کرده‌است، تراکنش را با خطایی خاتمه خواهند داد. این روش برخلاف حالت‌های ب و ج، حتی خارج از یک تراکنش نیز کار می‌کند و مشکلات قفل کردن طولانی مدت رکوردها توسط آن‌ها را به همراه ندارد.
‫۲ سال و ۱ ماه قبل، دوشنبه ۳۱ مرداد ۱۴۰۱، ساعت ۱۹:۴۵