9671

PHP PDO Transaction with nested PHP check

Question:

i have to do 2 MySql-queries:

<ol><li>

SELECT id FROM X WHERE [...]

</li> <li>

INSERT [...]

</li> </ol>

The second query should only be executed, if the first query returns an correct id.

Is it possible, to mix PHP conditions between both queries?

Eg.

try { $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $dbh->beginTransaction(); $stmt = $dbh->prepare("SELECT id FROM [...]"); $stmt->bindParam(1, [...]); if($stmt->execute()) { if($row = $stmt->fetch()) { $matchID = $row['id']; $checkD = $this->checkId($matchID); if($checkD) { return '-1'; } else { $stmt = $dbh->prepare("INSERT INTO [...]"); $stmt->bindParam(1,[...]); $stmt->execute(); stmt = $dbh->prepare("DELETE [...]"); $stmt->bindParam(1,[...]); $stmt->execute(); $dbh->commit(); return $matchID; } } else { return '-1'; } } else { return '-1'; } } catch(Exception $e) { $dbh->rollBack(); return '-1'; }

Is this correct? (i get zero errors) If not: how can i realize it?

I want to be sure, that no other user could reach the INSERT query, when annother is performing the 1. query.

Answer1:

Transactions are isolated from the current data. How they behave exactly is dependent on the <a href="http://www.postgresql.org/docs/9.1/static/transaction-iso.html" rel="nofollow">isolation level</a> they use. For example a transaction with serializable isolation level completely lives in the past, and it knows nothing of the data changes have been made since the beginning of the transaction.

If you want to prevent anybody to do changes on the database while your script is working on something, then you have to <a href="http://dev.mysql.com/doc/refman/5.0/en/locking-issues.html" rel="nofollow">lock</a> your database, tables or rows. This is usually not necessary, with the proper code.

In your case

<ul><li>you can use read committed transaction isolation level (default) call the DELETE after the SELECT, and check whether there are affected rows by the DELETE before the INSERT</li> <li>if you don't want to change the order of your queries then you can <ul><li>simply throw an exception if the DELETE does not affect any row, and so the INSERT will be rolled back</li> <li>add a constraint which prevents multiple INSERTs with the same data, so the duplicated INSERT will violate a constraint, and it will be rolled back</li> <li>lock the rows with a <a href="http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html" rel="nofollow">SELECT FOR UPDATE</a></li> </ul></li> </ul>

Answer2:

This rather makes no sense. there is no use for transactions at all. What you want to roll back? a SELECT query result?

<blockquote>

I want to be sure, that no other user could reach the INSERT query, when another is performing the 1. query.

</blockquote>

This is achieved via table locking, not transactions. Or, rather by the simple key uniqueness. Without knowing your business logic it's impossible to answer more. Better ask another question, explaining user experience, and avoiding technical terms like "transaction" at all.

Recommend

  • Using linked list in C
  • Concurrency scenarios with INSERTs
  • Bootstrap ScrollSpy skips section when no more scrollable area
  • No System image available when adding AVD in Android studio
  • How to get style in Elm
  • what does the follow prolog codes do?
  • link library to all targets in cmake project
  • Hadoop shuffle uses which protocol?
  • Sql Server 2008 sp_executesql syntax help - I think my quotes are not correct
  • How to move axis labels automatically in rgl R
  • Entity Framework unable to delete database, database in use
  • FTS3 searches in ORMLite?
  • Authentication in Play! and RestEasy
  • How to revert to previous XCode version?
  • Make VS2015 use angular-cli ng at build time in a .NET project
  • Retrieving value from sql ExecuteScalar()
  • Getting last autonumber in access
  • Where to put my custom functions in Wordpress?
  • Eraser for UIBezierPath
  • How to limit post in wp_query
  • Upload files with Ajax and Jquery
  • Build own AppleScript numerical error handling
  • Delete MySQLi record without showing the id in the URL
  • GridView Sorting works once only
  • Numpy divide by zero. Why?
  • AngularJs get employee from factory
  • Java static initializers and reflection
  • Exception on Android 4.0 `android.os.StrictMode$AndroidBlockGuardPolicy.onNetwork(StrictMode)`
  • Change div Background jquery
  • Authorize attributes not working in MVC 4
  • Bitwise OR returns boolean when one of operands is nil
  • Is there any way to bind data to data.frame by some index?
  • Django query for large number of relationships
  • Busy indicator not showing up in wpf window [duplicate]
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • How can I use `wmic` in a Windows PE script?
  • Python/Django TangoWithDjango Models and Databases
  • Net Present Value in Excel for Grouped Recurring CF
  • How to push additional view controllers onto NavigationController but keep the TabBar?
  • playing video using jmf