E
E
Eugene Ordinary2017-05-22 18:50:31
PHP
Eugene Ordinary, 2017-05-22 18:50:31

Is it correct to use LOCK TABLES READ to preserve data integrity in this case?

A topic is created and a message is added to the topic. The parent of the message is the number of the newly created topic:

mysqli_query("LOCK TABLES `themes_table` READ;"); 

mysqli_query( "INSERT INTO `themes_table` ( ... ;" );

$new_theme_id = mysqli_insert_id();

mysqli_query( "INSERT INTO `posts_table` ( `parent_theme_id`=$new_theme_id, ... ;" );

mysqli_query("UNLOCK TABLES;");

I want to avoid the case when, before inserting the message, another process creates a topic and the insert_id is incorrect. Do I understand correctly that after a LOCK TABLES `themes_table` READ lock, another process will not be able to write a new theme to `themes_table` until that process UNLOCK TABLES?

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Melkij, 2017-05-22
@melkij

insert_id will be wrong

Impossible. For any recording intensity. The value of last insert id is session value, not global value.
Wrap it in a transaction - then no one will see the partially recorded transaction until the commit. If we talk about transactional things, of course, and not some myisam.

A
Anton Anton, 2017-05-22
@Fragster

Is mysqli_insert_id(); will return not the same id that was created in the current connection? And then the hypothetical other connection will not affect it in any way.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question