Hi!
I've got a problem that I do not really understand,
and I am of course wondering if anyone could
enlighten me.

I have a system, ie several processes,
using ODBC to access a sql-server 2008 database.

At first, I was running with default transaction
isolation mode, but I ran into problems.

I would occasionally get a deadlock.
one process doing SELECT on a join of 4 tables,
and other processes were updating at least 3 of those
4 tables. probably the same records involved in the select.
This lead to sql-server rolling back the transaction
of the process doing select, with a dead-lock msg.

Not good, since the (big legacy) application
does not handle this situation on selects.
It counts on that a select is ALWAYS ok to do
(written for Oracle, this is a port to sql-server)

OK, looking at hinting selects with nolock, but
I don't want dirty reads to happen.
(industrial automation system, big things are moving,
and they cannot move just because I get a dirty read)

So, isolation level snapshot looks really good.
However now I get this situation:

1, Process A sets isolation level to snapshot
2, Process A starts new transaction by setting autocommit to off
3, Process A inserts a record in db
4, process A commits (sqlEndTran(commit))
5, process A signals via ipc to process B, sending the key of this
record
6, Process B sets isolation level to snapshot
7, Process B starts new transaction by setting autocommit to off
8, Process B selects on the table, with the given key as where clause
9, Process B get no hit. record is not found

Looking with sqlcmd in db, the record IS there

restart PROCESS B, it looks for ALL records marked 'process B'
and it finds the record.

OK, timing issue you say.
So, between 4 and 5, I sleep 20 seconds. Still no go

Ok remove the sleep, and skip step 6,
and the record is found.

I thought that setting the snapshot level was
done when the statement was issued.
Here, it looks like process B creates the snapshot
BEFORE process A commits, thus missing the insert.
Correct ? (I hope not)

So, what can I do to
* avoid dirty reads
* avoid locks when doing selects
* still see stuff COMMITTED by others
?
/Björn

björn lundin (b.f.lundin (AT) gmail (DOT) com) writes:
Note that in SQL Server there is no such thing as "SET AUTOCOMMIT OFF".
There is only BEGIN TRANSACTION. It sounds from your description that
B already has a transaction in progress, possibly orphaned.

Rather than using true snapshot isolation, you could consider setting
the database in READ_COMMITTED_SNAPSHOT. In this case, the default
isolation level READ COMMITTED will be implemented through the snaphot.
In this, you don't need to start to explicit transactions to use the
snapshot and avoid that readers and writers block each other.

Particularly, in this situation, even if B has an orphaned transaction,
it will still see the row inserted by A, because it reads committed
data.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On 2 Juni, 23:49, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Ok. I guess it is ODBC speak then, I got the impression that it is the
nomenclature to use
for the database itself from
<http://msdn.microsoft.com/en-us/library/ms131281.aspx>
where it says, among other things :
<quote>
Autocommit mode is the default transaction mode for ODBC. When a
connection is made, it is in autocommit mode until SQLSetConnectAttr
is called to switch to manual-commit mode by setting autocommit mode
off. When an application turns autocommit off, the next statement sent
to the database starts a transaction. The transaction then remains in
effect until the application calls SQLEndTran with either the
SQL_COMMIT or SQL_ROLLBACK options. The command sent to the database
after SQLEndTran starts the next transaction.

If an application switches from manual-commit to autocommit mode, the
driver commits any transactions currently open on the connection.

ODBC applications should not use Transact-SQL transaction statements
such as BEGIN TRANSACTION, COMMIT TRANSACTION, or ROLLBACK TRANSACTION
because this can cause indeterminate behavior in the driver. An ODBC
application should run in autocommit mode and not use any transaction
management functions or statements, or run in manual-commit mode and
use the ODBC SQLEndTran function to either commit or roll back
transactions..
</quote>

Good, this is with other words NOT the expected behavior then.
Probably means error in my code then.
Is there a way to query the db or ODBC-driver whether I'm already in a
transaction or not?

As I understand the quoted text above, there is no Start_Transaction.
The first statement after a commit/rollback actually starts the
transaction.
So i tried to _really_ start a transaction by query for dummy info,
like 'select 'a' from sys.databases' but that did not improve the
situation.

The application does all its db-work within a transaction, even
selects, ie
this gives the picture:

Transaction_Loop : loop
begin
Start_Transaction(T)
Prepare(S1,"SELECT cola,colb,colc FROM foo WHERE bar=:bar");
Prepare(S2,"UPDATE foo2 SET colb = 10 WHERE colb = :B);
Set(S1,"bar",25);
Open_Cursor(S1)
Cursor_Loop loop
Fetch(S1,End_Of_Set)
exit Cursor_Loop when End_Of_Set;
Get(S1,"colb",B);
if is_odd(B) then
Set(S2,"colb",B);
Execute(S2) -- <== may raise Transaction_Conflict
end if;
end loop Cursor_Loop;
Close_Cursor(S1);
Commit(T) -- A successful commit means leave the loop
exit Transaction_Loop;
exception
when Transaction_Conflict => -- start all over again
Close_Cursor(S1);
Rollback(T);
end Transaction_Loop;

Hmm, I read <http://decipherinfosys.wordpress.com/2008/04/18/new-
isolation-level-options-in-sql-server-2005/>
where it states
<quote>
A new implementation of the READ COMMITTED Isolation: This is the
READ_COMMITTED_SNAPSHOT database option. When this option is set, this
provides statement level read consistency and we will see this using
some examples in the post.
</quote>

That implicates that 2 consecutive selects with the same conditions
may return different resultsets,
depending on insert/delete/updates made by others between the selects.
That made me look into the isolation level snapshot.
However, your post made me think (harder/more) about what we have
today,
and I think that this is actually the case of today.
I'll look into it.
This READ_COMMITTED_SNAPSHOT, when I set it, does it apply to all
coming sessions in the database?
I mean new connections, do the get this setting by default, or do they
have to set it by themselves?
- per session or per transaction?

Well, I do start transactions always anyway, as indicated above, but
I get the idea.

Now this is something I'd like to fix.
How do I track down orphaned transactions.
Having them points to error in my code, and that, I do not want...
When i look in the management Studio, looking at database report 'All
transactions'
I get the reply that there are no current transactions...
Actually, all reports involving transactions says that (All blocking
Transaction, Top transactions by age...)

/many thanks
Björn

björn lundin (b.f.lundin (AT) gmail (DOT) com) writes:
"SELECT @@trancount" returns your current level of transaction nested. Any
number >= 1 means that you have a transaction in progress.

There is a command, SET IMPLICIT_TRANSACTION ON. When this setting is in
effect, the first command that performs an update will start a transaction.
This setting is off by default, which is different from other product
where implicit transactions is the norm. Implicit transactions is also
what ANSI mandates.

Personally, I don't like what is being is said in the ODBC manual. I much
rather submit BEGIN/COMMIT/ROLLBACK TRANSACTION commands myself than
have the API to perform things behind my back.

I don't know exactly what this code is doing, but it seems to me that
it could be implemented with a single UPDATE statement for better
performance and less risk for deadlocks.

Yes, this is correct. But if you are using READ COMMITTED already,
there is no diference.

All sessions that use the READ COMMITTED isolation level.

Once your application exists, all these transactions will of course
be rolled back.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On 3 Juni, 22:12, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Ok, that is good to know


This is, as i understand it, the underlaying mechanism for
the turning auotcommit on/off in the ODBC API.

I agree. Would be much clearer if I could execute those statements and
expect it to work.

<Ćhunk of code snipped/>

Well yes. But I put the code there to illustrate some requirements
that I have
* MARS, or multiple active record sets. It is used a lot.
* The statements are prepared, and late on bound.

That may or may not be relevant to know or someone answering my
questions.
I'd rather put in some extra info, than leaving it out.
Of course, since you were looking into the logic of the statement,
I was not very clear with the purpose of putting it there.


Ok.


Hmm, yes, I probably looked to late.

I will try swith to READ_COMMITTED_SNAPSHOT next week,
but for the sake of clearity I'd like to say that it now seems to
work. (with ISOLATION SNAPSHOT)

Turns out that the following sequence describes my INTENTIONS with the
code

1, Process A sets isolation level to snapshot
2, Process A starts new transaction by setting autocommit to off
3, Process A inserts a record in db
4, process A commits (sqlEndTran(commit))
5, process A signals via ipc to process B, sending the key of this
record
6, Process B sets isolation level to snapshot
7, Process B starts new transaction by setting autocommit to off
8, Process B selects on the table, with the given key as where clause
9, Process B get no hit. record is not found

But in reality, there were 3 more steps

-1, Process A sets isolation level to snapshot
0 , Process A starts new transaction by setting autocommit to off
5.5 process A commits (sqlEndTran(commit))

So, the service I used did what I described as Process A from the
start.
However, that service was running in the context of another
transaction :-(

This of course meant that signaling via IPC was too fast, and process
B did not
see the record (as it should not)

However, it all worked in READ_COMMITTED mode, which implies on of two
things
* Process B got a dirty read
* The Snapshot handling is so much slower that Process B was too quick
reading,
so Process A had not yet done its final commit. And when not using
snapshot,
process A send the IPC, and committed, BEFORE Process A got to
read.

I prefer to believe its the second option, but will keep my eyes on it
until I know for sure.
Erland, thanks again for the comments.

/Björn

björn lundin (b.f.lundin (AT) gmail (DOT) com) writes:
Ugh! I can't say that MARS is anything I recommend. When used correctly, it
is not likely to be harmful, but it can lure you do things you should not
do.

Still a stored procedure that performs all the business logic will
more efficient that sending data forth and back between server and client.

So it's not process B that has an uncommitted transaction as I assumed,
but process A. The effect is the same.

Not really. But without snapshot, B was blocked until A had performed its
final commit, whereas with snapshot B just reads the old data. No scheme is
perfect, and with snapshot - either true snapshot isolation or
read_commited_snapshot - the risk is that you read stale data.

If you have situtaions where a process may be signaled about new data that
has not yet been committed, that process cannot use snapshot isolation to
read the data. This is the one situation where enabling RCSI can wreak
havoc on an application. You can use the hint (READCOMMITTEDLOCK) to
prevent this from happening.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

On 5 Juni, 14:35, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
Well, the code is there. ~5000 files that works well today
with Oracle. It is not my choice to write code that depends on
MARS functionality, it is already present. But
I was a bit surprised when I found out that MARS was added as
late as 2005.

Yes, but that is no option.
This system went from VAX/VMS on Mimer, RDB, then to Oracle,
via AIX, and in the late 90's to WinNT, then up to w2k8.
The VAX branch is dead, but now we also got Linux, ie
We got Aix, Win and Linux. Both Unices do Oracle only,
and win does Oracle, and soon sql-server.
I forsee Postgresql as an option within 5 years for all 3 os:es.
So, anything more than a simple trigger for traceability
is no option.
(And that was no fun. The trigger functionality is very different
between Oracle and Sql-server,
especially the lack of 'For each row' firing scheme found in Oracle -
which OF COURSE is used.
The concept of 'inserted/deleted' tables are fine, but different...
This is solved, but was a challenge)

But of course. Thanks for enlightening me.

That situation is what I had here. And in this system, it is regarded
as a bug.
Never ever signal before commit.

Process A here is a test-process written
just a couple of days ago, to aid in testing this db-port.
It i ironic that the tools to find bugs with, contains bugs ;-)
But it helped gaining better understanding...

Thanks
/Björn