voltdb | Volt Active Data - Thank you for your interest in VoltDB | Learning library

Finally I found the solution.

Query is : ALTER TABLE TBLM_TABLE_NAME ALTER COLUMN MOBILE_NUMBER SET NULL

Reference : https://docs.voltdb.com/UsingVoltDB/ddlref_altertable.php

After reading this aspnetcore issue discovered that the problem was with the implementation of my IHostedService implementation that makes the request to Kafka.

The StartAsync method was performing the task, running until the request completed. By design this method is meant to be fire and forget, i.e. start the task and then continue. Updated my KafkaAdmin service to be a BackgroundService, overriding ExecuteAsync method, as listed below. Subsequently, tests no longer blocks.

1. In general yes: a transaction runs in a single database connection or database session: it may start in a implicit way or explicit way: it's started and ended with special SQL statements. For PostgreSQL see https://www.postgresql.org/docs/9.5/tutorial-transactions.html.

However there are rare exceptions like distributed transactions using two-phase commit protocol where a single transaction runs on different sessions on different databases.

1. most databases using SQL work this way because it is requested by SQL standard.

You must also take into account that some client tools (for example psql for PostgreSQL) or JDBC API for Java have defaults settings that change a little bit this behaviour: both implement auto-commit mode after each statement (this means that each SQL statement runs in its own transaction) but you can change this behaviour in each tool.

1. the connection/session/thread/process depends a lot on the database. MySQL uses threads whereas PostgreSQL uses processes: there is no SQL statement for that: this is very implementation dependant.

To make things easier for developers, procedures are called the same way regardless of what type they are. Internally there are different types of multi-partition procedures as they provide some optimizations, although there is more to be done and some H-Store projects have done research in these areas.

MP transactions still ultimately involve sending tasks to be done on all the partitions. The one exception you noticed is a special two-partition transaction that is only used in rebalancing data during elastic add or shrink.

Partitions consist of one or more sites (on separate servers) depending on kfactor. These sites stay in sync without a 2PC by requiring deterministic procedures. The partitions work through the backlog in a queue as fast as the process time (or local execution time) allows. All sites handle both reads and writes.

MP tasks sent to those partition queues have to wait on all the pending items to finish. That is why there is a pool of 20 (by default) threads for MP reads. This allows 20 tasks to be sent out at once, so that the next MP read usually doesn't have to wait for 2 networks hops + the max queue wait time + processing time before it can even get queued.

MP reads that are not "single-shot" would be Java procedures with multiple voltExecuteSQL() calls, such as a procedure where subsequent SQL queries depend on the results of prior queries. When these transactions send tasks to the partitions, the partitions have to wait for the max queue wait time + processing time + 2 network hops before they can do the next part of the transaction.

MP writes can also have multiple voltExecuteSQL() calls, plus they have to wait for a final commit signal, so this all delays the progress on the partitions.

There are certainly examples of MP transactions that shouldn't need to involve all of the partitions and could benefit from future optimizations, but it's not as easy as it may seem on a database that has to support durability to disk, k-safety, elastic add and shrink, multi-cluster active-active replication, and many of the other features that have been added to VoltDB over the years since it grew out of the H-Store project.

Disclosure: I work at VoltDB