目前业内常见的一种mysql读写分离实现可以基于wait_for_executed_gtid_set实现，即在去从库查询前，为了保证查询的一致性，等待从库达到要求的gtid值后再进行查询。典型的实现例如proxysql。
那proxy如何去获取从库需要等待的gtid呢？一种方式是proxy后台线程定期去后端主库查询，这样有时效性问题，不能确保某个会话去从库查询一定能查到自己刚写入的数据。另一种是基于mysql的Session State Trackers。
Session State Trackers中有一个tracker是gtid相关的，由参数session_track_gtids 控制。session_track_gtids 是mysql session状态跟踪相关的一个参数，允许的值为：
OFF： 默认值，不追踪gtid
OWN_GTID： 追踪上次反馈后当前会话所提交的所有新增gtid
ALL_GTIDS： 直接返回gtid_executed系统变量的值，在当前会话事务完成提交后读取。
1. Session_gtids_tracker
MySQL内部使用session_tracker.cc中的Session_tracker来追踪所有需要追踪的session状态，主要有
1501 void Session_tracker::init(const CHARSET_INFO *char_set) {
1502 m_trackers[SESSION_SYSVARS_TRACKER] =
1503 new (std::nothrow) Session_sysvars_tracker(char_set);
1504 m_trackers[CURRENT_SCHEMA_TRACKER] =
1505 new (std::nothrow) Current_schema_tracker;
1506 m_trackers[SESSION_STATE_CHANGE_TRACKER] =
1507 new (std::nothrow) Session_state_change_tracker;
1508 m_trackers[SESSION_GTIDS_TRACKER] = new (std::nothrow) Session_gtids_tracker;
1509 m_trackers[TRANSACTION_INFO_TRACKER] =
1510 new (std::nothrow) Transaction_state_tracker;
1511 m_trackers[TRACK_TRANSACTION_STATE] =
1512 new (std::nothrow) Session_transaction_state;
1513 }
可以看出，gtid的具体实现tracker为Session_gtids_tracker
333
340
341 class Session_gtids_tracker
342 : public State_tracker,
343 Session_consistency_gtids_ctx::Ctx_change_listener {
344 private:
345 void reset();
346 Session_gtids_ctx_encoder *m_encoder;
347
348 public:
349
350 Session_gtids_tracker()
351 : Session_consistency_gtids_ctx::Ctx_change_listener(),
352 m_encoder(nullptr) {}
353
354 ~Session_gtids_tracker() override {
355
359 if (m_enabled && current_thd)
360 current_thd->rpl_thd_ctx.session_gtids_ctx()
361 .unregister_ctx_change_listener(this);
362 if (m_encoder) delete m_encoder;
363 }
364
365 bool enable(THD *thd) override { return update(thd); }
366 bool check(THD *, set_var *) override { return false; }
367 bool update(THD *thd) override;
368 bool store(THD *thd, String &buf) override;
369 void mark_as_changed(THD *thd, LEX_CSTRING tracked_item_name) override;
370
371 // implementation of the Session_gtids_ctx::Ctx_change_listener
372 void notify_session_gtids_ctx_change() override {
373 mark_as_changed(nullptr, {});
374 }
375 };
2. GTID的Trace代码入口
GTID的trace模式有OWN_GTID和ALL_GTIDS，两者获取的时间点还有锁粒度不同。
OWN_GTID是在ordered_commit的process_commit_stage_queue阶段获取，是纯会话内操作，无需锁保护。
ALL_GTIDS是在会话完成事务提交之后获取的，在trans_commit_stmt 中完成ha_commit_trans后；因为读取的是gtid_state的gtid_executed，所以用的是全局大锁global_sid_lock，并且是写锁（这里感觉没必要用写锁，给官方报了个bug https://bugs.mysql.com/109435）。
2.1 SESSION_TRACK_GTIDS_ALL_GTIDS
在rpl_context.cc中，可以看出是使用全局大锁。
67 bool Session_consistency_gtids_ctx::notify_after_transaction_commit(
68 const THD *thd) {
69 DBUG_TRACE;
70 assert(thd);
71 bool res = false;
72
73 if (!shall_collect(thd)) return res;
74
75 if (m_curr_session_track_gtids == SESSION_TRACK_GTIDS_ALL_GTIDS) {
76
82 global_sid_lock->wrlock();
83 res = m_gtid_set->add_gtid_set(gtid_state->get_executed_gtids()) !=
84 RETURN_STATUS_OK;
85 global_sid_lock->unlock();
86
87 if (!res) notify_ctx_change_listener();
88 }
89
90 return res;
91 }
调用路径为transaction.cc:trans_commit调用 thd->rpl_thd_ctx.session_gtids_ctx().notify_after_transaction_commit
#0 Session_consistency_gtids_ctx::notify_after_transaction_commit (this=0x7fff40003198, thd=0x7fff40000da0) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/rpl_context.cc:73
#1 0x000000000340c50f in trans_commit_stmt (thd=0x7fff40000da0, ignore_global_read_lock=false) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/transaction.cc:538
#2 0x00000000032793df in mysql_execute_command (thd=0x7fff40000da0, first_level=true) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:4778
#3 0x000000000327a872 in dispatch_sql_command (thd=0x7fff40000da0, parser_state=0x7fffa82f2b10) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:5207
#4 0x0000000003270e6d in dispatch_command (thd=0x7fff40000da0, com_data=0x7fffa82f3c00, command=COM_QUERY) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:1957
#5 0x000000000326ef7b in do_command (thd=0x7fff40000da0) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:1352
#6 0x0000000003450a95 in handle_connection (arg=0xa886900) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/conn_handler/connection_handler_per_thread.cc:302
#7 0x0000000004fff5d2 in pfs_spawn_thread (arg=0xa8a2260) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/storage/perfschema/pfs.cc:2942
#8 0x00007ffff7bc16ba in start_thread (arg=0x7fffa82f4700) at pthread_create.c:333
#9 0x00007ffff5e1241d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109
2.2 SESSION_TRACK_GTIDS_OWN_GTID
在rpl_context.cc中，无需锁
93 bool Session_consistency_gtids_ctx::notify_after_gtid_executed_update(
94 const THD *thd) {
95 DBUG_TRACE;
96 assert(thd);
97 bool res = false;
98
99 if (!shall_collect(thd)) return res;
100
101 if (m_curr_session_track_gtids == SESSION_TRACK_GTIDS_OWN_GTID) {
102 assert(global_gtid_mode.get() != Gtid_mode::OFF);
103 assert(thd->owned_gtid.sidno > 0);
104 const Gtid >id = thd->owned_gtid;
105 if (gtid.sidno == -1) // we need to add thd->owned_gtid_set
106 {
107
108 #ifdef HAVE_GTID_NEXT_LIST
109 assert(!thd->owned_gtid_set.is_empty());
110 res = m_gtid_set->add_gtid_set(&thd->owned_gtid_set) != RETURN_STATUS_OK;
111 #else
112 assert(0);
113 #endif
114 } else if (gtid.sidno > 0) // only one gtid
115 {
116
124 rpl_sidno local_set_sidno = m_sid_map->add_sid(thd->owned_sid);
125
126 assert(!m_gtid_set->contains_gtid(local_set_sidno, gtid.gno));
127 res = m_gtid_set->ensure_sidno(local_set_sidno) != RETURN_STATUS_OK;
128 if (!res) m_gtid_set->_add_gtid(local_set_sidno, gtid.gno);
129 }
130
131 if (!res) notify_ctx_change_listener();
132 }
133 return res;
134 }
调用路径为rpl_gtid_state.cc的Gtid_state ::update_gtids_impl_own_gtid_set调用thd->rpl_thd_ctx.session_gtids_ctx().notify_after_gtid_executed_update。
#0 Session_consistency_gtids_ctx::notify_after_gtid_executed_update (this=0x7fff40003198, thd=0x7fff40000da0) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/rpl_context.cc:99
#1 0x000000000443a580 in Gtid_state::update_gtids_impl_own_gtid (this=0xa75de70, thd=0x7fff40000da0, is_commit=true) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/rpl_gtid_state.cc:881
#2 0x0000000004437c43 in Gtid_state::update_commit_group (this=0xa75de70, first_thd=0x7fff40000da0) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/rpl_gtid_state.cc:184
#3 0x00000000043bec64 in MYSQL_BIN_LOG::process_commit_stage_queue (this=0x80df1c0 <mysql_bin_log>, thd=0x7fff40000da0, first=0x7fff40000da0)
at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/binlog.cc:8552
#4 0x00000000043c052f in MYSQL_BIN_LOG::ordered_commit (this=0x80df1c0 <mysql_bin_log>, thd=0x7fff40000da0, all=false, skip_commit=false)
at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/binlog.cc:9061
#5 0x00000000043be2c8 in MYSQL_BIN_LOG::commit (this=0x80df1c0 <mysql_bin_log>, thd=0x7fff40000da0, all=false) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/binlog.cc:8303
#6 0x00000000035f25ae in ha_commit_trans (thd=0x7fff40000da0, all=false, ignore_global_read_lock=false) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/handler.cc:1766
#7 0x000000000340c454 in trans_commit_stmt (thd=0x7fff40000da0, ignore_global_read_lock=false) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/transaction.cc:532
#8 0x00000000032793df in mysql_execute_command (thd=0x7fff40000da0, first_level=true) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:4778
#9 0x000000000327a872 in dispatch_sql_command (thd=0x7fff40000da0, parser_state=0x7fffa82f2b10) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:5207
#10 0x0000000003270e6d in dispatch_command (thd=0x7fff40000da0, com_data=0x7fffa82f3c00, command=COM_QUERY) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:1957
#11 0x000000000326ef7b in do_command (thd=0x7fff40000da0) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/sql_parse.cc:1352
#12 0x0000000003450a95 in handle_connection (arg=0xa886900) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/sql/conn_handler/connection_handler_per_thread.cc:302
#13 0x0000000004fff5d2 in pfs_spawn_thread (arg=0xa8a2260) at /home/gaoxiaoxin/mysql-repo/mysql-8.0.29-repo/storage/perfschema/pfs.cc:2942
#14 0x00007ffff7bc16ba in start_thread (arg=0x7fffa82f4700) at pthread_create.c:333
#15 0x00007ffff5e1241d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109
3. Trace的GTID值通过OK包返回客户端
其中负责协议包编码的是Session_gtids_ctx_encoder *m_encoder; 具体的子类为：class Session_gtids_ctx_encoder_string : public Session_gtids_ctx_encoder。
调用路径为：Session_tracker::store -> Session_gtids_tracker::store -> Session_gtids_ctx_encoder_string::encode
具体的encode实现如下，主要的payload为 [ tracker type] [len] [ encoding spec ] [gtid string len] [gtid string]
276 bool encode(THD *thd, String &buf) override {
277 const Gtid_set *state = thd->rpl_thd_ctx.session_gtids_ctx().state();
278
279 if (!state->is_empty()) {
280
285 ulonglong tracker_type_enclen =
286 1 ;
287 ulonglong encoding_spec_enclen =
288 1 ;
289 ulonglong gtids_string_len =
290 state->get_string_length(&Gtid_set::default_string_format);
291 ulonglong gtids_string_len_enclen = net_length_size(gtids_string_len);
292 ulonglong entity_len =
293 encoding_spec_enclen + gtids_string_len_enclen + gtids_string_len;
294 ulonglong entity_len_enclen = net_length_size(entity_len);
295 ulonglong total_enclen = tracker_type_enclen + entity_len_enclen +
296 encoding_spec_enclen + gtids_string_len_enclen +
297 gtids_string_len;
298
299
300 uchar *to = (uchar *)buf.prep_append(total_enclen, EXTRA_ALLOC);
301
302
305
306
307 *to = (uchar)SESSION_TRACK_GTIDS;
308 to++;
309
310
311 to = net_store_length(to, entity_len);
312
313
314 *to = (uchar)encoding_specification();
315 to++;
316
317
318 to = net_store_length(to, gtids_string_len);
319
320
321 state->to_string((char *)to);
322 }
323 return false;
324 }
最终是在OK包中编码gtid信息：protocol_classic.cc的net_send_ok函数里实现
static bool net_send_ok {
...
909 if (protocol->has_client_capability(CLIENT_SESSION_TRACK) &&
910 thd->session_tracker.enabled_any() &&
911 thd->session_tracker.changed_any()) {
912 server_status |= SERVER_SESSION_STATE_CHANGED;
913 state_changed = true;
914 }
...
937 if (protocol->has_client_capability(CLIENT_SESSION_TRACK)) {
938
939 if (state_changed || (message && message[0]))
940 pos = net_store_data(pos, pointer_cast<const uchar *>(message),
941 message ? strlen(message) : 0);
942
943 if (unlikely(state_changed)) {
944 store.set_charset(thd->variables.collation_database);
945
946
950 store.append((const char *)start, (pos - start), MYSQL_ERRMSG_SIZE);
951
952
953 thd->session_tracker.store(thd, store);
954
955 start = (uchar *)store.ptr();
956 pos = start + store.length();
957 }
958 } else if (message && message[0]) {
...
}
MySQL的OK包payload协议参见：
https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_ok_packet.html
4. Proxy层获取GTID
Proxy层只需设置后端mysql开启session_track_gtids=OWN_GTID，并通过解析ok包获取刚提交会话的gtid值。对于后续同会话的读请求，可以在从库wait_for_executed_gtid_set来等待，确保从库执行完成对应gtid后，再执行查询，确保一致性。
这里使用OWN_GTID主要原因有：
1. OWN_GTID是会话内操作，相对轻量，对性能影响可控
2. OWN_GTID已足够确保会话内的因果序一致性了
3. 即便使用了ALL_GTIDS也无法实现强一致性，因为gtid是在事务提交后获取的，并不是查询执行前获取的。而ALL_GTIDS需要全局大锁，明显影响性能。
转载请注明转自高孝鑫的博客！
522
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
