The second one is always incorrect. No database will ever return NULL when the table is locked.
The behavior depends on the database server and whether you run your retrieve action within a greater transaction. Some databases lock the whole table and the retrieve action waits to return before the lock is released. Other databases return the data in the table in the state it had before the transaction was started which causes the lock. In that case, you get an older version of the data.
Sometimes, you have a row in a table and in a long during transaction you delete this row. When you retrieve the data in this table before this transaction is successfully ended, you get this row. The row is earlier deleted, but this deletion is not 'committed', so before the transaction is ended, the row still exist. For some databases, your retrieve action is blocked and you don't get results until the first transaction is ended.
As I say, all this behavior depends on the database server and the way the Mendix runtime handles transactions with the database. There is no 'one' answer.
If the data can't be retrieved at the moment of retrieval, the retrieve will return 'NULL'.