I use H2 in a Spring Boot application with the following h2 config:
jdbc:h2:./my-database;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=mysql
I have two tables, called
read_from_here
write_to_here
with identical DDL.
The application only reads from read_from_here.
A long processing job writes new rows to the write_to_here table.
I then want to swap these tables so write_to_here is renamed to
read_from_here.
I attempt to do this with the following query:
@Transactional
fun swap(): Either<Throwable, Unit> = Either.catch {
jdbcTemplate.execute("DROP TABLE IF EXISTS read_from_here; ALTER
TABLE write_to_here RENAME TO read_from_here;")
}
I assumed because this runs in a single transaction that the rename is
atomic. But it appears that queries that selects from read_from_here fail
during the swap because read_from_here no longer exists during a brief
period.
How should I perform an atomic rename of the tables so there is no downtime?
--
This e-mail and any attachments hereto are intended for the designated
recipient(s) only. It may contain confidential and/or proprietary
information. If you have received this communication unintentionally,
please inform us immediately by return email, and ensure that the original
message and copies, attachments, and printouts relating thereto are
permanently deleted. Thank you. Any dissemination, distribution or copying
of this e-mail is strictly prohibited.
E-mail is susceptible to
interception by third parties, data corruption, tampering, unauthorised
amendment, and viruses in connection with its transmission. We do not
accept liability for any such interception, corruption, tampering,
amendment or viruses, or any consequence thereof.
E-mails and attachments
thereto sent or received by us may contain personal data. The privacy
notice <https://www.budbee.com/en/external-privacy-notice> applicable to
our processing of personal data is available on our website at
www.budbee.com <https://www.budbee.com>
*Please consider the environment
before printing this email*
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/32dc641e-99fb-4b56-94b4-814a0c0942efn%40googlegroups.com.
