MySQL Transactions

[Necuima]

Hi Larry, am enjoying the book a lot! But I'd like to ask a question re transactions.

I have a PHP module which keeps track of the visitors to a website. Specifically it checks to see if the IP address of the visitor is in a database table, and if it is, it increments the access count for that IP address and records the date and time.  If the IP address is not already in that database table, it adds it.  I am getting an occasional error where the select does not indicate that the IP address is in the database table but when the code tries to add it, it is already there (the IP address is 'UNIQUE' in the database table).

I have checked the logs and this seems to occur when the same IP address attempts to visit the database several times in rapid succession though it does not seem to be a DOS attack.

So, if I used transactions, would this prevent two very-close-in-time database selects/inserts happening - i.e., would it ensure that the transactions for one visitor cannot interfere with transactions from another visitor, or even the same visitor close in time?  Maybe MySQL does this anyway?

Does the MySQL table engine need to be InnoDB?

Thanks, and Cheers from Oz.

P.S., the production environment uses MariaDB 10.0.34

Edited June 9, 2018 by Necuima
Added a P.S.

[Necuima]

Hi Larry,

I think that I now understand this a bit better.  I tried using table locks and that seems to work. Specifically:

$q103a = "LOCK TABLES visitors WRITE";            //only this session can read and write to the visitors table
    $r103a = $pdo101->prepare($q103a);
    if (!$r103a->execute())
     {   .................................

then at the end

$q103b = "UNLOCK TABLES";
    $r103b = $pdo101->prepare($q103b);
    if (!$r103b->execute())
     {  .............................

But I needed to ensure that the privileges included 'lock'!

Am I on the right track?

Thanks, and Cheers

 

[Larry]

Yeah, so locking the tables would work. I have a little bit of concern about the performance hit that may cause, but using a write lock helps and you probably won't have enough traffic that it's a concern. I don't have a better idea off the top of my head. Kudos for solving this so expertly!

[Necuima]

Hi Larry,

Thanks for your replies to my numerous posts!

In this instance, traffic volumes should be quite low so I'm not concerned about performance in this case.

Cheers from Oz.