[ https://forge.continuent.org/jira/browse/SEQUOIA-1034?page=comments#action_14497 ]
Robert Hodges commented on SEQUOIA-1034: ---------------------------------------- Hi Manu, Thanks for the comments! I knew you would have some good ideas. I left out consideration of LOCK TABLES for simplicity, but you are right--for our purposes it is absolutely vile. We have had a number of really nasty issues with this command with the commercial products as well. Generally speading, key to making Sequoia replication work as widely as possible is to establish some clear patterns of use with very clear assumptions. For instance, for the "no xact" case has the following assumptions: a.) Either all statements are autocommit transactions or transactions contain no more than one write. b.) LOCK TABLES is not allowed. c.) The DBMS implementation ensures that single statement transactions are always deadlock free. (This can happen if write operations internally access index and leaf pages in opposite orders. INSERT and UPDATE commands used to do this on older versions of Sybase.) For applications that use transactions it is very helpful to assume that when run in the wild against a single database they are free of deadlock or snapshot out of date errors. With modern snapshot isolation it is a lot less common for applications to deadlock. We don't have to anticipate deadlocks within Sequoia if applications can uphold this assumption. 4.2 Rollback/Abort/Cancel. I agree fully with your comment here. This all seems very unpredictable. 4.4 Mapping queries. The key to mapping queries in Sequoia to the database is to track connection IDs in the connection pool so that another thread can look up whether the session is stuck awaiting locks. At least that's what I found from looking at MySQL/InnoDB and PostgreSQL. Both of these provide ways to look up blocked sessions dynamically from within the database. Unfortunately MySQL is complicated by the presence of other engines than InnoDB. PosgreSQL by contrast appears quite straightforward. The middleware stored procedures idea is excellent. As you say, it is multi-statement transactions that are really the heart of the ordering problem. Approaches that can grab all locks up front for the transaction will be deadlock free. (That's not just an idea--it's a theorem!) I think we should include this as a pattern of use and provide support for it. One of the other properties of the stored procedure approach is that it supports partitioning rather well, as demonstrated by Skype's PL/Proxy implementation. Finally, what do you think of the idea of pluggable ordering? This would be a great 4.0 feature. > Possible race condition leading to concurrent requests being executed in a > different order on backends > ------------------------------------------------------------------------------------------------------ > > Key: SEQUOIA-1034 > URL: https://forge.continuent.org/jira/browse/SEQUOIA-1034 > Project: Sequoia > Type: Bug > Components: Core > Versions: Sequoia 2.10.9 > Reporter: Stephane Giron > Assignee: Stephane Giron > Priority: Critical > Fix For: sequoia 2.10.10 > > > This can lead to backends inconsistencies. > As an example, this is the behavior that was observed : > CREATE TABLE `table1` ( > `db_id` bigint(20) NOT NULL auto_increment, > `col1` varchar(255) default NULL, > `creation_date` datetime default NULL, > `request_date` varchar(255) default NULL, > `col2` varchar(255) default NULL, > `version` varchar(255) default NULL, > PRIMARY KEY (`db_id`), > UNIQUE KEY `col1` (`col1`,`col2`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > Transaction 1 > begin > insert into table1 (version) values('a1'); > insert into table1 (version) values('c1'); > commit > Transaction 2 > begin > insert into table1 (version) values('b') > commit > T1 begin > T2 begin > T1 insert 1 > T2 insert 1 > T1 insert 2 > commits > The following result is what I got in the backends : > On one controller : > | 8 | NULL | NULL | NULL | NULL | a1 | > | 9 | NULL | NULL | NULL | NULL | c1 | > | 10 | NULL | NULL | NULL | NULL | b | > On the other : > | 8 | NULL | NULL | NULL | NULL | a1 | > | 9 | NULL | NULL | NULL | NULL | b | > | 10 | NULL | NULL | NULL | NULL | c1 | > This can happen if the statement "T2 insert 1", which will be posted in the > conflicting queue, can be processed on one controller (because there is > nothing else at this time in the non conflicting queue), whereas on the other > controller the "T1 insert 2" statement arrives in the non conflicting queue > before "T2 insert 1" starts executing. > This behavior can be observed essentially because of the auto incremented > key. However other type of statements may run out of order as well. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: https://forge.continuent.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira _______________________________________________ Sequoia mailing list [email protected] https://forge.continuent.org/mailman/listinfo/sequoia
