Re: row vs page locking...
From: John (jsparrowNOSPAM_at_ecclesdeletethiscollege.ac.uk)
Date: 09/06/04
- Next message: John: "Re: custom procedures in master"
- Previous message: Victor Feng: "How to assign a channel to a filegroup in Raid 5?"
- In reply to: Randall Sell: "Re: row vs page locking..."
- Next in thread: Russell Fields: "Re: row vs page locking..."
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 6 Sep 2004 19:21:10 +0100
The Firebird guys don't consider this a bug, just an artifact of a different
concurrency system... it's certainly something you should be aware of. I
think there are thousands of systems out there that are potentially buggy in
multi-user situations (I know some of mine are! shhhhhh).
Maybe it's a problem of Interbase marketing - it used to be aimed at dbase /
Access crowd, whereas actually it is *very* specialist and arguably the
concurrency semantics are more tricky to learn than SQL Server.
I agree it would be nice if these were deferred until commit. That would
seem to be 1) easier to implement 2) more logical. While it may be a pain in
the ass for client/server systems (long transaction), n-tier and web systems
would be fine with it. Who writes long transaction apps these days?? (leave
the room, now! grin).
Consider checking for overlapping periods. Table UserCosts like:
pk int primary key
startdate datetime (timestamp in Interbase)
enddate datetime
You want to prevent overlapping periods. Can't do it with indexes, so no
Interbase 'magic' can't come into play. In SQL Server you can write a
trigger something like:
if exists(select i.entryid from inserted i inner join UserCosts u on
not(i.startdate > u.enddate or i.enddate < u.startdate) and i.entryid <>
u.entryid)
begin
raiserror('UserCost intersects another record',16,1)
rollback transaction
return
end
In Interbase / Firebird you can't see other uncommitted records!!! You have
two choices: 1) use table_stability isolation, and essentially go
single-user. or 2) implement a dodgy linked-list style approach.
Interbase has a 'check contraint select' that is totally broken - because it
uses the user context and not the 'magic' system context.
After exploring these issues, I switched development of a project from
Firebird to SQL Server.
Having said all this, I do think Firebird is cool. You just need to be aware
of it's quirks and limitations. It's not in the same league as SQL Server.
But it is very good (and free, more so than MySQL, and multi-platform).
Perhaps in the n-tier / webby world, cool concurrency with long-duration
transactions is not so important?
John
"Randall Sell" <randall@bytewise.nospam.com.au> wrote in message
news:UKR_c.20876$D7.9498@news-server.bigpond.net.au...
>
> John wrote:
>>
>> My only reservation about Firebird is the 'magic' it has to use to
>> enforce unique indexes, foreign keys etc. Ie you could get a situation
>> like this:
>>
>> Trans 1:
>> Insert into mytable values (1); -- this is the PK
>> -- don't commit yet
>>
>> Trans 2:
>> Insert into mytable values (1); -- doesn't work, PK violation. So try
>> this:
>> Select count(*) from mytable where pkfield = 1 -- returns 0. hhhmmmm!
>>
>
> Indeed, I can produce the same issue. I would have though that changing
> the isolation level would address this, but it does not. This borders (in
> my mind) as a bit of a bug. Since the second transaction shouldn't know
> anything about an inserted record of "1" from the first transaction hence
> it should also execute successfully, and not until one of the two commits,
> should the other raise an exception.
>
> Thanx for pointing this out.
- Next message: John: "Re: custom procedures in master"
- Previous message: Victor Feng: "How to assign a channel to a filegroup in Raid 5?"
- In reply to: Randall Sell: "Re: row vs page locking..."
- Next in thread: Russell Fields: "Re: row vs page locking..."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
