Locking rows with MyISAM + PHP

I was trying to figure out how to solve a problem of locking a single row in MySQL with MyISAM (so without transactions and stuff like that). The solution proposed was like this:

$row = fetch_and_query("SELECT * FROM details WHERE RowStatus=0 AND TaskID=? LIMIT 1");
query("UPDATE details SET RowStatus=1 where TaskID=? and RowId={$row['RowId']}");

The problem with that is that it can break – when multiple users call the same row this way with SELECT at the same time before the UPDATE, a lot of them might get the same row.

The solution to that might be a little trick with semaphores I came up with. To do that we need to create a table called for example Semaphores with two columns – name (text) and state (int). And for our purpose we will create one row called details (name of table but to be honest might be anything) with the state of 0.

The idea is that I will try UPDATE this new table and see if the update succeeded (it does if the row actually gets updated – not just when there is no error) and then follow to the next steps. I’m updating the row ‘details’ with value 1 (if the update does not succeed I try to update it until it actually does). Then after all of these operations I needed locked I call UPDATE on the table again – this time to switch the state back to 0. This way only one script can be inside the important part of this algorithm, the rest need to wait for their turn.

Now for the code:

$semaphore = query("UPDATE Semaphore SET state = 1 WHERE name = 'details'");

while(affected_rows($semaphore) == 0) {
    $semaphore = query("UPDATE Semaphore SET state = 1 WHERE name = 'details'");
    sleep(20); // sleep to not query too often
    // You might want to do some break here after too many loops - to prevent deadlocks
}

$row = fetch_and_query("SELECT * FROM details WHERE RowStatus=0 AND TaskID=? LIMIT 1");
query("UPDATE details SET RowStatus=1 where TaskID=? and RowId={$row['RowId']}");

query("UPDATE Semaphore SET state = 0");

This is my answer from stackoverflow.com – http://stackoverflow.com/questions/21904826/mysql-simultaneous-access?noredirect=1#answer-21906090

MSSQL – How to check which queries have been executed – logging queries.

At work we’re using a polish software for our shop’s warehouse called WF-Mag. It’s based on MSSQL 2008 Express server and as some of you might know, this server doesn’t provide free profiler. So for logging purposes when you are using some software which doesn’t tell you what queries it executes and you want to know how it modifies the database (like in my case for synchronization with our PHP shop), you can use this query. It’s very handy, it shows all SELECTs, UPDATEs, INSERTs, DELETEs which were executed recently (and more). It just shows everything what happened in the database.

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

If you want the logging to be automatic, then you can just run some cron jobs which will just run this query and save the results to a file or to another database table.

Source: http://stackoverflow.com/questions/123781/logging-all-queries-on-a-sql-server-2008-express-database#answer-17819485