SQL - Concurrent operations | Problems | Solutions

Concurrent Operations: When more than one user(s) / application(s) attempt operation (select / insert / update) on same data row(s), this is called Concurrent operation.

Concurrent operations may cause below given issues -

1. Dirty Reads: If session 2 executed during the 15 second delay. Session 2 will get updated data by session 1, but then session 1 get rolled back, so session 2 got wrong data its called Dirty Read.

Session 1

Begin tran

update tb_city set regionid = 2 where id between 11 and 20

waitfor delay '00:00:15'

--Commit

Rollback

Session 2

select * from tb_city where id = 15

2. Unrepeatable Reads:  If all these session executing parallel then 4 select statements of session 1 will get different values every time.

Session 1

select * from tb_city where id between 100 and 200

select * from tb_city where id between 100 and 200

select * from tb_city where id between 100 and 200

select * from tb_city where id between 100 and 200

Session 2 update tb_city set regionid = 2 where id between 100 and 200

Session 3 update tb_city set regionid = 4 where id between 100 and 200

Session 4 update tb_city set regionid = 9 where id between 100 and 200

Session 5 update tb_city set regionid = 3 where id between 100 and 200

3. Phantom Rows: User looking to set 20 for all active records. Session 2 is running parallel added new active values. These new rows are Phantom Rows.

Session 1

update tb_city set regionid = 20 where IsActive = 1

Session 2

insert into tb_city values (201, 1)

insert into tb_city values (202, 1)

insert into tb_city values (203, 1)

4. Lost Update: user is looking to set region id to 200, but parallel session 2 set it to 100. so user 1 lost his updates.

Session 1

update tb_city set regionid = 200 where IsActive = 1

Session 2

update tb_city set regionid = 100 where IsActive = 1

--------------------------------------------------------------------------

Solutions for these problems

1. Optimistic Lock
2. Pessimistic Lock

out Vs ref

Both are used to pass variable(s) as a reference from one function to another function.
out: It does not carry existing value of variable and ask to initialize it in function 2, but returns the new out put value. so its one way communication.

ref: It carries existing value of variable in function 1, and after operations on existing value returns the out put value, so its two way communication.

    static void Method(out int i, ref int j)

    {

        i = 50; //initialization required for out but not for ref

  i = i + 10;
  j = j + 10; 

    }

    static void Main()

    {

        int value1 = 20, value2 = 20;

        Method(out value1, ref value2);

        // value1 is now 60

        // value2 is now 30

    }

Keywords - checked & unchecked

First understand scenarios where we can use checked & unchecked keywords

C# compiler does not throws any error at compile or run time for arithmetic overflow.
j has assigned wrong value.

checked:  Used to check arithmetic overflow, it throws arithmetic overflow exception in case of overflow.

C# compiler checks for arithmetic overflow if constant(s) involved in operation and gives compile time error.

unchecked: unchecked is used to suppress checking for arithmetic overflow by compiler

Delegate in .Net

A delegate is a type that defines a method signature. When you create an instance of the delegate, you can associate a method to the instance of compatible signature, now you can call or invoke that method by the delegate instance.

1. Delegates are used to pass a method as argument to other method. 
2. A delegate instance can be assigned a method of any accessible class or struct that matches signature of delegate, method can be static or instance method.
3. Delegate types are sealed they can not be derived from. (Sealed restricts inheritance of any class, like struct can't be inherited as they are sealed)
4. Instance of delegate is an object so it can be assign to a property or can be pass to a method as parameter.
5. Delegate can be multicast.

Using delegate is 4 step process 

1. Declare delegate 
2. Create instance / object of delegate (Create delegate pointer)
3. Reference delegate instance to one or more methods with same signature
4. Call delegate 

Example

namespace ClassLibrary1

{

    public class Class1

    {

        //-1-Declare delegate

        public delegate string DelSaveData(string msg);

        public DelSaveData SaveClientData()

        {

            //-2-Create delegate pointer (object)

            DelSaveData delObj = null;

           

            //-3-Point method(s) / reference to method(s)

            delObj += SaveData;

            delObj += SendMailtoClient;
            delObj += SendSMStoClient;

            return delObj;           

        }

       

        private string SaveData(string msg)

        {

            return "Data saved at : " + DateTime.Now.ToString();

        }

        private string SendMailtoClient(string msg)

        {

            return "Email sent to client at : " + DateTime.Now.ToString();

        }

        private string SendSMStoClient(string msg)

        {

            return "SMS sent to "+ msg +" at : " + DateTime.Now.ToString();

        }

    }

}

Client Code

private void button1_Click(object sender, EventArgs e)

{

   Class1 objclass1 = new Class1();

   Class1.DelSaveData objDelSaveData = objclass1.SaveClientData();

           

   //-4-Execute/call the delegate

   string s = objDelSaveData.Invoke("Client1");

}

Point to be noted in delegate multi casting : 

1. Passed parameter(s) is available inside all assigned methods. Here "Client1" will be available to all three assigned method in msg variable.
2. Delegate returns output returned by last called method. Here delegate invoke will return output returned by SendSMStoClient.

string s = objDelSaveData.Invoke("Client1");

Implicit Vs Explicit Data Types Conversion & Boxing Vs Unboxing

Implicit Conversion: Type safe conversion that not required any special syntax. No data lose because its type safe conversion.

//Implicit conversions

int i = 10000;

double d = i;

           

DerivedType objD = new DerivedType();

BaseType objB = objD;

Explicit Conversion: Converting or casting one datatype to another data type using same casting operators is called Explicit conversion.
This conversion may succeed or not.
This conversion may lose the data.

//Explicit conversions

int j = 1;

bool b = Convert.ToBoolean(j);

           

float f = 232.32f;

int k = (int)f;

                       

BaseType objBase = new BaseType();

DerivedType objDrived = (DerivedType)objBase;

-------------------------------------------------------------------------------
Value Type Vs Reference Type

When a variable is declared using one of the basic, built-in data types or a user defined structure, it is called a Value type. string is an exception because its basic data type but it is reference type.

Instances, arrays kind of types are Reference type

Lets checkout how .net stores these types in memory. How below given variables will be stored.

    //Boxing (Implicit conversion)

    int i = 2;         //1  

    object obj = i;    //2

1. i is value type so stored in memory allocated in managed stack
2. New reference type created and stored in stack, in stack memory it contain address(1221) of actual data. Memory allocated in managed heap for new created reference type 'obj' to store actual value. assigned value from i that is 2 is stored here.

    int j = 5;         //3

    obj = j;           //4

    //Unboxing (Explicit conversion)

    int k = (int)obj;  //5

1. New value type j stored in stack
2. j is assigned to 'obj', so new memory location(3421) allocated in heap to store new assigned value from j that is 5. Now address contained by obj is changed to new location 3421.
3. Memory location 1221 is still there and it will be reclaimed by GC later.
4. New value type k is created and allocated in stack, and assigned value from obj that is 5.

     k = i;  //6

1. Value of k is overridden by value of i

Point to be noted: 

1. When assign new value to a value type, value overridden and not required new memory allocation.
2. As execution of memory block completed where the value type is declared, memory get released. As closing parenthesis executed memory used by ID will be released.
   
           private void SendSMStoClient()
           {
               int ID = 50;
           }
3. When we assign new value to a reference type new memory allocated, and old one still exist there that is reclaimed by GC later. that is memory & performance overhead with reference types.
4. Reference types are useful to pass them one function to other. While when we pass value type new copy passes of value type.

-------------------------------------------------------------------------------

Boxing: Converting value type to reference type. This is implicit conversion.

Unboxing: Converting reference type to value type. This is explicit conversion.

    //Boxing (Implicit conversion)

    int i = 2;

    object obj = i;

    //Unboxing (Explicit conversion)

    int k = (int)obj;

Why Boxing and Unboxing downgrades performance?

There is specific storage system for value types and for reference types.

Value types are stored in stack.

Reference types' reference pointer (address) is stored in stack but actual data stored in heap.

So whenever we perform Boxing or Unboxing .net internally changes the storage of data that is overhead so it downgrade performance.

String Vs StringBuilder

string is Immutable it means when we initialize a newly created string value it get stored in memory then if we assign some new value to same variable new value stored on new memory place and variable now refer this new memory place, old value still exist in memory.
If again assign a third value to same variable, again new value get stored on new memory location,
and variable refer it now, still previous two have existence.
Conclusion: value of string can't be update actually
string s = "Hello";

StringBuilder :  Value of stringBuilder can be updated. So it is recommended to use for better performance.
StringBuilder s = new StringBuilder();
s = s.Append("Hello");

Useful data types in .Net

S.No	Data Type	Size	Min Value	Max Value	Description
1	Byte	1 Byte (8 bit)
2	Char	2 Bytes			Each character contains 2 Bytes
3	String	Assigned chars * 2 Bytes
4	Int16 / Short	16 bit (2 Bytes)	-32768	32767
5	UInt16 /ushort	16 bit (2 Bytes)	0	65536
6	Int32 / int	32 bit (4 Bytes)	-2147483647	2147483648
7	UInt32 / uint	32 bit (4 Bytes)	0	4294967296
8	Int64 / long	64 bit (8 Bytes)	Neg (2 pow (64) / 2 -1)	2 pow (64) / 2
9	UInt64 / ulong	64 bit (8 Bytes)	0	2 pow (64)
10	bool
11	DateTime
12	float	4 Bytes			7 deccimal places
13	decimal	8 Bytes			16 decimal places
14	double	16 Bytes			29 decimal places