SQLite transaction and auto-increment deep analysis, and sqlite transaction deep analysis

SQLite transaction and auto-increment deep analysis, and sqlite transaction deep analysis

SQLite is nothing better, so we are afraid that "database is locked" will try our best to circumvent it over the years.

Test code:

Static void Test2 () {XCode. setting. current. transactionDebug = true; XTrace. writeLine (Role. meta. count + ""); XTrace. writeLine (Log. meta. count + ""); Console. clear (); Task. run () => TestTask (1); Thread. sleep (1000); Task. run () => TestTask (2);} static void TestTask (Int32 tid) {try {XTrace. writeLine ("TestTask {0} Start", tid); using (var tran = Role. meta. createTrans () {var role = new Role (); role. name = "R" + DateTime. now. millisecond; role. save (); XTrace. writeLine ("role. ID = {0} ", role. ID); Thread. sleep (3000); role = new Role (); role. name = "R" + DateTime. now. millisecond; role. save (); XTrace. writeLine ("role. ID = {0} ", role. ID); Thread. sleep (3000); if (tid = 2) tran. commit () ;}} catch (Exception ex) {XTrace. writeException (ex);} finally {XTrace. writeLine ("TestTask {0} End", tid );}}View Code

 

After the warm-up environment, we started two tasks to execute the test function, with an interval of 1 second.
The test function inserts two rows of data at an interval of 3 seconds.
The first task is rolled back and the second task is submitted.
Obviously, the two tasks overlap.

 

Curious: What is the auto-increment obtained by Task 2 after Task 1 is applied for auto-incrementing by 1?
After Task 1 is rolled back, what can be done with the auto-increment number it applied?


Result:

 

02:45:03. 470 6 Y 5 TestTask 1 Start02: 45: 03.470 6 Y 5 Transaction. begin ReadCommitted02: 45: 03.486 6 Y 5 Select Count (*) From Role Where Name = 'r470' 02: 45: 03.501 6 Y 5 Insert Into Role (Name, IsSystem, Permission) values ('r470', 0, ''); Select last_insert_rowid () newid02: 45: 03.517 6 Y 5 start initializing entity class UserX02: 45: 03.517 6 Y 5 complete initialization entity class UserX02: 45: 03.533 6 Y 5 role. ID = 110:45:04. 486 14 Y 6 TestTask 2 Start02: 45: 04.486 14 Y 6 Transaction. begin ReadCommitted02: 45: 04.486 14 Y 6 Select Count (*) From Role Where Name = 'r0000' 02: 45: 04.486 14 Y 6 Insert Into Role (Name, IsSystem, Permission) values ('r486 ', 0, ''); Select last_insert_rowid () newid02: 45: 05.251 15 Y 7 Transaction. begin ReadCommitted02: 45: 05.251 15 Y 7 Insert Into Log (Category, [Action], LinkID, CreateUserID, CreateTime, Remark) Values ('roame', 'add', 11, 0, '2017-01-27 02:45:03 ', 'Id = 11, Name = r470'); Select last_insert_rowid () newid02: 45: 2017 6 Y 5 Select Count (*) from Role Where Name = 'r548' 02: 45: 06.548 6 Y 5 Insert Into Role (Name, IsSystem, Permission) Values ('r548', 0 ,''); select last_insert_rowid () newid02: 45: 06.548 6 Y 5 role. ID = 120:45:09. 555 6 Y 5 Transaction. rollback ReadCommitted02: 45: 09.555 6 Y 5 TestTask 1 End02: 45: 09.618 14 Y 6 SQL statements take a long time. We recommend that you optimize Insert Into Role (Name, IsSystem, Permission) by 5,120 milliseconds) values ('rforward', 0, ''); Select last_insert_rowid () newid02: 45: 09.618 14 Y 6 role. ID = 110:45:12. 633 14 Y 6 Select Count (*) From Role Where Name = 'r633' 02: 45: 12.633 14 Y 6 Insert Into Role (Name, IsSystem, Permission) values ('r633', 0, ''); Select last_insert_rowid () newid02: 45: 12.633 14 Y 6 role. ID = 120:45:15. 649 14 Y 6 Transaction. commit ReadCommitted02: 45: 15.649 14 Y 6 TestTask 2 End02: 45: 15.774 15 Y 7 SQL takes a long time. It is recommended to optimize Insert Into Log (Category, [Action], LinkID, createUserID, CreateTime, Remark) Values ('roame', 'add', 11, 0, '2017-01-27 02:45:03 ', 'Id = 11, Name = r470 '); select last_insert_rowid () newid02: 45: 15.774 15 Y 7 Transaction. commit ReadCommitted02: 45: 16.622 16 Y 9 Transaction. begin ReadCommitted02: 45: 16.622 16 Y 9 Insert Into Log (Category, [Action], LinkID, CreateUserID, CreateTime, Remark) Values ('roame', 'add', 12, 0, '2017-01-27 02:45:06 ', 'Id = 12, Name = r548'); Select last_insert_rowid () newid02: 45: 2017 16 Y 9 Insert Into Log (Category, [Action], LinkID, CreateUserID, CreateTime, Remark) Values ('roame', 'add', 11, 0, '2017-01-27 02:45:09 ', 'Id = 11, name = R486 '); Select last_insert_rowid () newid02: 45: 16.622 16 Y 9 Insert Into Log (Category, [Action], LinkID, CreateUserID, CreateTime, Remark) values ('roame', 'add', 12, 0, '2017-01-27 02:45:12 ', 'Id = 12, Name = R633'); Select last_insert_rowid () newid02: 45: 16.637 16 Y 9 Transaction. commit ReadCommitted

 

From the test results:
1. Task 1 is applied for 11 and 12, and task 2 is also
2. After Task 1 is applied for 11, Task 2 is started. When the Insert operation is executed, 5.12 seconds are blocked until Task 1 rolls back the transaction.
3. threads 15 and 16 are asynchronous log writing. Obviously they are also blocked. threads 15 are blocked for 10.519 seconds.


Conclusion:The exclusive lock is used when SQLite executes the update transaction operation to force auto-increment numbers to be allocated synchronously!


Refer:
Http://sqlite.1065341.n5.nabble.com/Transactions-and-sqlite3-last-insert-rowid-td8905.html

 

 
> If I understand it correctly, connection C1 can do an INSERT, get 
> ROWID 4, C2 does an INSERT, gets 5, and commits, and then C1 commits, 
> with its 4; if C1 rolled back, there's no 4 in the database, just 5 
> and whatever else, correct? 
> 
No, this can't happen. As soon as C1 does its insert, it acquires an 
exclusive lock on the database. C2 can't do an insert until C1 either 
commits or rolls back and releases the lock. If C1 committed, then C2 
will get 5, if C1 rolled back, then C2 will get 4.