Row or Table Locking?

[chiefumpire]

Hi,

I have been searching for a while. The document only mentioned there is no locking mechanism for temp table.

Is that possible to lock a row or table? For example, SELECT ... FOR UPDATE.

Thanks

[rareddy]

Cheif,

In Teiid, in view layer the #temp tables are session scoped, so there is no chance separate two connections operating at same time. If are thinking about the locking the temp table in a source data base, then look at "native queries"  https://docs.jboss.org/author/display/TEIID/JDBC+Translator

Ramesh..

[chiefumpire]

Hi Ramesh,

Sorry for the misleading. I just want to lock a normal table instead of a dynamic created temp table. Thanks for the "native queries" information and seems it can be a workaround for row locking by using "SELECT ... FOR UPDATE" as a native query. However, it would be troublesome as my Teiid is connected with multiple types of databases.

My situation is, I have to make sure updating a row that will not overwrite other's update. The row can only be updated with new values when the values in database is not updated by others.

Does Teiid support row level locking? such as "SELECT ... FOR UPDATE"

Thanks

[rareddy]

Teiid does not own any data itself, it is just virtual layer on top of the sources, there is no locking of the view concept in the Teiid view layer.  Even if Teiid did, there is no restriction that another external process can not update source table out side the scope of Teiid process. You need to look into optimistic locking scenarios with timestamps or version numbers etc to avoid the concurrent updates though Teiid, or use native queries if you need pessimistic.

Ramesh..

[chiefumpire]

Thanks for answering. Ramesh,

I have an idea that use repeatable-read as the isolation level to overcome problem. But it cannot be changed at runtime.

https://docs.jboss.org/author/display/teiid830final/Limitations+and+Workarounds

Native queries seems to be the final answer as I can't change the table structure.

Anthony