Re: MSAcces as a SQL front-end

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Mike Hodgson (mike.hodgson_at_mallesons.nospam.com)
Date: 02/23/05 

Date: Thu, 24 Feb 2005 09:22:20 +1100

I'm not so sure that Access is not the problem. I agree that no matter
what tool or language, if you have an idiot writing the code you'll get
an idiotic app. However, I've observed over the years that MS Access
doesn't play very well with others in a true multi-user RDBMS like SQL
Server. It seems to behave like it's the only one with the right to use
the server. My main gripe is that it tends to hold locks on tables far
longer (indefinitely?) than it needs to. Let me explain in concrete terms.

I created a simple junk table in a SQL DB and filled it with garbage data:

create table dbo.Junk
    (
    ID int identity(1,1) not null primary key clustered,
    blah char(250) null,
    otherData float null
    )
go
create nonclustered index IX_Junk_blah on dbo.Junk(blah)
create nonclustered index IX_Junk_other on dbo.Junk (otherData)
go

set nocount on
declare @i int
set @i = 100000
while (@i > 0)
    begin
    insert into dbo.Junk (blah, otherData) select 'String ' + cast(@i -
@@IDENTITY as varchar(15)), rand(@i)
    set @i = @i - 1
    end
set nocount off

Now when I query the table from query analyser or osql or one of those
tools, they run a simple SELECT on the table ands hold shared locks on
rows (KEY locks in this case) and pages only for the duration on the
select. I then create a new empty MDB in Access and add a single linked
table which points at my dbo.Junk table through a user ODBC DSN. When I
double-click on the linked table in Access to "open" it and I run a
slightly modified version of sp_lock on the server to see what locks
MSAccess is holding, I see that well after the query has been executed
it's holding a shared page lock on one of the nonclustered indexes (and
an IS TABLOCK as well of course).

spid Database ObjectName IndexName Type Resource
Mode Status
------ -------- -------------- -------------- ---- ----------------
-------- ------
    53 DBA DB
S GRANT
    53 DBA TAB
IS GRANT
    53 DBA Junk IX_Junk_other PAG 1:4692
S GRANT

This makes it a right royal P-in-the-A to get an exclusive lock on that
page to update data for example, and I've noticed that the average
MSAccess user doesn't think that other users might want to user that
data too so they "open" a table in the morning and leave it open all day
in the background (occasionally flicking back to reference it) while
they do other things (like go to lunch or go home!). This just seems
like a really bad design and it's just stock standard MSAccess - no VBA
code involved. Why hold on to locks for the whole time that the linked
table is open? The MSAccess apps around my firm (law firm) cause the
DBAs big headaches for just this reason.

Also, I've noticed it opens 2 connections to the DB. One to get the
primary key/identity column in my table:
(from Profiler, SPID 53)
SELECT "dbo"."Junk"."ID" FROM "dbo"."Junk"

and the other to get the rest of the data:
(from Profiler, SPID 56)
declare @P1 int
set @P1=-1
exec sp_prepexec @P1 output, N'@P1 int,@P2 int,@P3 int,@P4 int,@P5
int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int', N'SELECT
"ID","blah","otherData" FROM "dbo"."Junk" WHERE "ID" = @P1 OR "ID" =
@P2 OR "ID" = @P3 OR "ID" = @P4 OR "ID" = @P5 OR "ID" = @P6 OR "ID" =
@P7 OR "ID" = @P8 OR "ID" = @P9 OR "ID" = @P10', 30960, 84628, 30959,
84627, 30958, 84626, 30957, 84625, 30956, 84624
select @P1

and it's the 1st SPID that gets the ID column (SPID 53 in this case)
that's holding the PAG lock indefinitely. What's up with that?!?!?! Is
this MSAccess's fault? Is it by design? Or is it due to ODBC?
(osql.exe uses ODBC and doesn't exhibit the same behaviour.) It
definitely doesn't happen with other apps unless they specify the "WITH
(HOLDLOCK)" table hint in their FROM clauses. 

--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@mallesons.nospam.com |* W* http://www.mallesons.com
Andy O'Neill wrote:
>"Mary Chipman [MSFT]" <mchip@online.microsoft.com> wrote in message 
>news:k23p11pnlrvj7cfc0l7vrjqd7agkk1i3ml@4ax.com...
>  
>
>>I agree that it's a terrible idea to let users link directly to
>>tables, especially if they're large. Ad hoc querying works best on
>>much smaller databases and with a fairly sophisticated user
>>population. Best practice in any querying tool would be to construct a
>>"query by form" interface which forces the users to fill in combo
>>boxes, etc., to restrict the result sets returned, or in the case of
>>reports, to call appropriate parameterized stored procedures. If the
>>developer on the front-end doesn't restrict users' activities, then
>>you're going to have support issues no matter which tool you use.
>>
>>--Mary
>>    
>>
>
>Precisely.
>
>Access is not really the problem.
>Swop it for dotnet or whatever and you'd still have problems if you allow 
>untrained people to design apps.
>Welll... they probably wouldn't even get a screen together,
>Then you have another problem as presumably there's a business reason why 
>the users want to use that data.
>
>Mix  some plaster and water.
>Pick up a hawk and a float.
>Does that make you a plasterer?
>Nope, but you ARE equipped to make one helluva mess of a wall.
>
>However.
>Take the tools off the end user and you have to supply someone to write 
>their apps for them.
>
>Sometimes a bit of training by a developer can go a long way to helping 
>power users out.
>Educating your power users and helping them to use access better is worth 
>thinking about.
>Providing them with some views might also be an idea.
>
>Take their toys off em and some users are likely to get annoyed.
>If they can break your database integrity, how come?
>Why do your constraints etc  not stop em?
>Be careful you're not stood in a glass house before you start chucking 
>rocks.
>
>
>  
>

Relevant Pages

  • Re: Single database transaction across a multiple HTTP requests?
    ... Either this transaction is poorly designed, or you entire systems of stored ... You will need to change one or the other until you obtain a design that will ... > database engine. ... It is normal practice to issue the locks manually just before performing the ...
    (comp.lang.php)
  • Re: access data from a closed wb protected with password without o
    ... It looks like that previous posted message locks just excel--not the desktop ... "Dave Peterson" wrote: ... don't save that workbook with the windows hidden. ... opens does not flash on the screen but it shows in the tool bar at the time ...
    (microsoft.public.excel.programming)
  • Re: Sharepoint Portal Server 2003 Backup: Is it even possible?
    ... I wasn't looking at the locks when I used it. ... > One of the books is the MS Press "Sharepoint Products and Technologies" ... > What you say about the SQL server backup makes sense to me - to do it you'd ... > almost have to detach the database everytime you wanted to back up the site.. ...
    (microsoft.public.sharepoint.portalserver)
  • Access causing other programs to CRASH
    ... When I try to log off, I close down Outlook. ... >suddenly my users are experiencing slow response times ... >department thinks that it is the Access database but I ... >when it locks up it doesn't hit any debug errors. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: .Net framework and VB
    ... all DB operation puts locks on the tables so as the ... I have many years of expirance in designing distributed system based on ... One class which is used for all database read ... CustomerSingleSelect - a calss which gets data from customer based on ...
    (microsoft.public.dotnet.framework)