Hi All,
Further to my previous long-winded question about a situation in which
we appear to be mysteriously losing data from our mssql2k server.
We discovered an update statement, in the stored procedure we believe
is at fault, after which no error check was being performed.
Under certain conditions, this update is fired against the same record
in the same table as the immediately preceding update statement within
the transaction. We are now suspecting that under some circumstances,
these two updates get into a locking conflict that is eventually
forcing the transaction to be rolled back.
However, I'm still left with three questions.
1) Where an update in a transaction gets locked, and an error isn't
tested immediately afterwards (ie no 'IF @@Error<>0' test is made),
would the transaction proceed as normal?
2) Most critically, would statements in the stored procedure that
appear after the COMMIT TRAN statement also be executed, even if an
unresolved lock existed within the transaction?
3) Assuming that (2) does happen, would a SELECT made on another
connection with a 'WITH(NOLOCK)' locking hint be able to see the
changes made in the locked transaction even if the server is set to
READ COMMITTED, and the SELECT takes place some time after the COMMIT
TRAN is issued? More to the point, given (2), how long would the
locked transaction survive before being rolled back after the COMMIT
TRAN has been issued? Is it possible that the COMMIT TRAN takes place,
the transaction is flagged for potential rollback while a lock
resolution is attempted, the stored procedure exists as though
everything was fine, a subsequent SELECT (ie performed as one of the
next operations in the same application) using WITH(NOLOCK) 'sees' the
changes made by the transaction, reinforcing the impression that the
transaction succeeded, and then at some point thereafter the lock is
determined to be unresolvable and the transaction is rolled back,
making it seem as though the data disappeared, even though it had been
SELECTable via a different connection to the server?
Thanks, by the way, to Simon and Erland for your advice on my previous
questions about this problem.
Much warmth,
M Wells 1 2123
M Wells (pl************ **@gmail.com) writes: We discovered an update statement, in the stored procedure we believe is at fault, after which no error check was being performed.
Short recap: when an error happens in a stored prcoedures, one three
things can happen depending on the error:
1) The statement is terminated, and @@error is set. The transaction is
not rolled back.
2) The execution of the stored procedure is terminated and @@error is
set. No rollback occurs.
3) The entire batch is aborted. Transactions is rolled back.
More details on http://www.sommarskog.se/error-handling-I.html.
Under certain conditions, this update is fired against the same record in the same table as the immediately preceding update statement within the transaction. We are now suspecting that under some circumstances, these two updates get into a locking conflict that is eventually forcing the transaction to be rolled back.
No, unless you engage in wierd arrangments with linked servers that
results in loopback connections, two statements in the same stored
procedure cannot get in conflicts with each other. What happens if
you have:
BEGIN TRANSACTION
UPDATE tbl1 ...
UPDATE tbl2 ....
UPDATE tbl3 ....
COMMIT TRANSACTION
and the UPDATE on tbl2 fails with a statement-terminating error (for
instance PK violation, NOT NULL violation), and there is no error-handling,
is that the effect of the updates on tbl1 and tbl3 will be persisted
when the transaction is committed.
1) Where an update in a transaction gets locked, and an error isn't tested immediately afterwards (ie no 'IF @@Error<>0' test is made), would the transaction proceed as normal?
Yes. (But you can't really say that an "update gets locked".)
2) Most critically, would statements in the stored procedure that appear after the COMMIT TRAN statement also be executed, even if an unresolved lock existed within the transaction?
Yes. (Save again that there are no "unresolved locks". All locks are
released as the transaction commits.)
3) Assuming that (2) does happen, would a SELECT made on another connection with a 'WITH(NOLOCK)' locking hint be able to see the changes made in the locked transaction even if the server is set to READ COMMITTED, and the SELECT takes place some time after the COMMIT TRAN is issued?
If you read data WITH (NOLOCK), you may be reading dirty data. It doesn't
matter what settings you have elsewhere. Query hints wins over anything
else. If you confirmation page reads with NOLOCK, you absolutely must
change that. Else you are not confirming data.
More to the point, given (2), how long would the locked transaction survive before being rolled back after the COMMIT TRAN has been issued?
Assuming that there are no nested transactions, therre is no transaction
and no locks around after COMMIT.
But if the COMMIT for some reason is not executed, for instance because
the query is cancelled, or the error terminates the stored procedure,
so that the transaction lives on, then the transaction will continue to
live until the process disconnects, at which points a fat rollback will
set in. (Actually with connection pooling it's takes a little while more
before it happens.)
Is it possible that the COMMIT TRAN takes place, the transaction is flagged for potential rollback while a lock resolution is attempted, the stored procedure exists as though everything was fine, a subsequent SELECT (ie performed as one of the next operations in the same application) using WITH(NOLOCK) 'sees' the changes made by the transaction, reinforcing the impression that the transaction succeeded, and then at some point thereafter the lock is determined to be unresolvable and the transaction is rolled back,
Again, there is no lock to resolve, nor that can be deemd to be
unresolved. I'm sorry to nag this point.
Judging from your description, you have two problems: 1) You read
with NOLOCK. 2) You fail to commit your transaction in some situations.
The missing @@error check is probably not the main problem about the false
confirmations. More likely you fail to handle procedure-aborting errors or
command timeouts, since these are the cases where you most easily can
go wrong about this.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: mike420 |
last post by:
In the context of LATEX, some Pythonista asked what the big
successes of Lisp were. I think there were at least three *big*
successes.
a. orbitz.com web site uses Lisp for algorithms, etc.
b. Yahoo store was originally written in Lisp.
c. Emacs
The issues with these will probably come up, so I might as well
mention them myself (which...
|
by: M Wells |
last post by:
Hi All,
I'm trying to track down a mysterious problem we're experiencing in
which updates and inserts to tables in our mssql2k server appear to be
'disappearing.'
To explain our situation:
We have a web page (written in ASP, if that's relevant) on which we
accept enrollment information.
|
by: sparks |
last post by:
We get more and more data done in excel and then they want it imported
into access.
The data is just stupid....values of 1 to 5 we get a lot of 0's ok
that alright but 1-jan ?
we get colums that are formatted for number and then half way down
they are changed to text.
OR the famous ok now everything in red is ---- and everything in blue...
|
by: curious2007 |
last post by:
Hello,
The following function gives me a warning:
template <class V, class I, class S>
I Array<V, I, S>::MaxIndex() const
{ // Return the maximum index
return m_start+Size()-1;
}
|
by: Charlie of Bolton |
last post by:
Hi,
I have to below script,
and everything seems to work except
that if I entered the Primary IP and Secondaries IP, with such
number; 10.100.1.11 or 10.10.10.245 the last numbers of my IP are missing
in my logs file (c:/tmp/myprimarylogs.xls) Here is the result, you see, '10.100.1.1 should be 10.100.1.11
and 10.10.10.2 should be...
| |
by: elliot.li.tech |
last post by:
Hello everyone,
I'm migrating a C++ program from 32-bit systems to 64-bit systems. The
old programmers used some annoying assignments like assigning "long"
to "int", which may lead to data loss.
Is it possible to let the compiler (specifically, GCC) spew some
warnings on this kind of implicit type casts?
Or you suggest use some other...
|
by: PC |
last post by:
I have a DB2 mainframe linked server setup in MSSQL2K using the DB2 Connect
client software. If the connection to DB2 hangs is there an easy way to
reset it, short of bouncing the server? Can it be reset with an SQL
statement?
|
by: kenneth6 |
last post by:
int a=10;
int b=3;
float c;
c=a/b;
cout << c << endl;
if, in my program, the int type for a and b is a must (according to the return), performing operation on a & b results in float type of c.
|
by: Haas C |
last post by:
Hi all,
I have a table with two columns, labeled Year and Loss. In the Year
field, I have the numbers 1 to 10,000, each which can or cannot
repeat. In the Loss column, i have numbers corresponding to the
Years...for example:
Year, Loss
1, 568
1, 621
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |