Board index » delphi » IB & M$SQL

IB & M$SQL

Is there any difference in row lock mechanism on IB and M$SQL.
I heard that M$SQL locks entire page when you update just one row.
If it's so then how to manage page locks which could lead to deadlocks in
M$SQL?

Thanks.

 

Re:IB & M$SQL


Where'd you hear that? The 6.5 docs? They are correct. For 6.5. Read the 7.0
docs. They apply to 7.0.
As do the SQL Server 2000 docs to the SQL Server 2000 product. 7 and higher
implement row level locking when one of the potential lock participants is
utilizing an index.

Re:IB & M$SQL


Your mileage may vary.  I've never seen MSSQL 7 initiate a row-level lock
(Updates/Inserts) on it's own.  The traces I've ran (for about a month on a
heavily used app), the server always used page locks (which is fine).   The
only way I can get a row lock is if I use the ROWLOCK optimizer hint (within
a transaction, this works nicely).  As I understand it, IB does no locking.
It does versioning (did I get it right?) .  An IB expert could probably
explain this better than I.

IMO, Microsoft's advertising of row-level locking mislead a lot of
developers moving from a desktop db environment to CS.  This is not
pessimistic locking.  If a record is locked, end-users requesting the record
won't get "The record is locked by xyz on 04/17/2001", all they get is an
hourglass.  You need pessimistic locking in MSSQL, you will have to do it
yourself (and it's a headache)

krf

Quote
Justin Pitts <jpi...@cnstores.com> wrote in message

news:3adc810c$1_2@dnews...
Quote
> Where'd you hear that? The 6.5 docs? They are correct. For 6.5. Read the
7.0
> docs. They apply to 7.0.
> As do the SQL Server 2000 docs to the SQL Server 2000 product. 7 and
higher
> implement row level locking when one of the potential lock participants is
> utilizing an index.

Re:IB & M$SQL


Quote
"Kevin Frevert" <kfrev...@midwayusa.com> wrote in message

news:3adc93b0$1_1@dnews...

Quote
> Your mileage may vary.  I've never seen MSSQL 7 initiate a row-level lock
> (Updates/Inserts) on it's own.  The traces I've ran (for about a month on
a
> heavily used app), the server always used page locks (which is fine).
The
> only way I can get a row lock is if I use the ROWLOCK optimizer hint
(within
> a transaction, this works nicely).  As I understand it, IB does no
locking.
> It does versioning (did I get it right?) .  An IB expert could probably
> explain this better than I.

> IMO, Microsoft's advertising of row-level locking mislead a lot of
> developers moving from a desktop db environment to CS.  This is not
> pessimistic locking.  If a record is locked, end-users requesting the
record
> won't get "The record is locked by xyz on 04/17/2001", all they get is an
> hourglass.  You need pessimistic locking in MSSQL, you will have to do it
> yourself (and it's a headache)

    I think you can specify a locking scheme against the index using
sp_indexoption.

-- Reddy Palle.

Re:IB & M$SQL


Quote
Kevin Frevert wrote:

> As I understand it, IB does no locking.
> It does versioning (did I get it right?)

        More or less.  To be precise, it does no blocking.  If there's a
conflict between different versions of a record in two transactions, you
see it when you Commit.  Transaction isolation can be set to either
report a deadlock immediately or wait to see if the other transaction
rolls back.  While the transaction is open it's working on its own
version of the record and can do anything it wants to that version.

        This is always done at the row level.  There's no such thing as a page
(b)lock in IB.  It's possible to pessimistically lock a table, but it's
almost never done.

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Re:IB & M$SQL


 "Craig Stuntz (TeamB)" <cstuntz@no_spam.vertexsoftware.com> wrote:

Quote
> Kevin Frevert wrote:
> > As I understand it, IB does no locking.
> > It does versioning (did I get it right?)
>    More or less.  To be precise, it does no blocking.  If there's a
> conflict between different versions of a record in two transactions, you
> see it when you Commit.  Transaction isolation can be set to either
> report a deadlock immediately or wait to see if the other transaction
> rolls back.  While the transaction is open it's working on its own
> version of the record and can do anything it wants to that version.
>    This is always done at the row level.  There's no such thing as a page
> (b)lock in IB.  It's possible to pessimistically lock a table, but it's
> almost never done.

From http://www.ibphoenix.com/ibp_assumptions.html

--------------------------
Most database systems implement concurrency control with locks. Update
locks prevent simultaneous users from modifying a single row. Read
locks protect the consistency of a readers view of data. At the time
InterBase was built, locking produced two problems:

A full implementation of update and read locks reduced concurrency to
an unacceptable level. A single large report could block all updates
to the database.

Locks were expensive. The lock table must be kept in memory for
performance. Memory was expensive and very limited. Two or three
megabytes was a large memory. Using record versions allowed InterBase
to work with a lock table that was small and quite stable in size.
-----------------------------------

My question is:

Why is there a lock table at all if InterBase doesn't *_do_* locking?

I am trying  to understand the InterBase model, and I thought that it
was non-locking (it's major selling point) but now I read that it has
a lock table - what is going on?

Is there something that I haven't understood?

Paul...

Quote
>    -Craig

Re:IB & M$SQL


Quote
Paul Linehan wrote:

> Why is there a lock table at all if InterBase doesn't *_do_* locking?

        The problem here is semantics.  There is no way that two transactions
can simultaneously commit changes to the same record without a
conflict.  IB has no magic to make this go away.  What IB does not do is
blocking (note the 'b').  Simply reading the record -- or even writing
to it -- will not stop other transactions from going about their
business.  The only thing which raises an error is when there is an
actual conflict, i.e., two transactions attempt to commit changes to the
same record.

        In some other databases, simply reading a record locks it to other
transactions.  This is a disaster for concurrency.  In other databases,
reading does not lock the record but writing does.  This is much better,
but it still doesn't take into account the chance that one transaction
might rollback.  IB only raises an error when there is an actual
deadlock.

        Versioning also has other advantages which I haven't discussed here.
It makes rollbacks trivial to implement, for example -- just throw away
the version.  Other databases have to replay their transaction logs to
achieve a rollback.

        HTH,

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Re:IB & M$SQL


Quote
> Versioning also has other advantages which I haven't discussed here.
> It makes rollbacks trivial to implement, for example -- just throw away
> the version.  Other databases have to replay their transaction logs to
> achieve a rollback.

Yep, and I've personally caused our MSSQL database to go into 'Recovery
mode' because something happened to the log.  M$ support said it was a
hardware problem, but didn't/couldn't say anything more specific.  Luckily
it was the development server, but the other developers sure didn't feel
lucky :)
krf

Re:IB & M$SQL


Quote
Paul Linehan wrote in message

<3add95fa.92473...@newsgroups.borland.com>...

Quote

>> > As I understand it, IB does no locking.
>> > It does versioning (did I get it right?)

While a transaction is open, any records you update will be "locked"
against other updates from other subsequent transactions, these others
will get deadlock errors (this depends on their transaction parameters
too). This lock does not block readers in other transactions. Those
readers' transaction parameters determine exactly what they read - the
original prior to your transaction or the "dirty" updated one.

--
Wayne Niddery (WinWright Inc.)
RADBooks - http://members.home.net/wniddery/RADBooks/delphibooks.html
"At the apex of every great tragedy of mankind there stands the figure
of an incorruptible altruist" - Ayn Rand

Other Threads