Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Tue Dec 12, 2017 3:05 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: SQLite Rollback Question
PostPosted: Wed Oct 28, 2009 3:36 am 
Offline

Joined: Mon Aug 20, 2007 9:42 am
Posts: 190
Let's say I have a multi-user database with two users, Anne and Bob, each using their own DB login.

1. Anne starts adding some records with, but doesn't COMMIT them.
2. Bob starts adding some records, but doesn't COMMIT them.
3. Anne and Bob continue adding records.
4. Anne issues a COMMIT command to actually add the data to the database.
5. Bob decides he doesn't want to save the records he added for some reason, and a ROLLBACK command is issued.

Two questions:

1 - Does Bob's ROLLBACK revert the database to its state at point 2, or does it only undo his own additions?
2 - Whilst Anne and Bob are adding records which are unCOMMITted, can they see each others additions in the database via SELECTs?


Thanks,
Tom


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite Rollback Question
PostPosted: Wed Oct 28, 2009 8:46 am 
Offline

Joined: Wed Sep 28, 2005 8:30 am
Posts: 5479
Location: Austin, TX
When you start a transaction with SQLite the file becomes locked and will prevent other users from making changes until the transaction is committed or rolled back. So the second that Anne starts adding some records, Bob will be preventing from modifying the database until she commits.

This is why your transactions should happen quickly, and you should NOT use transactions as an undo mechanism with a GUI. See tip number 6 in my database tips threads.

You might also look into the REAL Server engine if you want improved concurrent database access with a SQLite database.

Hope this helps.

_________________
Mike Bailey
Ekim Software
http://www.ekimsoftware.com/


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite Rollback Question
PostPosted: Wed Oct 28, 2009 11:24 am 
Offline

Joined: Mon Aug 20, 2007 9:42 am
Posts: 190
I am in fact using Real SQL Server 2008r1 - probably should've mentioned that!

Mike, I read your database tips about once a week as my 'best practises' bible - it really is useful, so thanks for that.

Out of interest, let's say Anne starts a process which adds 500 records at once, which will take a non-'zero' amount of time, especially considering network connections, etc.. If, during that small window whilst Anne's transaction is taking place, Bob tries to do a SELECT, will an error result, or will it just wait until Anne's transaction has finished and then give Bob his data?


Top
 Profile  
Reply with quote  
 Post subject: Re: SQLite Rollback Question
PostPosted: Wed Oct 28, 2009 2:34 pm 
Offline

Joined: Wed Sep 28, 2005 8:30 am
Posts: 5479
Location: Austin, TX
Read operations should work fine while there is an open transaction from another user, however you will (I think) get the uncommitted data in return. You should be able to test this with two sample apps. Have one open a transaction and change some data without committing, and then have the second one try to grab the data and see what results you get.

Hope this helps.

_________________
Mike Bailey
Ekim Software
http://www.ekimsoftware.com/


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 4 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group