Skip to content

Instantly share code, notes, and snippets.

@kamipo

kamipo/lock.rb

Last active Dec 15, 2020
Embed
What would you like to do?
# frozen_string_literal: true
require "mysql2"
def client
Mysql2::Client.new(
host: "localhost",
username: "root",
database: "test",
)
end
c1 = client
c2 = client
c1.query("DROP TABLE IF EXISTS `user_tables`")
c1.query <<-SQL
CREATE TABLE `user_tables` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `index_user_tables_on_name` (`name`)
)
SQL
1000.downto(1) do |i|
c1.query("INSERT INTO `user_tables` (`name`) VALUES ('p#{i}')")
end
t = Thread.new do
100.times do |j|
c2.query("BEGIN")
#puts "c2 locking:#{j}"
c2.query("SELECT 1 FROM `user_tables` FORCE INDEX(index_user_tables_on_name) WHERE `name` IN (#{1000.downto(1).map{|i|"'p#{i}'"}.join(",")}) FOR UPDATE")
#puts "c2 locked:#{j}"
c2.query("COMMIT")
end
end
100.times do |j|
c1.query("BEGIN")
#puts "c1 locking:#{j}"
c1.query("SELECT 1 FROM `user_tables` FORCE INDEX(PRIMARY) WHERE `id` IN (#{1.upto(1000).to_a.join(",")}) FOR UPDATE")
#puts "c1 locked:#{j}"
c1.query("COMMIT")
end
t.join
@kamipo

This comment has been minimized.

Copy link
Owner Author

@kamipo kamipo commented Dec 11, 2019

% be ruby lock.rb
/Users/kamipo/.rbenv/versions/2.7.0-dev/lib/ruby/gems/2.7.0/gems/bundler-1.17.3/lib/bundler/rubygems_integration.rb:200: warning: constant Gem::ConfigMap is deprecated
/Users/kamipo/.rbenv/versions/2.7.0-dev/lib/ruby/gems/2.7.0/gems/bundler-1.17.3/lib/bundler/rubygems_integration.rb:200: warning: constant Gem::ConfigMap is deprecated
#<Thread:0x00007fa6099b38c0@lock.rb:30 run> terminated with exception (report_on_exception is true):
Traceback (most recent call last):
	6: from lock.rb:31:in `block in <main>'
	5: from lock.rb:31:in `times'
	4: from lock.rb:34:in `block (2 levels) in <main>'
	3: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:130:in `query'
	2: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:130:in `handle_interrupt'
	1: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:138:in `block in query'
/Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:138:in `_query': Deadlock found when trying to get lock; try restarting transaction (Mysql2::Error)
Traceback (most recent call last):
	6: from lock.rb:31:in `block in <main>'
	5: from lock.rb:31:in `times'
	4: from lock.rb:34:in `block (2 levels) in <main>'
	3: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:130:in `query'
	2: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:130:in `handle_interrupt'
	1: from /Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:138:in `block in query'
/Users/kamipo/src/github.com/brianmario/mysql2/lib/mysql2/client.rb:138:in `_query': Deadlock found when trying to get lock; try restarting transaction (Mysql2::Error)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment