RE: What is this SQL Error?
> But isn't it what's happening with the "hotspot" problem? - > the query is done, but the database still has the page locked. I seriously doubt that your problem is occurring because of one individual instance of your page running. However, if you have more than one person adding records at the same time, you could easily run into this. > BTW, Thanks bunches Dave, it's great to have a geekguru > available like this > ;) You're welcome! Can I get a free Lexis-Nexis account out of this? Just kidding. 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
RE: What is this SQL Error?
But isn't it what's happening with the "hotspot" problem? - the query is done, but the database still has the page locked. BTW, Thanks bunches Dave, it's great to have a geekguru available like this ;) -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 1:07 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: What is this SQL Error? > I thought about using cftry/cfcatch, but what if my second > attempt is still done before the table is again available... > I was wondering if ColdFusion had a "timer" function - such > as, "wait 300ms before continuing"? No, it doesn't, but I don't think that matters, since CF's processing of queries isn't asynchronous, to the best of my knowledge. That is, it won't continue processing after a CFQUERY tag until the database says "I'm all done". Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.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
RE: What is this SQL Error?
> I thought about using cftry/cfcatch, but what if my second > attempt is still done before the table is again available... > I was wondering if ColdFusion had a "timer" function - such > as, "wait 300ms before continuing"? No, it doesn't, but I don't think that matters, since CF's processing of queries isn't asynchronous, to the best of my knowledge. That is, it won't continue processing after a CFQUERY tag until the database says "I'm all done". Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.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
RE: What is this SQL Error?
I thought about using cftry/cfcatch, but what if my second attempt is still done before the table is again available... I was wondering if ColdFusion had a "timer" function - such as, "wait 300ms before continuing"? -Original Message- From: Hoag, Claudia (LNG) [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:50 PM To: CF-Talk Subject: RE: What is this SQL Error? Yes, but if it calls the database and doesn't wait for anything back, just goes ahead and calls it again... wouldn't it happen the same as when I call the queries - and the loop is faster than the database? -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:46 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: What is this SQL Error? > The DBA wants me to call a stored procedure, "wait" until the > stored procedure is done, then continue with the loop to call > it again... Is there a way to make sure the store procedure > is "done" before the code continues? I'm pretty sure that CF will handle this for you; CF won't call the next iteration of the loop until the first one has completed. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. 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
RE: What is this SQL Error?
> Yes, but if it calls the database and doesn't wait for > anything back, just goes ahead and calls it again... > wouldn't it happen the same as when I call the queries > - and the loop is faster than the database? It has to wait until the database server returns a message, before continuing. That message needn't be a recordset, but it does signal completion of the SQL batch. In most situations CF doesn't support asynchronous processing - starting something but not waiting around for it to finish. 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
RE: What is this SQL Error?
Yes, but if it calls the database and doesn't wait for anything back, just goes ahead and calls it again... wouldn't it happen the same as when I call the queries - and the loop is faster than the database? -Original Message- From: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:46 PM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Subject: RE: What is this SQL Error? > The DBA wants me to call a stored procedure, "wait" until the > stored procedure is done, then continue with the loop to call > it again... Is there a way to make sure the store procedure > is "done" before the code continues? I'm pretty sure that CF will handle this for you; CF won't call the next iteration of the loop until the first one has completed. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.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
RE: What is this SQL Error?
:) yes, I guess I can do that!
Thanks
-Original Message-
From: Andy Ewings [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 17, 2002 12:40 PM
To: CF-Talk
Subject: RE: What is this SQL Error?
I guess you could wait for a return code from the SP and test for it in your
code. Used to do this all the time with VB but never done it with CF
-Original Message-
From: Hoag, Claudia (LNG) [mailto:[EMAIL PROTECTED]]
Sent: 17 May 2002 17:37
To: CF-Talk
Subject: RE: What is this SQL Error?
The DBA wants me to call a stored procedure, "wait" until the stored
procedure is done, then continue with the loop to call it again... Is there
a way to make sure the store procedure is "done" before the code continues?
-Original Message-
From: Hoag, Claudia (LNG) [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 17, 2002 12:27 PM
To: CF-Talk
Subject: RE: What is this SQL Error?
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
insert into someTable (col1,col2)
values (#thisUser#,#thisSelectedItem#)
-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
__
Get the mailserver that powers this list at http://www.coolfusion.com
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
RE: What is this SQL Error?
> The DBA wants me to call a stored procedure, "wait" until the > stored procedure is done, then continue with the loop to call > it again... Is there a way to make sure the store procedure > is "done" before the code continues? I'm pretty sure that CF will handle this for you; CF won't call the next iteration of the loop until the first one has completed. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Get the mailserver that powers this list at http://www.coolfusion.com 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
RE: What is this SQL Error?
I guess you could wait for a return code from the SP and test for it in your
code. Used to do this all the time with VB but never done it with CF
-Original Message-
From: Hoag, Claudia (LNG) [mailto:[EMAIL PROTECTED]]
Sent: 17 May 2002 17:37
To: CF-Talk
Subject: RE: What is this SQL Error?
The DBA wants me to call a stored procedure, "wait" until the stored
procedure is done, then continue with the loop to call it again... Is there
a way to make sure the store procedure is "done" before the code continues?
-Original Message-
From: Hoag, Claudia (LNG) [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 17, 2002 12:27 PM
To: CF-Talk
Subject: RE: What is this SQL Error?
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
insert into someTable (col1,col2)
values (#thisUser#,#thisSelectedItem#)
-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
RE: What is this SQL Error?
> 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. Well, then, see if you're using a clustered index on your identity column, and find out from your DBA what your lock level really is for inserts, and as a stopgap preventative measure, you may want to do the following: 1. Use CFTRANSACTION around your insert block, if for no other reason than if the thing fails, you won't have some of the records added but not others. Alternatively, keep track of which records have been entered successfully on each loop iteration; this will be a tiny bit of extra coding, but won't have the overhead of CFTRANSACTION if you really don't need it otherwise. 2. Use CFTRY/CFCATCH to catch your deadlock errors, and retry the operation in the CFCATCH block. You may even want to nest another CFTRY within there, so that if the second attempt fails, you can provide an alternative, like prompting the user to try again. Then, it's time to actually fix the problem (note that the above measures are just stopgaps). You may need a faster, or more powerful, database server, you may need to improve the speed of your database connection, you may need to port this logic to stored procedures (along with the rest of your db logic), and so on. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 __ Get the mailserver that powers this list at http://www.coolfusion.com 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
RE: What is this SQL Error?
The DBA wants me to call a stored procedure, "wait" until the stored
procedure is done, then continue with the loop to call it again... Is there
a way to make sure the store procedure is "done" before the code continues?
-Original Message-
From: Hoag, Claudia (LNG) [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 17, 2002 12:27 PM
To: CF-Talk
Subject: RE: What is this SQL Error?
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
insert into someTable (col1,col2)
values (#thisUser#,#thisSelectedItem#)
-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
__
Get the mailserver that powers this list at http://www.coolfusion.com
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
RE: What is this SQL Error?
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
insert into someTable (col1,col2)
values (#thisUser#,#thisSelectedItem#)
-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
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
__
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.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
RE: What is this SQL Error?
If you're getting a deadlock error, chances are that it has to do with the
order that your queries are going. If you have one page with a transaction
that starts with an insert to Table A and then updates all the rows in Table
B while you have another page with a transaction that inserts into Table B
and then updates all the rows in Table A you can have a deadlock.
Example:
Page 1
BEGIN TRANSACTION
INSERT INTO TableA (field1,field2) VALUES ('value1', 'value2')
UPDATE TableB SET field1 = 5
COMMIT TRANSACTION
Page 2
BEGIN TRANSACTION
INSERT INTO TableB (field1,field2) VALUES ('value1', 'value2')
UPDATE TableA SET field1 = 5
COMMIT TRANSACTION
If both pages run at the same time, page 1 will lock TableA and page 2 will
lock TableB. Neither can let go of the lock until the transaction is
complete, but they can't get to other table they need until the other page
releases its lock.
The idea is to put all your tables into the same order so if page 1 works on
TableA and then TableB, then page 2 should work on TableA and then TableB as
well -- not in reverse order.
Does that help?
Ben Johnson
Hostworks, Inc.
__
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
RE: What is this SQL Error?
I'm trying to figure out if ColdFusion locks the whole table when running a
cfquery.
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. 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?
-Original Message- From: Houk, Gary
[<mailto:[EMAIL PROTECTED]>] Sent: Tuesday, April 16, 2002 3:06
PM To: CF-Talk Subject: What is this SQL Error? First time I've seen this
one...what does it mean? Diagnostics: ODBC Error Code = 40001 (Serialization
failure) [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process
ID 60) was deadlocked on {lock} resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.
__
Get the mailserver that powers this list at http://www.coolfusion.com
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
RE: What is this SQL Error?
are you sure you are using transaction statements.
Anthony Petruzzi
Webmaster
954-321-4703
[EMAIL PROTECTED]
http://www.sheriff.org
-Original Message-
From: Bob Imperial [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 16, 2002 4:11 PM
To: CF-Talk
Subject: RE: What is this SQL Error?
We've run into this from time to time, it occurs when two or more users thry
to access the data at exactly the same time even though SQL is suppose to
handle it. Just refreshing it seems to clear up the deadlock. I haven't
heard any other solutions for resolving the problem though. Let me know if
you do.
***
Bob Imperial
Software Development Team
Schoollink, Inc.
910-223-2116 ext. 108
***
"Linking Schools to The World .. One Classroom at a Time"
-Original Message-
From: Houk, Gary [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 16, 2002 3:06 PM
To: CF-Talk
Subject: What is this SQL Error?
First time I've seen this one...what does it mean?
Diagnostics: ODBC Error Code = 40001 (Serialization failure)
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID
60) was deadlocked on {lock} resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.
__
Get the mailserver that powers this list at http://www.coolfusion.com
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
RE: What is this SQL Error?
Thanks Bob...I'll let you know if I find a workaround.
- Gary
-Original Message-
From: Bob Imperial [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 16, 2002 4:11 PM
To: CF-Talk
Subject: RE: What is this SQL Error?
We've run into this from time to time, it occurs when two or more users
thry
to access the data at exactly the same time even though SQL is suppose
to
handle it. Just refreshing it seems to clear up the deadlock. I haven't
heard any other solutions for resolving the problem though. Let me know
if
you do.
***
Bob Imperial
Software Development Team
Schoollink, Inc.
910-223-2116 ext. 108
***
"Linking Schools to The World .. One Classroom at a Time"
-Original Message-
From: Houk, Gary [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 16, 2002 3:06 PM
To: CF-Talk
Subject: What is this SQL Error?
First time I've seen this one...what does it mean?
Diagnostics: ODBC Error Code = 40001 (Serialization failure)
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID
60) was deadlocked on {lock} resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.
__
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
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
RE: What is this SQL Error?
We've run into this from time to time, it occurs when two or more users thry
to access the data at exactly the same time even though SQL is suppose to
handle it. Just refreshing it seems to clear up the deadlock. I haven't
heard any other solutions for resolving the problem though. Let me know if
you do.
***
Bob Imperial
Software Development Team
Schoollink, Inc.
910-223-2116 ext. 108
***
"Linking Schools to The World .. One Classroom at a Time"
-Original Message-
From: Houk, Gary [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 16, 2002 3:06 PM
To: CF-Talk
Subject: What is this SQL Error?
First time I've seen this one...what does it mean?
Diagnostics: ODBC Error Code = 40001 (Serialization failure)
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID
60) was deadlocked on {lock} resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.
__
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.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
What is this SQL Error?
First time I've seen this one...what does it mean?
Diagnostics: ODBC Error Code = 40001 (Serialization failure)
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID
60) was deadlocked on {lock} resources with another process and has been
chosen as the deadlock victim. Rerun the transaction.
__
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
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
