On 11-11-23 09:27 AM, Simon Riggs wrote:
On Wed, Nov 23, 2011 at 1:51 PM, Steve Singer<[email protected]> wrote:
On 11-11-23 04:28 AM, Simon Riggs wrote:
On Tue, Nov 22, 2011 at 10:26 PM, Steve Singer<[email protected]>
wrote:
On 11-11-20 05:47 AM, Cédric Villemain wrote:
ISTM that setting the remote worker to REPEATABLE READ would work well
for this case. Patch attached.
Simon, Did you forget to attach the patch? I don't see it.
Looks that way.
Thanks for the patch.
So why would a SERIALIZABLE READ ONLY DEFERRED transaction produce fewer
conflicts than a READ COMMITTED transaction? Currently the
remote_listener gets the default isolation level (READ COMMITTED).
The attached patch combines your two patches plus performs the same
change to other places in remote_worker (there are places in
remote_worker.c where that initial transaction is rolledback and
restarted, this version also makes sure that those transactions are
started as READ COMMITTED). It also makes the connections that the
remote helpers do to the remote database for querying sl_log_x READ ONLY
DEFERRED.
When I run this patch through the test suite on 9.1 I don't see any
serialization pivot failures (at least not yet).
diff --git a/src/slon/remote_listen.c b/src/slon/remote_listen.c
index 2c8e425..6ff48d1 100644
*** a/src/slon/remote_listen.c
--- b/src/slon/remote_listen.c
*************** remoteListenThread_main(void *cdata)
*** 293,298 ****
--- 293,344 ----
continue;
}
+ if(PQserverVersion(dbconn) >= 90100)
+ {
+ slon_mkquery(&query1,"SET SESSION CHARACTERISTICS AS TRANSACTION read only deferrable");
+ res = PQexec(dbconn, dstring_data(&query1));
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ slon_log(SLON_ERROR,
+ "remoteListenThread_%d: \"%s\" - %s",
+ node->no_id,
+ dstring_data(&query1), PQresultErrorMessage(res));
+ PQclear(res);
+ slon_disconnectdb(conn);
+ free(conn_conninfo);
+ conn = NULL;
+ conn_conninfo = NULL;
+ rc = sched_msleep(node, pa_connretry * 1000);
+ if (rc != SCHED_STATUS_OK && rc != SCHED_STATUS_CANCEL)
+ break;
+
+ continue;
+ }
+
+ }
+ if(PQserverVersion(dbconn) >= 90100)
+ {
+ slon_mkquery(&query1,"SET SESSION CHARACTERISTICS AS TRANSACTION read only isolation level serializable deferrable");
+ res = PQexec(dbconn, dstring_data(&query1));
+ if (PQresultStatus(res) != PGRES_COMMAND_OK)
+ {
+ slon_log(SLON_ERROR,
+ "remoteListenThread_%d: \"%s\" - %s",
+ node->no_id,
+ dstring_data(&query1), PQresultErrorMessage(res));
+ PQclear(res);
+ slon_disconnectdb(conn);
+ free(conn_conninfo);
+ conn = NULL;
+ conn_conninfo = NULL;
+ rc = sched_msleep(node, pa_connretry * 1000);
+ if (rc != SCHED_STATUS_OK && rc != SCHED_STATUS_CANCEL)
+ break;
+
+ continue;
+ }
+
+ }
slon_log(SLON_DEBUG1,
"remoteListenThread_%d: connected to '%s'\n",
node->no_id, conn_conninfo);
diff --git a/src/slon/remote_worker.c b/src/slon/remote_worker.c
index 43bb516..497272a 100644
*** a/src/slon/remote_worker.c
--- b/src/slon/remote_worker.c
*************** remoteWorkerThread_main(void *cdata)
*** 554,560 ****
*/
(void) slon_mkquery(&query1,
"begin transaction; "
! "set transaction isolation level serializable; ");
monitor_state(conn_symname, node->no_id, local_conn->conn_pid, event->ev_type, event->ev_seqno, event->ev_type);
--- 554,560 ----
*/
(void) slon_mkquery(&query1,
"begin transaction; "
! "set transaction isolation level read committed; ");
monitor_state(conn_symname, node->no_id, local_conn->conn_pid, event->ev_type, event->ev_seqno, event->ev_type);
*************** remoteWorkerThread_main(void *cdata)
*** 1116,1122 ****
/* Start the transaction again */
(void) slon_mkquery(&query3,
"begin transaction; "
! "set transaction isolation level serializable; ");
slon_appendquery(&query1,
"lock table %s.sl_event_lock,%s.sl_config_lock;",
rtcfg_namespace,
--- 1116,1122 ----
/* Start the transaction again */
(void) slon_mkquery(&query3,
"begin transaction; "
! "set transaction isolation level read committed; ");
slon_appendquery(&query1,
"lock table %s.sl_event_lock,%s.sl_config_lock;",
rtcfg_namespace,
*************** remoteWorkerThread_main(void *cdata)
*** 1326,1332 ****
* it was released above.
*/
slon_mkquery(&query1, "start transaction;"
! "set transaction isolation level serializable;");
slon_appendquery(&query1,
"lock table %s.sl_config_lock; ",
rtcfg_namespace);
--- 1326,1332 ----
* it was released above.
*/
slon_mkquery(&query1, "start transaction;"
! "set transaction isolation level read committed;");
slon_appendquery(&query1,
"lock table %s.sl_config_lock; ",
rtcfg_namespace);
*************** remoteWorkerThread_main(void *cdata)
*** 1348,1354 ****
if(copy_set_retries != 0)
{
slon_mkquery(&query1, "start transaction;"
! "set transaction isolation level serializable;");
slon_appendquery(&query1,
"lock table %s.sl_config_lock; ",
rtcfg_namespace);
--- 1348,1354 ----
if(copy_set_retries != 0)
{
slon_mkquery(&query1, "start transaction;"
! "set transaction isolation level read committed;");
slon_appendquery(&query1,
"lock table %s.sl_config_lock; ",
rtcfg_namespace);
*************** copy_set(SlonNode *node, SlonConn *local
*** 2699,2709 ****
*/
if (sub_provider == set_origin)
{
(void) slon_mkquery(&query1,
"start transaction; "
! "set transaction isolation level serializable; "
"select \"pg_catalog\".txid_snapshot_xmin(\"pg_catalog\".txid_current_snapshot()) <= '%s'; ",
! event->ev_maxtxid_c);
res1 = PQexec(pro_dbconn, dstring_data(&query1));
if (PQresultStatus(res1) != PGRES_TUPLES_OK)
{
--- 2699,2711 ----
*/
if (sub_provider == set_origin)
{
+ int provider_version=PQserverVersion(pro_dbconn);
(void) slon_mkquery(&query1,
"start transaction; "
! "set transaction isolation level serializable read only %s; "
"select \"pg_catalog\".txid_snapshot_xmin(\"pg_catalog\".txid_current_snapshot()) <= '%s'; ",
! provider_version>=90100 ? "deferrable" : ""
! ,event->ev_maxtxid_c);
res1 = PQexec(pro_dbconn, dstring_data(&query1));
if (PQresultStatus(res1) != PGRES_TUPLES_OK)
{
*************** copy_set(SlonNode *node, SlonConn *local
*** 2739,2747 ****
}
else
{
(void) slon_mkquery(&query1,
"start transaction; "
! "set transaction isolation level serializable; ");
if (query_execute(node, pro_dbconn, &query1) < 0)
{
slon_disconnectdb(pro_conn);
--- 2741,2751 ----
}
else
{
+ int provider_version=PQserverVersion(pro_dbconn);
(void) slon_mkquery(&query1,
"start transaction; "
! "set transaction isolation level serializable read only %s; ",
! provider_version >= 90100 ? "deferrable" : "" );
if (query_execute(node, pro_dbconn, &query1) < 0)
{
slon_disconnectdb(pro_conn);
_______________________________________________
Slony1-general mailing list
[email protected]
http://lists.slony.info/mailman/listinfo/slony1-general
