Question

Result of retrieving data from a locked table

5

What would happend if I would try to retrieve data from a table that is locked at the moment of retrieval (by for instance a scheduled import action)? I can imagine two things that could happend: The first retrieve never finishes as the table is unreachable The first retrieve returns 'NULL' as the table is unreachable Which one is correct (and if none, what would happend)?

asked 2009-08-28

Michel Vermeer

2 answers

0

If the data can't be retrieved at the moment of retrieval, the retrieve will return 'NULL'.

answered 2009-08-28

Fedor Klinkenberg

Jonathan Veldhuizen · Accepted Answer · 2009-08-28

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.