Even SELECT statements lock resources. A SELECT statement will
place a read lock on the resources it is reading so they cannot be changed
in the middle of the read. INSERT and UPDATE statements will place an
exclusive lock on their resources while they write to the table(s). How
big the lock is (e.g., row, page, or table) depends on how much data your
statement affects, how large each row is (e.g., whether it consists of
more than one 8K page), and other factors like indexes affected, triggers
that will fire, etc.
With three joins, your SELECT has probably placed a read-only lock
on a table that another process is trying to UPDATE or INSERT a row into.
If the other process was an INSERT, then your lock has spilled over into a
page or table lock (usually due to large amounts of data being read).
First, keep in mind that <cflock> only locks ColdFusion access to
resources. It will not affect the database and cannot throw an ODBC or
OLEDB error. The <cftransaction> tag will help you make sure that your
SELECT is carried out as an atomic operation with other statements, but
also will not prevent deadlocks in and of itself. It will, however, make
sure that you don't get halfway through a series of database operations
and die due to a deadlock, leaving your tables half-baked.
In SQL Server, you can get around this by adjusting the isolation
level of your query statements with the SET TRANSACTION ISOLATION LEVEL statement, but
this has important implications. From the Microsoft SQL
Server Books Online:
SQL-92 defines the following isolation levels, all of which are supported
by SQL Server:
Read uncommitted (the lowest level where transactions are isolated only
enough to ensure that physically corrupt data is not read).
Read committed (SQL Server default level).
Repeatable read.
Serializable (the highest level, where transactions are completely
isolated from one another).
If transactions are run at an isolation level of serializable, any
concurrent overlapping transactions are guaranteed to be serializable.
These isolation levels allow different types of behavior.
Isolation level
Dirty read
Nonrepeatable read
Phantom
Read uncommitted
Yes
Yes
Yes
Read committed
No
Yes
Yes
Repeatable read
No
No
Yes
Serializable
No
No
No
Transactions must be run at an isolation level of repeatable read or
higher to prevent lost updates that can occur when two transactions each
retrieve the same row, and then later update the row based on the
originally retrieved values. If the two transactions update rows using a
single UPDATE statement and do not base the update on the previously
retrieved values, lost updates cannot occur at the default isolation level
of read committed.
Profanity is the one language all programmers know best.
-- Troutman's Second Programming Postulate
"Clark Baker" <[EMAIL PROTECTED]>
01/24/2003 10:18 AM
Please respond to sql
To: SQL <[EMAIL PROTECTED]>
cc:
Subject: Transaction deadlock error
I've been working with CF and SQL Server for several years and recently
began seeing an error I've never seen before. It occurs very rarely but
it's
bugging me:
"OLEDB Error Code = 1205
Your transaction (process ID #100) was deadlocked with another process and
has been chosen as the deadlock victim. Rerun your transaction."
The error is occurring on a pretty simple SELECT query (3 joins) and there
is no <CFTRANSACTION> or <CFLOCK> used in the template. I've looked around
and can't find anything on this. Has anyone seen this before in this
situation? There isn't that much traffic so I don't know what's going on.
Thanks,
Clark
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6
Get the mailserver that powers this list at http://www.coolfusion.com
