Bug #45908 start transaction should fail if a transaction from the same session is active

• Submitted: 2 Jul 2009 12:13
• Modified: 2 Jul 2009 12:21
• Reporter: Peter Lorenz
• Status: Not a Bug
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: CM-Server 5.1
• OS: Any
• CPU Architecture: Any
• Tags: commit, rollback, transaction

[2 Jul 2009 12:13] Peter Lorenz

Description:
"start transaction" from sql
should fail if a transaction from the same session is still active.

On other database engine I get an error like "you have already started an transaction tracking".
So I have to commit or rollback befor I can start an other transaction from this session.

How to repeat:

Open querybrowser (@1) for transaction test
Open an other querybrowser (@2) for viewing data in table.

@1: do an "start transaction" 
@1: insert an record

(@2: view contend of table with other view, sure, you don't see the inserted record because it's not committed)

@1: do a second "start transaction"

(@2: view contend of table with other view and look what happened )

@1: insert an record other record
@1: do an "commit work"

Suggested fix:

give back an error like "you have already started an transaction tracking".

[2 Jul 2009 12:21] Valeriy Kravchuk

Sorry, but current behaviour is intended and properly documented. Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/commit.html:

"Beginning a transaction causes any pending transaction to be committed."

[6 Jul 2009 15:30] Sergei Golubchik

btw, from the sql standard (2003, part 2, 16.1 <start transaction statement>, general rules):

1) If a <start transaction statement> statement is executed when an SQL-transaction is currently active, then an exception condition is raised: invalid transaction state - active SQL-transaction.