· 10 years ago · Jul 06, 2016, 11:09 PM
1package main
2
3import (
4 "database/sql"
5 "log"
6
7 "github.com/alecthomas/kingpin"
8 _ "github.com/go-sql-driver/mysql"
9)
10
11var (
12 user = kingpin.Flag("user", "The database user").Short('u').Required().String()
13 password = kingpin.Flag("password", "The database password").Short('p').Required().String()
14 host = kingpin.Flag("host", "The database host").Short('h').Default("localhost").String()
15 port = kingpin.Flag("port", "The database port").Default("3306").String()
16 database = kingpin.Flag("database", "The database to connect to").Default("invisionapp").String()
17 lockHistoryTable = kingpin.Flag("lockHistory", "The name of the lock history table").Default("lock_monitor").String()
18 lockDuration = kingpin.Flag("lockDuration", "The lock duration to monitor for").Default("3").Int()
19)
20
21func main() {
22 kingpin.Version("0.0.1")
23 kingpin.Parse()
24
25 db := connect()
26
27 defer db.Close()
28
29 log.Println("Attempting to obtain unique process lock")
30 if (lockProcess(db)) {
31 log.Println("success")
32 } else {
33 log.Fatal("failed to obtain process lock; another process is already running.")
34 }
35
36 if (lockTableExists(db)) {
37 log.Println("History table already exists:", *lockHistoryTable)
38 } else {
39 log.Println("Creating history table:", *lockHistoryTable)
40 createLockTable(db)
41 }
42
43 monitorLocks(db)
44}
45
46func connect() *sql.DB {
47 var dburl string = *user + ":" + *password + "@tcp(" + *host + ":" + *port + ")/" + *database + "?autocommit=true"
48
49 db, err := sql.Open("mysql", dburl)
50
51 if err != nil {
52 log.Fatal(err)
53 }
54
55 log.Print("connecting to [" + dburl + "]... ")
56 err = db.Ping()
57
58 if err != nil {
59 log.Fatal(err)
60 }
61
62 log.Println("connected")
63
64 return db
65}
66
67func lockProcess(db *sql.DB) bool {
68 var lockResult int64 = 0
69
70 stmt, err := db.Prepare("SELECT GET_LOCK(?, 10)");
71
72 if err != nil {
73 log.Fatal(err)
74 }
75
76 rows, err := stmt.Query(*lockHistoryTable)
77
78 if err != nil {
79 log.Fatal(err)
80 }
81
82 if rows.Next() {
83 err = rows.Scan(&lockResult)
84
85 if err != nil {
86 log.Fatal(err)
87 }
88 }
89
90 err = rows.Err()
91
92 if err != nil {
93 log.Fatal(err)
94 }
95
96 rows.Close()
97
98 return lockResult == 1
99}
100
101func lockTableExists(db *sql.DB) bool {
102 var tableCount int64 = 0
103
104 stmt, err := db.Prepare(
105 `SELECT
106 COUNT(*)
107 FROM
108 information_schema.tables
109 WHERE
110 table_type = 'BASE TABLE'
111 AND table_schema = DATABASE()
112 AND table_name = ?`)
113
114 if err != nil {
115 log.Fatal(err)
116 }
117
118 rows, err := stmt.Query(*lockHistoryTable)
119
120 if err != nil {
121 log.Fatal(err)
122 }
123
124 if rows.Next() {
125 err = rows.Scan(&tableCount)
126
127 if err != nil {
128 log.Fatal(err)
129 }
130 }
131
132 err = rows.Err()
133
134 if err != nil {
135 log.Fatal(err)
136 }
137
138 rows.Close()
139
140 return tableCount != 0
141}
142
143func createLockTable(db *sql.DB) {
144 result, err := db.Exec(
145 `CREATE TABLE ` + *lockHistoryTable + ` (
146 id int NOT NULL AUTO_INCREMENT,
147 requesting_trx_id VARCHAR(18) NOT NULL,
148 requesting_thread_id BIGINT(21) UNSIGNED NOT NULL,
149 requesting_trx_started DATETIME NOT NULL,
150 requesting_duration BIGINT(21) NOT NULL,
151 requesting_query VARCHAR(1024) NOT NULL,
152 blocking_trx_id VARCHAR(18) NOT NULL,
153 blocking_thread_id BIGINT(21) UNSIGNED NOT NULL,
154 blocking_trx_started DATETIME NOT NULL,
155 blocking_duration BIGINT(21) NOT NULL,
156 blocking_query VARCHAR(1024) NOT NULL,
157 PRIMARY KEY (id)
158 ) ENGINE=INNODB DEFAULT CHARSET=UTF8;`)
159
160 _ = result
161
162 if err != nil {
163 log.Fatal(err)
164 }
165}
166
167func monitorLocks(db *sql.DB) {
168 var monitorSql string =
169 `SELECT
170 requesting_trx.trx_id requesting_trx_id,
171 requesting_trx.trx_mysql_thread_id requesting_thread_id,
172 requesting_trx.trx_started requesting_trx_started,
173 TIMESTAMPDIFF(SECOND, requesting_trx.trx_started, NOW()) requesting_duration,
174 requesting_trx.trx_query requesting_query,
175 blocking_trx.trx_id blocking_trx_id,
176 blocking_trx.trx_mysql_thread_id blocking_thread_id,
177 blocking_trx.trx_started blocking_trx_started,
178 TIMESTAMPDIFF(SECOND, blocking_trx.trx_started, NOW()) blocking_duration,
179 blocking_trx.trx_query blocking_query
180 FROM
181 information_schema.innodb_lock_waits trx_waits
182 JOIN
183 information_schema.innodb_trx requesting_trx ON trx_waits.requesting_trx_id = requesting_trx.trx_id
184 JOIN
185 information_schema.innodb_trx blocking_trx ON trx_waits.blocking_trx_id = blocking_trx.trx_id
186 HAVING requesting_duration >= ?;`
187
188 _ = monitorSql
189}