I am using $db->begin_transaction(MYSQLI_TRANS_START_READ_WRITE); to begin a transaction. Will this lock the tables involving in the transactions?
If Yes, what happens when simultaneous users are involved (multiple transactions at the same time).
PS: I don't want to lock any table.
Nothing of that kind happens on BEGIN TRANSACTION. It just means: "start recording of what I like to do".
All this is then executed when you do the COMMIT or just discarded if you ROLLBACK.
If you have data changing queries in that transaction like INSERT, UPDATE or DELETE, the table locks will be issued as normal during the commit. On begin, the engine doesn't even know what you are going to do next, so it actually could not lock anything at that point.
When you write in table, the table always will be locked. This is to save database.
The transaction is a complete process of save o update data in your database. This is used to maintain the integrity and relation of the data.
For example: if a one query fail, the process maybe discard with "rollback" and go back to initial status. if process finished with success persist data with "commit".
Code example:
try
{
// Disable auto commit
mysqli_autocommit( $db_conn, FALSE );
$query = "TRUNCATE TABLE `table`;";
if( ! mysqli_query( $db_conn, $query ) )
{
throw new \Exception( "ERROR TRUNCATE: ".$query, 1 );
}
foreach( $data as $d )
{
$query = "INSERT INTO ....";
if( ! mysqli_query( $db_conn, $query ) )
{
throw new \Exception( "ERROR INSERT: ".$query, 2 );
}
}
// Success finish
mysqli_commit( $db_conn );
}
catch( \Exception $e )
{
echo $e->getMessage()."\n";
echo "errno: " . mysqli_errno( $db_conn ) . PHP_EOL . "\n";
echo "error: " . mysqli_error( $db_conn ) . PHP_EOL . "\n";
// Go back to initial status
mysqli_rollback( $db_conn );
}
