What is Repeatable Read problem and how to solve it?

Let’s replicate the problem, for which I am creating a table StudentMarks and insert three records

create table studentMarks (

       id int

       ,name varchar(50)

       ,total_marks int

       )

    insert into studentMarks(id,name,total_marks) values( 1,'Prayansh',430);

    insert into studentMarks(id,name,total_marks) values( 2,'Rishika',345);

    insert into studentMarks(id,name,total_marks) values( 3,'Aayansh',390);         

Now lets open a transaction in a new session and try to read this table twice, and in between two read keep a delay of 18 Sec

Open Session1, and execute below transaction

    begin tran

    select * from studentMarks where ID in(1,2)

    waitfor delay '00:00:18'

    select * from studentMarks where ID in (1,2)

    rollback

Parallely open another session and execute below session

update studentMarks

set    total_marks=499 where id=1

Now the output from the first session you will find

 id          name         total_marks

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

1           Prayansh      430

2           Rishika       345

(2 row(s) affected)

id          name          total_marks

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

1           Prayansh      499

2           Rishika       345

(2 row(s) affected)

Here if you look at session1 output, a same select statement gives two different outputs, that is because , while session one is waiting for 18 second, session two updated the marks of id=1 to 499.

This is a problem with repeatable read if any outside transaction updates the data in between two reads.

How to avoid it

It can be avoided by setting transaction isolation level to repeatable read, let’s see how

set transaction isolation level repeatable read

    begin tran

    select * from studentMarks where ID in(1,2)

    waitfor delay '00:00:15'

    select * from studentMarks where ID in (1,2)

    rollback

Now here what will happen, if there is any outside transaction tries to modify the data which affect the Transaction one query then, the outside transaction will go on wait for getting update lock until transaction1 complete its operation.

Post Reference: Vikram Aristocratic Elfin Share

Commonly used List methods in Python

Below are the basic frequently used methods of List

list1 = [1,2,3]
list1.append(3)
print('1.Append: After appending value 3 at the end of list1 : ',list1)
Output:

1.Append: After appending value 3 at the end of list1 :  [1, 2, 3, 3]

list2 = [10,11]
list1.append(list2)
print('\n2.Append: After appending list2 with list1 : ',list1)
Output

2.Append: After appending list2 with list1 :  [1, 2, 3, 3, [10, 11]]

list1.insert(5,4)
print('\n3.Insert: After inserting value 4 at postion 5 : ',list1)
Output

3.Insert: After inserting value 4 at postion 5 :  [1, 2, 3, 3, [10, 11], 4]

list1.insert(6,list2)
print('\n4.Insert: After inserting value list2 [10,11] at postion 6 : ',list1)
Output

4.Insert: After inserting value list2 [10,11] at postion 6 :  [1, 2, 3, 3, [10, 11], 4, [10, 11]]

a= list1.count([10,11])
print('\n5.Count: Count the occurance of sublist [10,11] in list1: ',a)
Output:

5.Count: Count the occurance of sublist [10,11] in list1:  2

list1.pop()
print('\n6.Pop: Poping up of last item of the list:',list1)
Output:

6.Pop: Poping up of last item of the list: [1, 2, 3, 3, [10, 11], 4]

list1.extend(list2)
print('\n7.Extend: Append will add as element whereas Extend will add as value:',list1)
Output:

7.Extend: Append will add as element whereas Extend will add as value: [1, 2, 3, 3, [10, 11], 4, 10, 11]

print('\n8.Reverse: List before sorting :',list2)
list2.sort(reverse=True)
print('\n8.ReverseList after sorting :',list2)
Output:

8.Reverse: List before sorting : [10, 11]

8.ReverseList after sorting : [11, 10]

i=list1.index(3)
print('First occurance of 3 in the list', i)

Output:

First occurance of 3 in the list 2

print('To use Min or Max from list, list should not be nested list')
print('Minimum from the list2 :', min(list2), '\nMaximum from the list2  :', max(list2))
Output:

To use Min or Max from list, list should not be nested list

Minimum from the list2 : 10

Maximum from the list2  : 11

print('del and Remove, del take index, remove take value to be deleted from list')
print('Before Del, list1 is :', list1)
del list1[0]
print('Del 0 index element from the list, new list :', list1)
list1.remove([11,10])
print('After removing [10,11] from the list1 :', list1)

Output:

del and Remove, del take index, remove take value to be deleted from list

Before Del, list1 is : [1, 2, 3, 3, [11, 10], 4, 10, 11]

Del 0 index element from the list, new list : [2, 3, 3, [11, 10], 4, 10, 11]

After removing [10,11] from the list1 : [2, 3, 3, 4, 10, 11]

Data Science with…Python J

Post Reference: Vikram Aristocratic Elfin Share

Deepcopy in Python

If any list is made from existing list then changing the existing list also changes the list which is formed using reference list, to avoid this situation deepcopy function is used to form the resultant list

Problem with below code, here the list c is made from

C = [a,b], which refer to the memory of a and b to get its value and that is the reason when a or b value changes the c list value also gets change.

a = ['neha','sharma']
b=[10,12]
print('a location:', id(a), ' b location:', id(b))
c=[a,b]
print('c list [a,b]:', c, ' c location :', id(c))
b.insert(2,13)
print('b after inserting 13', b)
print('b locatiton after inserting new value :', id(b))
print('c after changing b :  ',c, ' c location after change :', id(c))

Output

a location: 2639312  b location: 2640472

c list [a,b]: [['neha', 'sharma'], [10, 12]]  c location : 2996424

b after inserting 13 [10, 12, 13]

b locatiton after inserting new value : 2640472

c after changing b :   [['neha', 'sharma'], [10, 12, 13]]  c location after change : 2996424

Now, instead of creating new list with z= [x,y] , lets apply copy.deepcopy function, it will copy the content of list instead of referring to the list location , see below in the example

import copy
x=[1,2]
y=[4,5]
z=[copy.deepcopy(x),y]
print('Element of Z :', z)
print('\nLets change the value of X list, by appending')
x.append([3,6])
print('Value of X after appending another list:', x)
print('Now lets check the value of Z :',z)

Output:

Element of Z : [[1, 2], [4, 5]]

Lets change the value of X list, by appending

Value of X after appending another list: [1, 2, [3, 6]]

Now lets check the value of Z : [[1, 2], [4, 5]]

Here if you see, the value of c list didn’t change even though we changed the value of x that is because we took deepcopy of x list while creating the z list.

Data Science with…Python J

Post Reference: Vikram Aristocratic Elfin Share

List is mutable in Python

Lets check this out by writing a small script, which will create two list and copy both the list to form another list.

a = ['neha','sharma']
b=[10,12]
print('a location:', id(a), ' b location:', id(b))
c=[a,b]
print('c list [a,b]:', c, ' c location :', id(c))

Output:

a location: 14501328  b location: 14502488

c list [a,b]: [['neha', 'sharma'], [10, 12]]  c location : 51493064

Now lets insert a value at the end of second list that is b and check if it is affecting list c

b.insert(2,13)
print('b after inserting 13', b)
print('b locatiton after inserting new value :', id(b))
print(id(c))

Output

b after inserting 13 [10, 12, 13]

b locatiton after inserting new value : 14502488

c after changing b :   [['neha', 'sharma'], [10, 12, 13]]  c location after change : 51493064

Here we can see, changing the list of b, changed the list of c, without changing the memory location. So here we can see list is mutable.

Complete Code:

a = ['neha','sharma']
b=[10,12]
print('a location:', id(a), ' b location:', id(b))
c=[a,b]
print('c list [a,b]:', c, ' c location :', id(c))
b.insert(2,13)
print('b after inserting 13', b)
print('b locatiton after inserting new value :', id(b))
print('c after changing b :  ',c, ' c location after change :', id(c))

But what if we don’t want c to get changed with the change in b…. let’s check this in next post..

Data Science with…Python J

Post Reference: Vikram Aristocratic Elfin Share

Reversing a string in Python

It is quite simple in Python, it can be done easily using step parameter of string extract

>>> str = 'reverse'

>>> str[::-1]

'esrever'

With the help of step = -1, we can easily reverse any string in python.

Lets create a function, which will reverse the given string and then check if reverse = actual then return true else false.

def reverseStr(str):
    if str[::-1] == str:
        print('The result is True')
    else:
        print('The result is False')

str = 'liril'
reverseStr(str)

Output:

True

So here you can assign str with value ‘liril’ which in turn is passed to the function and the result was equal to the reverse of the string passed that’s why the result was true.

Data Science with…Python J

Post Reference: Vikram Aristocratic Elfin Share