[MDEV-10561] Transactions not ACID compliant? Created: 2016-08-15  Updated: 2016-10-17  Resolved: 2016-10-17

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.0.20
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Volker Cordes Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: need_feedback
Environment:

debian jessie


 Description   

Hello,

I ran into a problem when testing MariaDB with my application. Transactions do not work as expected. To reproduce I opened two connections to the database and entered the following statements:

  1. connection 1: START TRANSACTION
  2. connection 2: START TRANSACTION
  3. connection 1: SELECT * FROM table WHERE field1=1; # SHOWS field2=0
  4. connection 2: SELECT * FROM table WHERE field1=1; # SHOWS field2=0
  5. connection 1: UPDATE table SET field2=1 WHERE field1=1;
  6. connection 2: UPDATE table SET field2=1 WHERE field1=1; # this shows 0 rows affected
  7. connection 1: COMMIT;
  8. connection 2: COMMIT; # this should fail if I understand ACID right

Am I missing something or is this a bug?

Regards,
Volker

 Comments   
Comment by Daniel Black [ 2016-08-16 ]

This looks like you are using MyISAM for the table type. MyISAM isn't a transactional database and it would exhibit the behaviour you describe. Try again using innodb. 

ALTER TABLE table ENGINE=Innodb

Comment by Elena Stepanova [ 2016-08-16 ]

vc1979,

I agree with danblack, what you've described, if we take it literately, is not InnoDB behavior.
The problem in your scenario is not even at the step 8 (why do you think it should fail?), but at step 6. This UPDATE shouldn't show anything at this point, it should wait for the row lock – that is, until step 7 in your scenario is executed. If you insist it's not so, and you are using InnoDB, please do the following:

  • enable general_log (set global general_log=1);
  • re-establish connection1 and connection2;
  • run SHOW CREATE TABLE table from one of connections;
  • execute your scenario step by step;
  • close connections;
  • paste the full unabridged output from both connections, starting from the moment of connecting, and till the end;
  • attach the general log and your cnf file(s) or the output of SHOW VARIABLES.

Thanks.

Comment by Volker Cordes [ 2016-08-17 ]

Hello,

I'm using innodb for all my tables. I expected step 8 to fail, because I was thinking that with optimistic locking the first transaction to commit wins and for later transactions with conflicts the commit fails.
I tested a little further and discovered that setting isolation_level to SERIALIZABLE fixes the issue for me (I expected transactions to be serializable by default). So I assume that for other isolation levels this is expected behaviour. If not, I can provide the requested output.

Volker

Comment by Elena Stepanova [ 2016-09-19 ]

My previous comment still applies. In your scenario, with the default transaction isolation level REPEATABLE-READ (and other two levels, for that matter), UPDATE on step 6 should wait for COMMIT from the other connection, rather then return immediately as you describe. At least that's how it works for me on a simple table with two fields and one row.

If it's not so, please provide the structure and the contents of your test table (table dump if possible, if not then the output of SHOW CREATE TABLE and SELECT * FROM table if there are only few rows, or SHOW TABLE STATUS). Please also attach your cnf file(s) or the output of SHOW VARIABLES from both connections which you use for your test.

With SERIALIZABLE it should also work differently from the initial scenario that you described – not COMMIT on step 8 would fail, but UPDATE on step 6.

Comment by Elena Stepanova [ 2016-10-17 ]

Please comment to re-open if you have further information on the issue.

Generated at Thu Feb 08 07:43:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.