Row/Table Locks and SQL Server

[Charles Sheehan-Miles-2]

Okay, I'm observing a very strange phenomena.

Following the bulk insert of a fairly complex xml document, I run a stored
procedure which parses through the document passing the values into
relational tables.  This part is all done in SQL, inside the stored
procedure.

Platform:

Windows Server 2008 64bit
SQL Server 2008
Coldfusion 8

If I do this from the query analyzer, it executes instantly, parses the
document happily and moves on, with row locks on the database never more
than 50 or so:

exec myProcedure @id=somevalue, blah other variables

However, if I call the following code in coldfusion, I end up with
potentially _thousands_ of row locks on the table, and the database becomes
unusable within seconds:

<cfstoredproc procedure="myProcedure " datasource="#application.maindsn#">
                    <cfprocparam type="in" value="#somevalue#"
cfsqltype="cf_SQL_INTEGER">
                    ... blah blah other parameters
</cfstoredproc>

That wasn't working out, so I tried this instead, so that I had isolated the
_exact_ same code as from query analyzer:

<cfquery datasource="#application.maindsn#">
        exec myProcedure @id=somevalue, blah other variables
 </cfquery>

The result this way is even worse.

This leads me to think there may be some extra locking being forced by the
jdbc driver, or I'm just doing something really wrong. I am not wrapping
this in cftransaction at this point.  Has anyone encountered anything like
this?

--
-------------------------------
Charles Sheehan-Miles
202-412-2433 | charles@...
http://www.linkedin.com/in/sheehanmiles


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328261
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4

[Kris Jones]

Does your procedure specify rowlocks, nolocks, etc? Without that you
may be at the mercy of the client (or driver in this case)

Cheers,
Kris

> Following the bulk insert of a fairly complex xml document, I run a stored
> procedure which parses through the document passing the values into
> relational tables.  This part is all done in SQL, inside the stored
> procedure.
>
> Platform:
> Windows Server 2008 64bit
> SQL Server 2008
> Coldfusion 8
>
> If I do this from the query analyzer, it executes instantly, parses the
> document happily and moves on, with row locks on the database never more
> than 50 or so:
>
> exec myProcedure @id=somevalue, blah other variables
>
> However, if I call the following code in coldfusion, I end up with
> potentially _thousands_ of row locks on the table, and the database becomes
> unusable within seconds:
>
> <cfstoredproc procedure="myProcedure " datasource="#application.maindsn#">
>                    <cfprocparam type="in" value="#somevalue#"
> cfsqltype="cf_SQL_INTEGER">
>                    ... blah blah other parameters
> </cfstoredproc>
>
> That wasn't working out, so I tried this instead, so that I had isolated the
> _exact_ same code as from query analyzer:
>
> <cfquery datasource="#application.maindsn#">
>        exec myProcedure @id=somevalue, blah other variables
>  </cfquery>
>
> The result this way is even worse.
>
> This leads me to think there may be some extra locking being forced by the
> jdbc driver, or I'm just doing something really wrong. I am not wrapping
> this in cftransaction at this point.  Has anyone encountered anything like
> this?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists
Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:328284
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.4