Databases
StackOverflow2013
Postsdbo
POconcurrent discarded mysql transaction without locks
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POconcurrent discarded mysql transaction without locks
    primarykey
    data
    text
    <p>I'm currently trying to improve the testing behaviour of our application.</p> <p>The tests are currently started within nested transactions, where the test suite start the actual transaction and the test cases each create nested savepoints.</p> <p>When a test case is finished the savepoint is simply released and the original data is restored. When the suite is finished the transaction is rolled back.</p> <p>(Using truncation on the tables is not an option for us, since the seed data is just to big and it prevents central and concurrent tests)</p> <p>This works fine.</p> <p>When the transaction run concurrently, the transactions tend to lock each other out of tables. This happens even more so on tables with an unique key (every test fails for one of the two transactions).</p> <p>I tried to do this with the transaction states "READ COMMITTED", "READ UNCOMMITED" and the default "REPEATABLE READ".</p> <p>READ UNCOMMITTED resulted in duplicate key issues. READ COMMITTED has lock / deadlock issues REPEATABLE READ has lock / deadlock issues (same as above)</p> <p>Additionally i tried to apply the following options: innodb_autoinc_lock_mode=2</p> <pre><code>&gt; -- mysql command line &gt; SET SESSION binlog_format=ROW &gt; SET SESSION innodb_table_locks=OFF </code></pre> <p>The last parameter solved some locking issues. The "lock wait timeout" and deadlock issues remain.</p> <p>Is there a solution the make seperate transactions truly independent of each other?</p> <p>update: further testing showed: The deadlocks currently only happen on tables with unique keys. I think session 1 already used those keys, so i might be a victim of row locking.</p> <p>mysql server version: 5.5.15</p> <p>Edit: adding workflow example, so the problem is clearer stated, (might contain typos and minor syntax errors. those are not the point). different connections differently intended</p> <pre><code>&gt; create table foo ( &gt; `id` int(11) auto increment, &gt; `name` varchar(255), &gt; PRIMARY KEY id, &gt; UNIQUE KEY `name`(`name`) &gt; ) ENGINE=InnoDb; &gt; insert into foo(name) values ('bar'); &gt; begin; -- connection 1, transaction 1 &gt; begin; -- connection 2, transaction 1 &gt; savepoint t_1; -- connection 1, savepoint within transaction 1 &gt; savepoint t_1; -- connection 2, savepoint within transaction 1 &gt; select count(*) from foo; -- connection 1, number of entries in foo, should be 1 &gt; insert into foo('name') values ('meh'); -- connection 1, insert into foo the unique fixed value 'meh', should work &gt; select count(*) from foo; -- connection 2, number of entries in foo, should be 1 &gt; insert into foo('name') values ('meh'); -- connection 2, insert into foo the unique fixed value 'meh', should work &gt; select count(*) from foo; -- connection 1, number of entries in foo, should be 2 &gt; select count(*) from foo; -- connection 2, number of entries in foo, should be 2 &gt; rollback to savepoint t_1; -- connection 1, discard everything up until savepoint t_1 &gt; rollback; -- connection 1, rollback &gt; rollback to savepoint t_1; -- connection 2, discard everything up until savepoint t_1 &gt; rollback; -- connection 2, rollback </code></pre> <p>In this example all the <em>should be</em> are to be considered tests for the transaction isolation i want.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USscones
    1. USscones
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POconcurrent discarded mysql transaction without locks
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POconcurrent discarded mysql transaction without locks
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POconcurrent discarded mysql transaction without locks
      1. USscones
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload