Write your select statements like this:
SELECT * FROM table1(nolock) inner join table2 (nolock) on
table1.id = table2.id
The nolock keeps the locking from occurring and forces dirty reads on the
data.
With Update statements, write them like this:
UPDATE Table1 WITH (ROWLOCK) SET Field1 = 'value'
FROM Table1 inner join table2 (nolock) on table1.id = table2.id
The WITH(ROWLOCK) statement keeps sql server from escelating the lock to a
full table lock. It will only lock the records it needs.
-----Original Message-----
From: Colin Murphy [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 26, 2002 7:13 AM
To: CF-Talk
Subject: Re: Table Locking Problems
Thanks for your help.
I think I will dig out the SQL books and write a couple of stored procedures
todo this.
Colin
----- Original Message -----
From: "S. Isaac Dealey" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, July 26, 2002 2:01 PM
Subject: Re: Table Locking Problems
> > HELP!
>
> > I am having problems with table locks in one of my databases.
> > These table locks result in the cfm pages failing becuase
> > they either can't
> > access the database or they get a serialization failure.
>
> > I have tried using cflock to stop multiple queries accessing the
> > same table but the problem is the site is running over more than 1
> > server and I can't
> > think of anyway to prevent all of the code on all the
> > servers from accessing
> > the tables at the same time. It also runs on 2 SQL
> > servers which are
> > replicating to each other.
>
> > Does anyone have any suggestions on how I could solve
> > these problems?
>
> > The sites run on coldfusion 4.5.
>
> With some fairly advanced SQL knowledge / work you might be able to
> design
a
> stored procedure which would check for the lock on the given table
> until
it
> is released, then perform the action... Or there might be someone else
here
> who could offer a better solution -- what database system are you
> using?
SQL
> Server , Oracle, something else? I'd be willing to bet the final
> solution will hinge on the db server in use... There is a much more
> crude solution tho I don't know that I'd want to go this route:
>
> Put this in a custom tag and use it to run your sql query...
>
> <cfif thistag.executionmode is "end">
> <cftry>
> <cfquery ...>
> #thistag.generatedcontent#
> </cfquery>
> <cfcatch type="ODBC">
> <cfmodule template="#getfilefrompath(getcurrenttemplatepath())#"
> attributecollection="#attributes#">
> #thistag.generatedcontent#
> </cfmodule>
> </cfcatch>
> </cftry>
> <cfset thistag.generatedcontent = "">
> </cfif>
>
> This will just brute-force repeatedly attempt to access the db until
either
> it's successful in performing its task or it chokes the cf server to
death,
> whichever comes first. :)
>
> Isaac Dealey
> Certified Advanced ColdFusion Developer
>
> www.turnkey.to
> 954-776-0046
>
>
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
