2

I have a database on XAMPP and I use php in order to manipulate the database. I wish to read and write the database in concurrency way and each table are connected with the others. I don't understand if my approach is right.

When I wish to read I use this mode:

        $connection = new mysqli($host, $user, $password, $dbName);
        if (connection->connect_errno)
          return false;
        $connection->autocommit(true);
        $result = mysqli_query($connection, $query);
        if (!$result)
            return false;
        else
            return true;
       $connection->close();

And when I wish to write I use $connection->autocommit(false); with commit or rollback at the end.

But I wish to know if some users is reading and in this moment one or more people write on the database, does the database remain in right? And there is a way to put in an queue the write/read in order don't stop the user but putting in an spin wait?

I have also read that mysqli has mysqli_begin_transaction so I have thought that it is possibile to use mysqli_begin_transactionwhen I have to write, but I'm not sure that it is right.

Have you some ideas in order to read and write on the database in concurrency way without to stop any transaction but put in an spin wait queue?

I have thought a way like mutex like the c with some lock (I wish to implement at the end this):

  • I wish to write:

    1. I wait the lock in order to write and to block the other user;
    2. I lock the database and the others remains on spin wait;
    3. Here to check using php if the new records are right doing new query into the database but the database is locked only for user not for checking.
    4. Write database;
    5. unlock database.

  • When I wish to read:

    1. I wait if somebody finish to write;
    2. I read;
user9659243
  • 117
  • 1
  • 2
  • 11
  • I don't think that you want to do that. What if a write operation takes 1 hour? Nobody will be able to read the db for 1 hour? What if a read operation takes 1 hour? – aletzo Aug 07 '18 at 15:18
  • It is an idea in order to solve the problem. In my case doesn't matter, but I'm searching some thing in order to always have right database also during the writing. I'm afraid that two people can write in same time. In addition before to write I have to check some contains (that stay on database) using php – user9659243 Aug 07 '18 at 15:24
  • What MySQL database type are you using – RiggsFolly Aug 07 '18 at 15:27
  • This: https://www.apachefriends.org/it/index.html – user9659243 Aug 07 '18 at 15:28
  • No I mean INNODB or MYISAM – RiggsFolly Aug 07 '18 at 15:28
  • Two people writing at the same time is a different problem. You can use locks and transactions for that, as you said, but there's no need to block the read operations. – aletzo Aug 07 '18 at 15:29
  • It is MySQL Apache server – user9659243 Aug 07 '18 at 15:29
  • RiggsFolly refers to MySQL storage engines: https://stackoverflow.com/questions/4233816/what-are-mysql-database-engines – aletzo Aug 07 '18 at 15:31
  • I don't know the `what are MySQL database engines` – user9659243 Aug 07 '18 at 15:34
  • But if a people reads and the same time another writes the same record? – user9659243 Aug 07 '18 at 15:34
  • The database servers have already solved that problem for us :) – aletzo Aug 07 '18 at 15:35
  • I have this problem: I wish to insert a record but before I have to check some contains using query php on database. If the contains are respect I can insert or update the new record. I can do it with commit and autocomit? Or I do use the lock? – user9659243 Aug 07 '18 at 15:37
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/177586/discussion-between-aletzo-and-user9659243). – aletzo Aug 07 '18 at 15:46

0 Answers0