Transactions in SQL Server

dc · 2013-03-19 14:29:01

Hi,
there were some issues regarding working with transactions in SQL Server and OleDB drivers raised in this topic by p.s.t.: http://synopse.info/forum/viewtopic.php?id=1131 but since there are no responses I would like to summarize problems with transactions in SQL Server.

1. sometimes, and I honestly don't know why MainConnection.StartTransaction raises exception saying

A request to establish a connection with the transaction manager was denied - A request to establish a connection with the transaction manager was denied

While using ODBC drivers you get a little more meaningful error:

A request to establish a connection with the transaction manager was denied - the transaction could not be started because it is used more than one ODBC connection.

2. When you use at least two Execute() statements inside transaction you get an error

Cannot create new connection because in manual or distributed transaction mode.

Since I know this error from working with devart drivers I was able to quickly fix it. Instead of calling:

dbConnection.MainConnection.StartTransaction;
dbConnection.Execute('select 1', []);
dbConnection.Execute('select 2', []); // - here you get error

i used this:

function Exec(query: string; params: array of const; const expectResults: Boolean = true): TSQLDBStatement;
begin
  result := dbConnection.NewThreadSafeStatement;
  result.Prepare(query, expectResults);
  result.Bind(params);
  result.ExecutePrepared;
end;
...
dbConnection.MainConnection.StartTransaction;
Exec('select 1', []);
Exec('select 2', []);

It looks like there is a bug in NewStatementPrepared that doesn't close statements which return results but user code doesn't explicitly read/close them. Maybe it should not be in base class and be overridden by ancestors just as NewStatement is?

3. Sometimes, and again I don't know why StartTransaction call passes OK, however call to commit gives error

Invalid TOleDBConnection.Commit call.

And this (I suppose) is the case of p.s.t post.

dc

ab · 2013-03-19 16:19:46

Retrieve the latest unstable source code from http://synopse.info/fossil/wiki?name=Get+the+source
It has several fixes about transactions.
(for your point 3)

If your transaction is executed remotely via a HTTP client for instance, the Commit probably won't be executed in the same thread than the TransactionBegin.
Each request is run in the HTTP thread which processes the incoming RESTful command - and it changes at every call.
This is why you have such a problem.

Two working implementation patterns:
- Use an interface based service, in which all the DB process is done within a single method - so here TransactionBegin / Commit/Rollback will be in the same thread, therefore connection;
- Use an interface based service, in sicClientDriven mode, in which the options is to be executed in the main thread - SetOptions([],[optExecInMainThread]): the call TransactionBegin/Commit/Rollback within the expected methods.

Executing the DB commands in a main thread may help to reduce also the server CPU and memory load, since it will share one single connection.

Perhaps a global option, at SynDB level, should be made available in order to force execution of a DB with the main thread.

dc · 2013-03-19 16:47:54

Thank you for reply, I can't check your fixes right now but will do it in the morning. As far as threads are concerned, it's not a case here since I've tested transactions in a simple application in main thread.

dc

ab · 2013-03-19 21:09:06

Btw, your code does not release the statements.
:-)

dc · 2013-03-19 23:44:03

ab wrote:

Btw, your code does not release the statements.
:-)

Yeah I know... in my defense I don't release statements in both approaches and it gives errors only when using dbConnection.Execute (or as I suppose NewStatementPrepared).

dc

ab · 2013-03-20 06:09:54

What you are naming a dbConnection is not... a connection I suppose.
This is a TSQLDBConnectionProperties instance.
So you are mixing some commands on the MainConnection and some on the thread safe pool.

1. Use a single TSQLDBConnection instance for all your requests.
2. Be aware that calling Execute() as you do return a ISQLDBRows instance, which should be released on purpose.

dc · 2013-03-20 10:40:29

ab wrote:

What you are naming a dbConnection is not... a connection I suppose.
This is a TSQLDBConnectionProperties instance.

I am aware of that.

ab wrote:

So you are mixing some commands on the MainConnection and some on the thread safe pool.
1. Use a single TSQLDBConnection instance for all your requests.
2. Be aware that calling Execute() as you do return a ISQLDBRows instance, which should be released on purpose.

Hmmm...
Ok, I was just ctrl+clicking through code and it made me think that dbConnProp.MainConnection is handled by TSQLDBConnectionProperties.GetMainConnection method while in fact it's TSQLDBConnectionPropertiesThreadSafe.GetMainConnection. So basically i was wrong assuming that there is only a single connection used. Now when it all become clear and I feel quite embarrassed...

However when I use dbConnProp.ForceOnlyOneSharedConnection := true it should work properly (with one connection) shoudn't it?

And two more things:
1. Do you plan to add support for named transactions (using ITransactionOptions.SetOptions)? It would be really helpful for logging/debugging purposes
2. Is it possible to add support for nested transactions for SQL Server? I know OleDB drivers for SQL Server don't support it natively but SQL Server does support it. Now when you try to use conn.StartTransaction twice you get an Run-time error '-2147168237 (8004d013)': Only one transaction can be active on this session.

However I think you can "unlock" nested transactions by doing this:
- use "SET XACT_ABORT ON" as soon as possible (for example just after creating connection and connecting do SQL Server)
- when calling StartTransaction you have to check if fTransactionCount > 0. If it is instead of trying to do usual stuff:

fTransaction.StartTransaction(ISOLATIONLEVEL_READCOMMITTED,0,nil,nil)

you should just execute "begin transaction" statement and increase fTransactionCount

Similar approach should be used for commit and rollback. Of course this should not be default functinality and accessible only by using some settings like UseNestedTransactions := true;

What do you think?

dc

ab · 2013-03-20 10:50:37

ForceOnlyOneSharedConnection will not work in multi-thread for MSSQL, since OleDB expect one connection per thread.

But it will work in your case, I suspect.

Named and nested transactions are specific to some engines, as you stated.
The SynDB units philosophy was KISS, i.e. keep it as simple as possible, even if all features are not available.

To be honnest, SynDB purpose was not to be another DB.pas/TDataSet library - you should better use UniDAC/AnyDAC/FireDAC/ZeosLib for this purpose.
The fact that it performs very well in some cases (e.g. direct Oracle connection speed) is due to the fact that it by-passes the DB.pas/TDataSet design - but it was not the main point, just a consequence.
SynDB classes were designed to work with our mORMot framework mainly, in which transactions should be as small as possible, and used with interface-based services: only SQlite3 direct backed transaction process is fully supported. Due to multi-thread design, you may encounter problems with external DBs, e.g. with OleDB connections (due to diverse threading expectations).

So I'm not sure it would be worth it to include those new features, unless you find some way to keep the KISS design, and provide accurate regression tests.
Complex transaction process, or even stored procedure full supports may be implemented at the SynOleDB.pas unit level, as a MS SQL specificity. But since OleDB is deprecated, and ODBC does not support all features, won't it be a dead-end?

dc · 2013-03-20 12:48:51

I understand your points about KISS strategy and agree with them, so maybe it's better to implement nested transactions in user code. On the other hand naming transactions doesn't seem to be in conflict with KISS principles and it could be easily implemented in TSQLDBConnection.StartTransaction.

As you said

ab wrote:

But since OleDB is deprecated, and ODBC does not support all features, won't it be a dead-end?

As Microsoft states in their roadmap from January 2013 (http://msdn.microsoft.com/en-us/library/ms810810.aspx):

- OleDB will be supported only up to SQLServer 2012 (but corporations are usually slowly adopting, so I suspect that version 2012 will be still in use for next 5-10 years, as I know some big companies that are slowly moving from version 2005 to 2008 nowadays)
- ODBC will be supported "better" from now on - that's good because now it's not as good as OleDB

From user point of view it would be probably better to switch to ODBC for future versions of code, however I would expect OleDB and ODBC in mORMot to behave identically, so when I change connection from OleDB to ODBC for the same database it would just work. What I mean here is inconsistent behavior of:
- datetime
- bigint
- stored procedure parameters and result codes http://synopse.info/forum/viewtopic.php?pid=7056#p7056
and so on...

dc

ab · 2013-03-20 12:59:15

About DateTime you have to use DateToSQL() / DateTimeToSQL() functions when transmitting such a parameter, as stated by the documentation. It should work on all targets.
About BigInt, what is the exact problem?

Stored procedures are a bit out of scope of the ORM, IMHO.
They are available for convenience, not fully tested.
So any patch to fix is welcome.

dc · 2013-03-21 13:55:03

ab wrote:

About DateTime you have to use DateToSQL() / DateTimeToSQL() functions when transmitting such a parameter, as stated by the documentation. It should work on all targets.
About BigInt, what is the exact problem?
Stored procedures are a bit out of scope of the ORM, IMHO.
They are available for convenience, not fully tested.
So any patch to fix is welcome.

As for bigint it seems there is no problem, there was a problem with devart drivers and SQL Server 2005 (you had to cast it to string for values > maxint). With mORMot and SQL 2008 it's working correctly.

There is however problem with DateTimeToSQL() function- it converts to ISO 8601 and therefore looses millisecond part of time. When you pass TDateTime directly it behaves differently depending on Bind*() method used. Take a look at this example:

  dt := EncodeDateTime(2013, 03, 21, 13, 20, 21, 234);
  stmt := dbConn.NewStatementPrepared('insert into dummy_table (col_datetime) values(?)', false, true);
  stmt.BindDateTime(1, dt);
  stmt.ExecutePrepared;

  stmt := dbConn.NewStatementPrepared('insert into dummy_table (col_datetime) values(?)', false, true);
  stmt.Bind([dt]);
  stmt.ExecutePrepared;

you will have two wrong dates in database:
from first insert: 2013-03-21 13:20:21.000 (because of DateTime to ISO conversion)
and from second: 2013-03-23 13:20:21.233 (1 ms difference)

Of course when using ODBC you don't ever get milliseconds part...

As for stored procedures I'll gladly share my fixes when I have them

regards,
dc

ab · 2013-03-21 15:00:18

Yes, internally the SynDB classes implementation does not expect to handle milliseconds.

It will perhaps depend on the backend DB, but most of the engines will loose the data, e.g. in BATCH mode.

Date/Time is implemented as ISO 8601 without milliseconds in the ORM kernel.

This is a limitation.

If you want milliseconds, use a TDateTime Delphi value, and store it as a DOUBLE, or use a milli second count and store it as an INTEGER.
But you will miss the DB native date/time functions, unless you convert the data within the SQL statement.

ab · 2013-03-22 08:20:58

I've added a feature request to handle all kind of SQLite3 date/time, up to the millisecond resolution.
See http://synopse.info/fossil/info/b11df3a9f4

1.2 Date and Time Datatype
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
- TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
- REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
- INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
http://www.sqlite.org/datatype3.html#datetime

dc · 2013-03-22 08:46:42

Thank you, it's really good news for me

ab · 2013-03-22 09:25:24

What do you think of the implementation proposals in the ticket?

dc · 2013-03-22 13:23:35

I don't really care how it will be done at SQLite3 or mORMot internal level as far as I can acomplish my goal, which is correctly storing Delphi's TDateTime values into appropriate fields in DB. There are many databases and many datetime-like types around. In SQL Server alone you have:

- date - only date part, so no time issues here and ISO is fine
- datetime - old type rounded to increments of .000, .003, or .007 seconds (3.33 milliseconds)
- datetime2 - new type since SQL 2008 - accuracy of 100 nanoseconds.
- datetimeoffset - same as datetime2 but with additional time zone offset.
- smalldatetime - accuracy of 1 minute (without seconds part)
- time - accuracy of 100 nanoseconds.

Apart from datetime type all other types fully cover Delphi's TDateTime or TTime types.

There is however a problem with using string literals as date and time values (http://msdn.microsoft.com/en-us/library … 05%29.aspx):

SQL Server might interpret a date value in a string literal format, input by an application or user for storage or to a date function, as different dates. The interpretation depends on the combination of string literal format, data type, and runtime SET DATEFORMAT, SET LANGUAGE and default language option settings.
...
The ISO 8601 formats, '1998-02-23T14:23:05' and '1998-02-23T14:23:05-08:00' are the only formats that are an international standard. They are not DATEFORMAT or default login language dependent and are multilanguage.

Format of ISO 8601 with milliseconds part (as I assume you want to use) is language dependent, so it will lead to problems when using old datetime type instead of new datetime2:

DECLARE 
	@today2 datetime2,
	@today datetime

set LANGUAGE French -- you can try Italian or Polish too
set @today2 = '2013-03-22 11:12:13.456' -- this is OK
begin try
	set @today = '2013-03-22 11:12:13.456' -- this will raise error
end try
begin catch
	set @today = @today2 -- this is OK
end catch
select @today2 as 'asDateTime2', @today as 'asDateTime', DATENAME(month, @Today) AS 'Month Name'

SET LANGUAGE us_english
set @today2 = '2013-03-22 11:12:13.456' -- this is OK
set @today = '2013-03-22 11:12:13.456' -- this is OK
select @today2 as 'asDateTime2', @today as 'asDateTime', DATENAME(month, @Today) AS 'Month Name'

I would not use string literals for queries' parameters. Binding native TDateTime type or maybe special type (like DateTimeToSQLTimeStamp from Data.SqlTimSt.pas) would be better?

I hope you find above info helpful.

dc

ab · 2013-03-22 14:06:00

Therefore, I guess that using a plain DOUBLE floating point value to store the TDateTime value is nice and easy, and will work with any external DB.

dc · 2014-05-20 11:03:50

dc wrote:

1. sometimes, and I honestly don't know why MainConnection.StartTransaction raises exception saying
A request to establish a connection with the transaction manager was denied - A request to establish a connection with the transaction manager was denied
While using ODBC drivers you get a little more meaningful error:
A request to establish a connection with the transaction manager was denied - the transaction could not be started because it is used more than one ODBC connection.
....
dc

I have found a way to reproduce this error and thought I leave a comment here for those struggling with it. In our case reason for this error was using multiple statements in one, like this:

  stmt := dbConn.NewStatementPrepared('set ROWCOUNT 1 '
    +' select * from yourtable order by id desc '
    +' set ROWCOUNT 0 ', false, true);
  stmt.ExecutePrepared;

First call to BeginTransaction after this query always generates above error. In this case we could easily change query to use "select top 1 * from yourtable order by id desc" instead.

Hope this saves someone's time.

dc

ab · 2014-05-20 13:17:43

Only single statement execution is supported yet.

Multiple statement execution is a feature not implemented in all libraries yet, so we stick to the simplest model, that is one SQL statement per SynDB statement.

dc · 2014-05-22 12:28:56

ab wrote:

Only single statement execution is supported yet.
Multiple statement execution is a feature not implemented in all libraries yet, so we stick to the simplest model, that is one SQL statement per SynDB statement.

Hmm... maybe it's not supported but it works and we are still using old version from march or april 2013. We use other multiple statements in different places and it doesn't produce any transactions errors later. Probably mORMot just works well with MS SQL . Anyway my post was just to let other people know what might be the reason for this error.

dc

mORMot Open Source

#1 2013-03-19 14:29:01

Transactions in SQL Server

#2 2013-03-19 16:19:46

Re: Transactions in SQL Server

#3 2013-03-19 16:47:54

Re: Transactions in SQL Server

#4 2013-03-19 21:09:06

Re: Transactions in SQL Server

#5 2013-03-19 23:44:03

Re: Transactions in SQL Server

#6 2013-03-20 06:09:54

Re: Transactions in SQL Server

#7 2013-03-20 10:40:29

Re: Transactions in SQL Server

#8 2013-03-20 10:50:37

Re: Transactions in SQL Server

#9 2013-03-20 12:48:51

Re: Transactions in SQL Server

#10 2013-03-20 12:59:15

Re: Transactions in SQL Server

#11 2013-03-21 13:55:03

Re: Transactions in SQL Server

#12 2013-03-21 15:00:18

Re: Transactions in SQL Server

#13 2013-03-22 08:20:58

Re: Transactions in SQL Server

#14 2013-03-22 08:46:42

Re: Transactions in SQL Server

#15 2013-03-22 09:25:24

Re: Transactions in SQL Server

#16 2013-03-22 13:23:35

Re: Transactions in SQL Server

#17 2013-03-22 14:06:00

Re: Transactions in SQL Server

#18 2014-05-20 11:03:50

Re: Transactions in SQL Server

#19 2014-05-20 13:17:43

Re: Transactions in SQL Server

#20 2014-05-22 12:28:56

Re: Transactions in SQL Server

Board footer