Use SKIP LOCKED in MySqlChannelMessageStoreQueryProvider and PostgresChannelMessageStoreQueryProvider

[ckalan]

Mysql 8 supports SKIP LOCKED and Postgres also supports it in its recent versions.

Allow query providers of these databases to either conditionally support SKIP LOCKED or introduce DB version specific variants that support it.

Current Behavior

Currently, OracleChannelMessageStoreQueryProvider appends SKIP LOCKED clause to its queries but MySqlChannelMessageStoreQueryProvider and PostgresChannelMessageStoreQueryProvider don't.

Context
I am planning to use Mysql or Postgres as a persistent queue in a HA cluster, but based on my understanding, without SKIP LOCKED, multiple pollers in different JVMs may receive the same message from the same JDBC backed queue simultaneously. PostgresChannelMessageStoreQueryProvider uses FOR UPDATE clause which I guess prevents retrieval of same message at the same time but not in an ideal way.

Currently, developers can write their own query provider as a workaround which I plan to but I am not fully aware of the consequences. Based on Mysql and Postgres documentations, the behavior looks similar to Oracle's but I am not a DB expert and there may be other developers like me who may share the same hesitation.

Even if it is not possible to use SKIP LOCKED as I suggest here for any reason, this ticket would probably be a guide to those who have the similar question

[artembilan]

I have scheduled this for the next milestone, however no one stops you to extend that PostgresChannelMessageStoreQueryProvider and override respective methods to add that SKIP LOCKED in the end of query:

public class SkipLokcedPostgresChannelMessageStoreQueryProvider extends PostgresChannelMessageStoreQueryProvider {

	@Override
	public String getPollFromGroupExcludeIdsQuery() {
		return super.getPollFromGroupExcludeIdsQuery() + " SKIP LOCKED";
	}

	@Override
	public String getPollFromGroupQuery() {
		return super.getPollFromGroupQuery() + " SKIP LOCKED";
	}

	@Override
	public String getPriorityPollFromGroupExcludeIdsQuery() {
		return super.getPriorityPollFromGroupExcludeIdsQuery() + " SKIP LOCKED";
	}

	@Override
	public String getPriorityPollFromGroupQuery() {
		return super.getPriorityPollFromGroupQuery() + " SKIP LOCKED";
	}
	
}

[artembilan]

Hey @AdamaSorho!

This is a good simple issue to fix quickly.
I'm not sure how we can test it: perhaps should be just enough to be sure that all the test in spring-integration-jdbc are passing.
Of course, counting on the fact that Docker is available locally.