0
Posted on Monday, January 22, 2018 by 醉·醉·鱼 and labeled under
之前分析过SQL SERVER的死锁,但基本都是基于READ COMMITTED下的死锁。玩得高级点的,就是key lookup lock。最近不幸玩了MySQL,拿原来的理解去尝试分析,结果不对,然后才发现,MySQL的默认隔离级别是REPEATABLE READ。呵呵~

在RR级别下,除了常规的RECORD LOCK,还有一个GAP LOCK。即两条记录之前的间隙。这样的话,就不会允许在范围内插入数据了。http://blog.csdn.net/wanghai__/article/details/7067118 这里有个很好的例子去模拟死锁。

至于分析锁,首先执行
set global innodb_status_output_locks=on;

然后,再执行
SHOW ENGINE INNODB STATUS \G
就可以拿到所有session的锁了。

-- session 1
mysql> start transaction;
mysql> delete from game_summaries where game_id = 2;

-- session 2
mysql> start transaction;
mysql> delete from game_summaries where game_id = 3;

-- session 1
mysql> insert into game_summaries(game_id, score) values (2, 0);
-- waiting

-- session 2
mysql> insert into game_summaries(game_id, score) values(3, 0);
-- deadlock occurs


Deadlock info

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-02-11 02:19:51 0x7ff5b7b83700
*** (1) TRANSACTION:
TRANSACTION 365986, ACTIVE 59 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 140693325752064, query id 1184 172.18.0.1 root update
insert into game_summaries(game_id, score) values (2, 0)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3445 page no 4 n bits 72 index index_game_summaries_on_game_id of table `TEST`.`game_summaries` trx id 365986 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 365987, ACTIVE 45 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 17, OS thread handle 140693326018304, query id 1186 172.18.0.1 root update
insert into game_summaries(game_id, score) values(3, 0)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3445 page no 4 n bits 72 index index_game_summaries_on_game_id of table `TEST`.`game_summaries` trx id 365987 lock_mode X locks gap before rec
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3445 page no 4 n bits 72 index index_game_summaries_on_game_id of table `TEST`.`game_summaries` trx id 365987 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000009; asc     ;;
 1: len 4; hex 80000002; asc     ;;

*** WE ROLL BACK TRANSACTION (2)




然后就按照下面两篇文章去分析锁就行了。


  1. http://keithlan.github.io/2017/06/21/innodb_locks_algorithms/
  2. http://keithlan.github.io/2017/06/05/innodb_locks_1/
如果你有SQL SERVER的背景知识,简单来说,就是基本的record lock(以及相关的index),加上gap lock。一旦有gap lock,这个范围内是不允许插入数据的。这就增加了死锁发生的几率。这种情况更多是发生在DELETE & INSERT 组合情况下。

0
Posted on Monday, October 30, 2017 by 醉·醉·鱼 and labeled under
故事是这样的,如果我在创建一个实例以后,再去编辑类并增加一个方法,这个实例是能够发现新的方法的。

class Dog
  def name
    
  end
end

a_dog = Dog.new

p a_dog.methods

class Dog
  def age
    
  end
end

p a_dog.methods

同理,在已经included 的module里增加一个新的方法。

module Professor
  def lectures
    
  end
  
end

class Mathematician
  attr_accessor :first_name, :last_name
  include Professor
end

fett = Mathematician.new

p fett.methods


module Professor
  def primary_classroom
    
  end
  
end

p fett.methods # this will have new method

但是,如果在已经included的module里面include一个新的module,这样就不行了。

module Employee
  def hired_date
    
  end
  
end

module Professor
  include Employee
end

p fett.methods # this will not have hired_date method until Mathematician included Professor again

原因在于,前两者影响的是方法表而已,而实例对应的klass里面留下的只是方法表pointer,而不是具体的方法。所以,在方法表里面增加方法是可行的。
但是,include 一个新的module,是会改变super pointer。在第一次include的时候,就已经“复制”好module并设置好了super pointer,不会再次改变。除非,重新打开类再include一次。


0
Posted on Friday, October 20, 2017 by 醉·醉·鱼 and labeled under
给指定的硬币类型,用最少的硬币个数,找出指定的amount。比如,现在有[2, 5, 10, 20, 50]这几种硬币,找出21块钱出来。

这个其实是算法导论里面动态规划。无意间,发现还有其他的实现方法。记录下来。

首先是动态规划。原来是,21块钱,可以拆分成19+2, 16+5, 11+10, 1+20。19又可以继续拆分成17+2, 14+5, 9+10。以此类推下去。里面有个技术点就是,还可以用block去定义Hash,厉害了。代码如下:

 def change(coins, amount, results = [])
    return [] if amount == 0

    coins.sort! { |a, b| a <=> b }

    optimal_change = Hash.new do |hash, key|

      if key < coins.min
        hash[key] = []
      elsif coins.include?(key)
        hash[key] = [key]
      else
        hash[key] = coins
            .reject { |coin| coin > key }
            .map { |coin| [coin] + hash[key - coin] }
            .reject { |change| change.inject(&:+) != key }
            .min { |a, b| a.size <=> b.size } || []
      end

      puts hash
      hash[key]
    end

    optimal_change[amount].empty? ? -1 : optimal_change[amount].sort
end



另外一种方法是,创建一个数组,从1到amount,然后逐一用coin去替代,最后一个就是amount对应的coin了。


  def change(coins, total_change)
    return -1 if total_change < 0 || coins.any? { |x| x < 1 }
    m = [[]] + [nil] * total_change
    coins.size
         .times
         .to_a
         .product((1..m.size - 1).to_a)
         .each { |c, t|
           if coins[c] == t
             m[t] = [coins[c]]
           else
             (1..t - 1).select { |t2| coins[c] + t2 == t }
                       .reject { |t2| m[t2].nil? }
                       .each { |t2| 
                         m[t] = m[t2] + [coins[c]] if m[t].nil? || m[t2].size + 1 < m[t].size 
                       }
           end
         }
    m[-1].nil? ? -1 : m[-1]
  end
0
Posted on Tuesday, October 17, 2017 by 醉·醉·鱼 and labeled under
Ruby的include和prepend有一个重要的知识点,就是多重包含的时候,后面的Module会被ignore掉,只会包含一次。


module C
  
end

module M
  
end

class B
  include M
  include C
end

p B.ancestors
# [B, C, M, Object, Kernel, BasicObject]

class A
  prepend M
  prepend C
end

p A.ancestors
# [C, M, A, Object, Kernel, BasicObject]


class D
  include M
  prepend C
end

p D.ancestors
# [C, D, M, Object, Kernel, BasicObject]

module M
  include C
end

class E
  prepend C
  include M
end

p E.ancestors
# [C, E, M, Object, Kernel, BasicObject]
0
Posted on Monday, September 25, 2017 by 醉·醉·鱼 and labeled under
http://exercism.io/exercises/ruby/poker/readme
来玩玩德州扑克~
德州扑克里面,每人会有五张牌,然后两个人比点。规则是先比牌型,再是点数,最后是花色。牌型一共有9种,从高到低依次是:同花顺,四条,葫芦,同花,顺子,三条,两对,一对,散牌。

基本思路是,针对每一手牌hand,依次判断其是哪种牌型,再讲改牌型的点数按大小排序,方便后面比点的时候用。

目前来看,这道题稍微有点难,只有30多个人完成了。


class Poker

  RANKING_CATEGORIES = {
    :straight_flush => 9,
    :square => 8,
    :full => 7,
    :flush => 6,
    :straight => 5,
    :three => 4,
    :two_pairs => 3,
    :one_pair => 2,
    :high_card => 1
  }

  CARD_RANKING = {
    '1' => 1,
    '2' => 2,
    '3' => 3,
    '4' => 4,
    '5' => 5,
    '6' => 6,
    '7' => 7,
    '8' => 8,
    '9' => 9,
    '10' => 10,
    'J' => 11,
    'Q' => 12,
    'K' => 13,
    'A' => 14
  }

  def initialize(hands)
    @hands = hands.map { |e| Hand.new(e).tap(&:hand_nubmer_groups) }
  end

  def best_hand
    largest_rank_hands = @hands.group_by { |e| e.rank }.sort.last.last #get the largest rank hands
    return largest_rank_hands.map(&:hand) if largest_rank_hands.size == 1
    largest_rank_hands.group_by { |e| e.point }.sort.last.last.map(&:hand)
  end


  class Hand
    attr_reader :hand
    def initialize(hand)
      @hand = hand
    end

    # 找出最高的rank,比如同花顺,既是同花,又是顺子,但只能够取最高rank的,即同花顺
    def rank
      RANKING_CATEGORIES.map { |k, v| self.send(:"#{k}?") ? v : 0 }.max
    end

    # 根据不同牌型,返回对应的点数以便后面比点。比如,四条就会返回是4张相同牌的数字,三条/葫芦就会范围3张相同牌的点数
    # 两对的时候,就需要将点数顺序颠倒一次,大的放到前面
    # 顺子就返回最小的那个点,因为‘A2345'是小于'910JQK'
    # 同花或者散牌,就直接将点数顺序颠倒,在比点
    def point
      return hand_nubmer_groups.find { |k, v| v.size == 4 }.first if square? # #find will convert Hash to Array
      return hand_nubmer_groups.find { |k, v| v.size == 3 }.first if three? || full?
      return hand_nubmer_groups.select { |k, v| v.size == 2 }.keys.reverse if two_pairs? || one_pair?
      return hand_nubmers.min if straight? # straight, compare the lowest number to avoid 'A2345' > '910JQK'
      hand_nubmers.reverse # flush or high card, compare the points from high to low
    end

    # e[0..-2]是为了避免'10S'这种情况
    def hand_nubmers
      @hand_nubmers ||= @hand.map { |e| CARD_RANKING[e[0..-2]] }.sort
    end

    # 将手里的牌进行分组,相同点数的分到一组,方便统计是否为对子/三条/四条
    def hand_nubmer_groups
      @hand_nubmer_groups ||= hand_nubmers.group_by { |i| i }
    end

    def straight_flush?
      straight? && flush?
    end

    def flush?
      @hand.map { |e| e[-1] }.uniq.size == 1
    end

    def straight?
      all_5_different_values? && ((hand_nubmers.max - hand_nubmers.min == 4) || ( hand_nubmers == [2,3,4,5,14] ))
    end

    def square?
      hand_nubmer_groups.any? { |k, v| v.size == 4 }
    end

    def full?
      hand_nubmer_groups.any? { |k, v| v.size == 3 } && hand_nubmer_groups.any? { |k, v| v.size == 2 }
    end

    def three?
      hand_nubmer_groups.any? { |k, v| v.size == 3 } && hand_nubmer_groups.keys.size == 3
    end

    def two_pairs?
      hand_nubmer_groups.any? { |k, v| v.size == 2 } && hand_nubmer_groups.keys.size == 3
    end

    def one_pair?
      hand_nubmer_groups.keys.size == 4
    end

    def high_card?
      all_5_different_values? && !straight?
    end

    def all_5_different_values?
      hand_nubmers.uniq.size == 5
    end

  end

end
0
Posted on Friday, September 22, 2017 by 醉·醉·鱼 and labeled under
最近在做exercise.io上面的题练习Ruby,顺便分享一下自己的代码。
http://exercism.io/exercises/ruby/kindergarten-garden/readme

这道题相对比较简单,主要是用each_slice进行一次分组,并且通过Hash做一个key-value转换。最后就只需要按照students的序号,找到之前分好组里对应需要的plants就行了。有趣的是,测试用例里面是通过student的名字在找其拥有的plants,那就需要点元编程的技巧了。这里需要用到define_singleton_method去动态定义以student命名的方法。

class Garden
  DEFAULT_STUDENTS = %w(alice bob charlie david eve fred ginny harriet ileana joseph kincaid larry)

  PLANTS = {
    'R' => :radishes,
    'C' => :clover,
    'G' => :grass,
    'V' => :violets
  }

  def initialize(plants, students=DEFAULT_STUDENTS)
    @plants = plants
    @students = students.sort
    distribute_plants
  end

  def distribute_plants
    # 将两行plants拆分成每两个一组,并对每个plant进行映射转换('R'=>:radishes)
    plants_grouped = @plants.split("\n")
                            .map { |r| r.chars #每一行plants
                                        .each_slice(2) #每两个一组
                                        .map { |plants|
                                          plants.map { |plant| PLANTS[plant] } #每个plant转换一次
                                            }
                                          }
    # 给每个student分配plants
    @students.each_with_index do |s, i|
      break if plants_grouped.first.size < i + 1 # plants不够,就不再分配给student了
      #创建singleton method,因为每个garden对应的plants分配是不一样的。define_method是对整个Class创建实例方法
      define_singleton_method(s.downcase) do
        plants_grouped.map { |e| e[i] }.flatten # 通过索引i,找到student对应的那组plant
      end
    end
  end

end
0
Posted on Wednesday, September 06, 2017 by 醉·醉·鱼 and labeled under
SQL SERVER的index是按照B+tree来存储的。在dm_db_database_page_allocations可以看到这类page的type是2,即index page。
每个index page存储着key - pointer关系,其childPage可能还是intermediate page,也可能是leaf page。leaf page就存储着具体的data。

如下图中的page 623876就是一个intermediate page。

其数据即存储着ChildPageId和key pointer。比如这个例子中,id小于477的,都存在page 623874上。其中level表示该page是root node page。


用fn_PhysLocCracker来实验一下,就可以验证上面的观点。




DROP TABLE LargeTable

CREATE TABLE LargeTable (keyval int, 
    dataval int,
    constraint pk_largetable primary key (keyval)
    )

INSERT INTO LargeTable(keyval, dataval)
select n, rand(10) 
from dbo.GetNums(10000000)


SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('event_service'), object_id('LargeTable'),
                                          1, null, 'DETAILED');
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('event_service', 1, 623876, 3); 
GO

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('event_service', 1, 608307, 3); 
GO

select *
from LargeTable t
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS flc
WHERE keyval < 478