3

I'm writing a programm which inserts data to a MariaDB-Server and can be used by different people on the same time. The transactions take some time, so the following problem might occur: Person A starts a transaction with primary key "c" and while the transaction is still uncommitted, Person B wants to insert data with the same primary key "c". How can I prevent that B can start its transaction with a primary key that A already uses in its uncommitted transaction?

I use MariaDB as database and InnoDB as Engine.

I've checked the Isolation-Levels but couldn't figure how to use them to solve my Problem.

Thanks!

Lukas
  • 381
  • 3
  • 13

3 Answers3

4

It has nothing to do with transaction isolation levels. It's about locking.

Any insert/update/delete to a specific entry in an index locks that entry. Locks are granted first-come, first-serve. The next session that tries to do an insert/update/delete to the same index entry will be blocked.

You can demo this yourself. Open two MySQL client windows side by side.

First window:

mysql> START TRANSACTION;
mysql> INSERT INTO mytable SET c = 42;

Then don't commit yet.

Second window:

mysql> INSERT INTO mytable SET c = 42;

Notice that it hangs at this point, waiting for the lock.

First window:

mysql> commit;

Second window finally returns:

ERROR 1062 (23000): Duplicate entry '42' for key 'PRIMARY'
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
2

Every table should have a PRIMARY KEY. In MySQL, the PRIMARY KEY is, by definition, UNIQUE.

You can also have UNIQUE keys declared on the table.

Each connection should be doing this to demark a transaction:

BEGIN;
various SQL statements
COMMIT;

If any of those SQL statements inserts a row, it uses the unique key(s) to block others from inserting the same unique value into that table. This will lead to some form of error -- deadlock (fatal to the transaction), "lock wait timeout" -- which it might recover from, etc.

Note: If you have any SELECTs in the transaction, you may need to stick FOR UPDATE on the end of them. This signals what rows you might change in the transaction, thereby giving other connections a heads-up to stay out of the way.

Can you find out if any of this is going on? Not really. But why bother? Simply plow ahead and do what you need to do. But check for errors to see if some other connection prevented you from doing it.

Think of it is "optimistic" coding.

Leave the isolation level alone; it only adds confusion to typical tasks.

Rick James
  • 135,179
  • 13
  • 127
  • 222
0

Primary keys are internal values that ensure uniqueness of rows and are not meant to be exposed to the external world.

Generate your primary keys using IDENTITY columns or using SEQUENCEs. They will handle multiple simultaneous inserts gracefully and will assign each one different values.

Using IDENTITY:

CREATE TABLE house (
  id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  address VARCHAR(40) NOT NULL
);

INSERT INTO house (address) VALUES ('123 Maple Street');

Using a SEQUENCE:

CREATE SEQUENCE myseq1;

CREATE TABLE house (
  id INTEGER NOT NULL PRIMARY KEY,
  address VARCHAR(40) NOT NULL
);

INSERT INTO house (id, address) VALUES (NEXT VALUE FOR myseq1, '123 Maple Street');
Joe DiNottra
  • 836
  • 8
  • 26