About transaction control issues in sqlite3

Question

When encapsulating the method of operating the sqlite3 database, the test code is as follows:

$path = 'test.db';

$conn = new sqlite3($path);

$sql = 'insert into tb_test...';

for($i=0;$i<10000,$i ){

$conn-> query($sql);

}

I want to add transaction control, but I haven’t found a way to add transactions in sqlite3. new pdo(). I have been writing Java before, and I am not familiar with PHP yet. I hope all coders can answer it, thank you.

Answer 1

//sqlite3 事务的操作没有特别的接口函数，就是一个普通的 sql 语句而已，分别如下：
int ret ; 
ret = sqlite3_exec ( db , "begin transaction" , 0 , 0 , & zErrorMsg ); // 开始一个事务 
ret = sqlite3_exec ( db , "commit transaction" , 0 , 0 , & zErrorMsg ); // 提交事务 
ret = sqlite3_exec ( db , "rollback transaction" , 0 , 0 , & zErrorMsg );     

//如果要进行大量的操作前使用如下语句 
ret = sqlite3_exec ( db , "begin transaction" , 0 , 0 ,& zErrorMsg ); 
for (...) {   
//insert into operate     
// 如果操作错误  
ret = sqlite3_exec ( db , "rollback transaction" , 0 , 0 , & zErrorMsg )
 } 
 ret = sqlite3_exec ( db , "commit transaction" , 0 , 0 , & zErrorMsg );

encountered such a problem during the development process:

Operate different tables of two database files respectively. The execution sequence is: open db A->begin trasaction->open db B-&gt ;select from db B->close db B->select from db A->rollbak or commit->close db A

The test found that the step select from db B will go wrong, and the error message is library routine called out of sequence, execute rollback after an error, this step will also report an error.

I originally thought the reason was: starting a transaction can only operate on one database.

The test found that even if the transaction is not started, the execution sequence is: open db A->open db B->select from db B->close db B->select from db A-> close db A, the error of ibrary routine called out of sequence will still occur.

Is it possible to open another database before closing one database?

Does the concurrent execution of sqlite need to be controlled by the user?

I discovered today that the cause of the above error may be that the global variables of the master control program and the subprogram are consistent (both are sqlite3 *db), ​​causing the global variables of the master control program to be modified.

Specific details require further study.