[dba-SQLServer] Locking Discussion

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Sunday, July 13, 2008 12:13 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Locking Discussion

Jim - I am NOT arguing for bound/unbound forms.  Let that go.

> That was not my intention... just a heads up.

What I had never heard of before was that SQL Server has its own mechanism
to prevent data conflicts.  The past discussions had never mentioned that.
And the whole central point of all those discussions was how to prevent data
conflicts with unbound forms - no one even mentioned that SQL Server already
does this.  Read chapter 21 - then you'll see what I just learned is already
done and can be done.

> That is the way all DBs work today. In over 10 years of working with MS
SQL and Oracle, I have never had an unresolved data issue... but I have
heard, in very special circumstances they can happen... But I have no
personal experience with that.  

What I was hoping to get a discussion on was if anyone was actively using
the different isolation levels that can be managed by a FE application, and
how people where doing that, what their results were, etc, etc.

> I have been mostly working with web-based applications lately but there
are a host documentation out there. The only item that I know of is a
article I wrote a few yeasr back on using an Access FE and MS SQL BE. See
article:
http://www.databaseadvisors.com/newsletters/newsletter112003/0311UnboundRepo
rts.asp

I hope this sheds some light on the subject at hand. 

Thanks,
Dan

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence
Sent: Sunday, July 13, 2008 12:50 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] Locking Discussion

Hi Dan:

Listen to Susan she knows what she is talking about.

When using MS SQL Express/MS SQL/MySQL/Oracle or any of the other stand
alone databases the whole concepts of tight binding that you have learned
using the Access MDB are gone. Some built-in Access emulations can be rigged
that give the appearance of bound fields but in some circumstances you will
pay a heavy performance price.

It is best to just let the DB do what it does best... manage data. Built
within it are many methods for handling data conflict resolutions so you do
not have to worry about it. (Note: there are situations in which what is
called a 'deadly-embrace' or 'a locked record due to use conflict' can be
created but it generally takes real effort and all professional DBs have
unlocking tools and time-out solutions.)

When you move 'up' it is time to drop the 'bound' forms and fields and this
can take a bit more programming but it is not difficult and there are many
here amongst the Access and SQL groups that can answer any particular
questions you may have.... and the performance gains are stellar.

Jim  

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Sunday, July 13, 2008 9:26 AM
To: SQL Server List
Subject: [dba-SQLServer] Locking Discussion

To everyone:

Several weeks ago there was a protracted 'discussion' about this issue.
While I skimmed the discussion I never did see anyone provide an answer to
the question, "How can I prevent data conflicts in SQL Server?"

Today I started reading my copy of Mastering SQL Server 2005 Express
Edition, coauthored by Susan Harkins and Mike Gunderloy.  News to me was
that SQL Server has it's own quite sophisticated and flexible locking
mechanism (see Chapter 21 - Locking).  From page 477: "What this means to
you as an application developer is that you almost never have to worry about
locking."

I'm just getting started upsizing an Access app, and would like to hear some
discussion on this and on how this locking mechanism impacts your
development of 'unbound' or 'bound' Access FE applications.

Thanks!
Dan