Thanks, I'll discuss that with our DBA.
I'm not using any CFTRANSACTION, though. What might have an impact here is
that this is a code that loops up to four times and does an insert every
time. The user selects up to four items, and then the code loops through
them and inserts a record in the table in each iteration. It's simple as
<cfif listlen(selectedItems)>
<cfloop list="#selectedItems#" index="thisSelectedItem">
<cfquery name="insertItem" database="someDatabase">
insert into someTable (col1,col2)
values (#thisUser#,#thisSelectedItem#)
</cfquery>
</cfloop>
</cfif>
-----Original Message-----
From: Dave Watts [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 17, 2002 12:19 PM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Subject: RE: What is this SQL Error?
> I'm trying to figure out if ColdFusion locks the whole table
> when running a cfquery.
This is entirely dependent on the database. CF is just a regular database
client, like any other database client.
> I'm getting those deadlock errors in production, where too
> many people access the same table at the same time. It never
> happens in the development environment or in the testing
> environment.
This makes sense, since you're less likely to have collisions with fewer
users.
> But the thing is, I get deadlocks with insert queries!
> Something simple as "insert into sometable (col1, col2,
> col3) values ('val1','val2','val3')" This tells me that the
> lock is not done on a row level, but on the table level.
> I'm using MSSQL 7, and the DBA assures me that it would
> lock at row level, so I'm guessing ColdFusion is requesting
> a lock at table level somewhere. Is there a CFAdmin setting
> for this?
No, as mentioned above, this isn't specific to CF; there's no CF Admin
setting for this. However, I wouldn't be so sure that locking is being done
on a row level. If I recall correctly, that's not the default behavior for
SQL Server 7. I think that SQL Server 7 locks pages (an "internal" storage
unit) rather than locking individual rows or locking the whole table.
Now, here's a problem. You may have several rows within a single page. Also,
if you're using an identity column as a primary key, and you've defined a
clustered index on that column, each insert will be physically located right
after the previous insert. So, if you have one page with one row being
inserted, and that page gets locked, and the database wants to write the
next row to the same page due to the clustered index (which by definition
matches the physical sort order of the column) that second write may be
delayed, and if the database is busy enough, that may cause problems. I've
heard that problem called a "hotspot" before. For a solution to this
problem, I'm not sure what route would be best to take, in your situation.
However, I'm not certain that this, by itself, is the problem you're having;
typically, I think that this problem simply causes things to get
significantly slower. A deadlock, on the other hand, implies that you have
two transactions occurring, and neither can complete until the other has
finished. So, the question for you is, are you doing anything else besides
the insert here? Are you using CFTRANSACTION or transactional logic within
your query/stored procedure also? On the other hand, for all I know, if the
hotspot issue gets bad enough, maybe that will return a deadlock error!
Your DBA should be able to help determine what's going on at the database
level, I hope.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
______________________________________________________________________
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
