sqlite3 shell命令".backup"和事务 [英] sqlite3 shell command '.backup' and transaction

问题描述

我在本地主机(Win NT)上具有sqlite数据库，并希望对其进行备份.我想使用shell命令".backup"来完成此任务.但似乎我可以在备份过程中向数据库中插入新行.

I have sqlite database on my local host (Win NT) and want to back it up. I want to use shell command '.backup' to accomplish this task. But it seems I can insert a new rows into database during backup process.

'.backup'shell命令是否在我的数据库上启动新的互斥事务?

Does '.backup' shell command starts new exclusive transaction on my database?

我认为当我执行'.backup'shell命令时，它会锁定数据库.

I thought when I execute '.backup' shell command it locks my database.

推荐答案

sqlite3备份方法不会锁定数据库.如果您想锁定数据库，建议使用以下解决方法:

The sqlite3 backup method does not lock the database. I would suggest to use the following workaround if you would like to lock the database:

• 开始交易(共享锁)
• 通过使用任何INSERT语句，数据库将获得保留的锁.但是，此INSERT语句可以为空.
• 备份数据库.
• 使用ROLLBACK或COMMIT结束交易.

• Start a transaction (shared lock)
• By using any INSERT statement, the database gets a reserved lock. However, this INSERT statement can be empty.
• Backup the database.
• End the transaction by using a ROLLBACK or COMMIT.

代码:

BEGIN;
INSERT INTO <anytable> SELECT * FROM <anytable> WHERE 1=0;
.backup <database> <file>
ROLLBACK;

如果您使用的是名为"backup"的表，并且要为每个副本插入一行(日期，..)(如果此信息与您相关)，则是一种不太麻烦的方法.

A less hacky way would be if you are using a table named 'backup' and you are inserting a row (date,..) for each copy (if this information is relevant for you).

这篇关于sqlite3 shell命令".backup"和事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！