Re: ensuring row/table locking

[Tobias Downer <toby@xxxxxxxxx>]

Aaron,

If the commit fails because of a conflict, the transaction is 
automatically rolled back.  If it makes you feel safer, you can 
explicitely use the 'rollback' command after a conflict fail (rollback 
will never fail under normal circumstances).  When the transaction is 
rolled back after a conflict, the next iteration of the loop will see 
the $0 balance.  You will have to actually run the 'SELECT balance FROM 
accounts WHERE user = 'Bob'' query again to see the $0 balance.

Toby.

Aaron Westendorf wrote:
> Toby,
> 
> Thank you for the response, you addressed part of the problem I'm 
> working through, but I'm still unsure about one aspect of transactions, 
> and in particular how it works in Mckoi and Oracle.
> 
> Say I have a customer with $1 in their account.  In my code below, I 
> have a request to spend $1 on a new Foo.  My account server software is 
> running on several systems, and so another server is processing a cyclic 
> request for Foo Filler, also $1.  Inside of my loop, I check the current 
> balance, and then attempt to decrement it.  Consider the situation where 
> both transactions are happening simultaneously, and the Foo Filler order 
> is committed after running the check on the balance for the Foo order. 
> The Foo Filler order is processed fine, and we can consider it 
> completed.  The Foo order on the other hand will fail when it tries to 
> commit(), and we go back to the top of the loop.  My concern is that, 
> according to one interpretation of the documentation on the Mckoi 
> website, if I run the query again to see the current balance, I'll still 
> see $1, not 0.
> 
> It really comes down to that last sentence there, so if you or someone 
> else could address that, I'd appreciate it.
> 
> Thanks for the help!
> -Aaron
> 
> Tobias Downer wrote:
> 
>> Aaron,
>>
>> You only need to call 'setAutoCommit(false)' once per connection.  
>> Once it is off it stays off even if a commit fails.
>>
>> Aaron Westendorf wrote:
>>
>>> ==========================================================
>>> // Open connection
>>> connection.setAutoCommit( false );
>>> queryStatement = connection.prepareStatement( getStuffQuery );
>>> // Setup query parameters
>>>
>>> while(true) {
>>>   // Query and test current value
>>>   // If passed, update
>>>   connection.commit();
>>>   // If catch exception, continue through loop.  Else, break.
>>> }
>>> ==========================================================
> 
>