[firebird-support] Transactions isolation levels and collisions

[Aldo Caruso aldo.car...@argencasas.com [firebird-support]]

Hello,

   I'm trying to understand interactions between transactions with 
different isolation levels.

   My environment is Firebird Super Server 2.5 (v. 2.5.2) on Linux 
Ubuntu 14.04

   I'm testing by opening two consoles and running isql-fb on both consoles.

On the first one I start a SNAPSHOT transaction ( lets call it 
transaction A ).

Afterwards, on the second console, I start another transaction with a 
READ COMMITTED RECORD_VERSION isolation level ( lets call it transaction 
B ).

In transaction B, I update a record X and commit the transaction.

In the same second console I start a third transaction, also READ 
COMMITTED RECORD_VERSION ( lets call it transaction C ).

In transaction C, I update record X, but neither commit it nor roll it 
back ( transaction C remains active ).

On the first console, in transaction A, I update record X and it 
obviously waits for a commit or a rollback of transaction C.

Back on the second console, in transaction C, I roll it back.

Not surprisingly, on the first console, transaction A ends waiting with 
a collision error, because although transaction C rolled back, 
transaction B, that had started after transaction A, had made an update 
and committed it. As the isolation level of transaction A was SNAPSHOT, 
it finds a collision and so an error is risen.

So we have the following situation:

If transaction C ends rolling back (as in the example), transaction A 
raises an error because of the collision with the previous transaction 
that touched and committed the record ( transaction B ).

If transaction C ends committing, transaction A also raises an error 
because of the collision with transaction C.

Whichever way transaction C ends ( committing or rolling back ), the 
waiting update in transaction A is aborted with an error message because 
of a collision.

The question is which was the point of waiting in transaction A for the 
end of transaction C ? Why didn't it raise an error in the same moment I 
tried to do an update in transaction A ? Couldn't it foresee that the 
record was previously updated by a committed transaction B, which 
started after the SNAPSHOT transaction A, and so whichever the outcome 
of C were there would be a collision ?

Note also that if in the example above, we wouldn't have started 
transaction C, transaction A would raise an error as soon as it tried to 
update the record that was updated and committed by B ( I tested it ).

Thanks for any answer.

Aldo Caruso

Re: [firebird-support] Firebird 2.5 classic performance issue on linux64

[Andreas Zeller zel...@lux-medien.com [firebird-support]]

Hi guys,

I'm just responding one more time because I want to mark this as solved
for me:

Several things did the trick:

  * Upgrade Firebird to 2.5.7 (apparently there was a subquery bug in
2.5.4 which wasn't fixed in the debian package) - thanks to LiENUS
on IRC.
  * blockdev -setfra 32768  - this has nothing to do with
firebird but speeds up reads in the RAID array quite a bit. Thanks
to wltjr on IRC.
  * switching to manual sweeping over automatic and transaction-based
  * doing a complete gbak backup/restore one more time to re-index
  * tweaking xinetd (flags = REUSE NODELAY)

Tweaking a system for firebird is a little bit different from tweaking
it for other databases, because firebird uses one huge file and not
several of them scattered all over the filesystem. Not judging, just
saying it's a different approach :)

Maybe I'll write something up on our blog for this so people can more
easily find the bottlenecks. This took me two weeks of research and a
lot of asking around. One simple best-practice howto would have saved me :)

If anyone cares, I will write this down as an optimized firebird linux
setup from start to finish and run this by you guys. Most of the howtos
and optimization resources I could find focus on Windows (CPUAffinity
tweaks, etc.) - and I'd like to help people out who want to skip the
windows section :)

Thanks again.

Andreas

 


On 28.02.2017 00:33, 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support] wrote:
>  
>
> Andreas,
>
>  
>
>  
>
> this is the one thing I am getting when I am connecting to the
> database. I am not the one working productively on the system, so I
> can't really tell wether this has become faster or is still the same.
>
> LOCK_HEADER BLOCK
> Version: 145, Active owner:  0, Length: 7048576, Used: 540536
> Flags: 0x0001
> Enqs:   5031, Converts:113, Rejects:  8, Blocks: 11
> Deadlock scans:  0, Deadlocks:  0, Scan interval:  10
> Acquires:   7695, Acquire blocks:  3, Spin count:   0
> Mutex wait: 0.0%
> Hash slots: 30011, Hash lengths (min/avg/max):0/   0/   4
> Remove node:  0, Insert queue:  0, Insert prior:  0
> Owners (3):forward: 252920, backward: 490968
> Free owners: *empty*
> Free locks (5):forward: 254960, backward: 519480
> Free requests (6):forward: 540344, backward: 403464
> Lock Ordering: Enabled
>
> This is what the fb_lock_print output looks like.
>
>  Those numbers look to be very good.
>
>  Q: Why are you running Classic server?  How many
> users/connections are there usually to the database?
>
>  Perhaps SuperServer provide better performance – it would allow
> you to “blow up” the page cache size.
>
>  
>
> 

-- 

*Andreas Zeller*

Geschäftsleitung
lux-medien.com 
Carl-Friedrich-Gauss-Str. 56
47475 Kamp-Lintfort

Office: +49 2151 32 66 628
Fax: +49 2151 32 66 721
Mobil: +49 163 27 9 1979







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

[firebird-support] Strange behaviour on Update Operation

[trsk...@yahoo.com [firebird-support]]

Hi all,

I have a strange result when running update operation.

I am using TIBSQL from Borland CBuilder 6, Firebird Super Clasic 2.5.2 (64bit) 
on Windows 7 64 bit.

Here are the script from TIBSQL :
=
Update table1 set field1 = 'abc', field2 = 'abc', field3 = field3 + 1 where id 
= 1 returning new.field1; 
 

 Field1 & Field2 = Varchar(12)
 Field3 = smallint default 0
 

 After that script executed, my app checked whether field1 is null or not. If 
it was null, an error message raised to users.
 

Sometimes (not always) field2 & field3 have a value while field1 is null, but 
no error message raised.
 

 This is strange because this is the only procedure on my app that could update 
these fields (field1,field2, & field3). 

 

 Initial condition for these fields should be null (field1 & field2) and 0 for 
field3, and when update occur, field1 & field2 must have a value while field3 
has a value 1 or 2 or 3, so on. 

 

 Has anyone experiences some problem? 

 

 Thanks in advance.
 Regards,
 Anto