search

webyog / sqlyog-community

Webyog provides monitoring and management tools for open source relational databases. We develop easy-to-use MySQL client tools for performance tuning and database management. Webyog's solutions include SQL Diagnostic Manager for MySQL performance optimization and SQLyog for MySQL administration. More than 35,000 companies (including Amazon, IBM, Salesforce, AT&T, eBay, and GE) and 2.5 million users rely on Webyog's solutions to provide valuable insights into their databases. Webyog is an Idera, Inc. company.
https://webyog.com/
GNU General Public License v2.0
2.19k stars 322 forks source link

In database sync Locking Reads are only released after the end of database sync. #2026

Open atulwy opened 9 years ago

atulwy commented 9 years ago

Original issue 2026 created by webyog on 2014-03-05T13:03:24.000Z:

​In One way sync we set autocommit =​ 0 on the target. In Two way sync we set autocommit = 0 on both the source as well as the target. In order to get consistent snapshots for checksum calculation during database synchronization we execute SELECT ... LOCK IN SHARE MODE on source and SELECT ... FOR UPDATE on target. This results in LOCKS. COMMIT is done only after all synchronization ends.

Also Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is disabled.

COMMIT after a table with rows that are locked finishes synchronization can release the locks, but it can have an impact on performance.

atulwy commented 9 years ago

Comment #1 originally posted by webyog on 2014-03-05T13:23:58.000Z:

Well .. user could rather create a batch file with 2 one-way sync jobs. The LOCKING logic would not be the same. Why not?

atulwy commented 9 years ago

Comment #2 originally posted by webyog on 2014-03-05T13:31:11.000Z:

Refer Discussions here:

atulwy commented 9 years ago

Comment #3 originally posted by webyog on 2014-03-05T13:31:54.000Z:

My last qeustion was replied.

With two way sync we will sync source>>target and next target>>source for one table. next do same for next table etc.

With 2 one ways syncs we will sync all table source>>target and next all tables target>>source.