[ https://forge.continuent.org/jira/browse/SEQUOIA-1034?page=comments#action_14578 ]
Robert Hodges commented on SEQUOIA-1034: ---------------------------------------- Here is a short comment to show how to determine if a connection is blocked in PostgreSQL. 1.) First of all, when you get the connection initially you need to find out the session ID. Here's the SQL: select pg_backend_pid() This gives you a number like 19009 that is the process ID. 2.) To find out whether the session is blocked and waiting for locks, execute the following SQL on another connection than the one you are trying to check: select * from pg_locks where pid=19009 and granted='f' This will return a list of locks on which your session is waiting. If the number is greater than 0, you are waiting for locks. PostgreSQL awaits only one lock at a time, so there should only be one row that meets this predicate. NOTE: PostgreSQL does not have a notion of a lock wait timeout. Once your session is blocked awaiting locks, it will remain blocked until the locks are granted, the request is cancelled, or the session is killed. The only exception is a deadlock situation. > 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.11 > > > 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
