How to get 1 record in Tarantool DB with uniq ganantee?

Question

I have table in DB my_table_ext [ID, CITY_ID, CTN, EXT, InUse]

Have data in

{1,'City1','001','1234',0}
{2,'City1','001','3456',0}

In MSSQL i have this SQL code to return unique record with update param InUse ( 0 >1 )

  update A set A.InUse=1 
    output inserted.ID,inserted.CTN,inserted.EXT
     into @ext (EXT_ID,CTN,EXT)
    from
     (select top 1 ID,CTN,EXT,InUse from
       my_table_ext with(readpast)
       where CITY_ID=@city_id  and InUse=0
       ) A
    where ID=A.ID

Question is - How repeat this in Lua/Tarantool?

Answer 1

In Tarantool you can execute SQL statements using box.execute(). The SQL dialect used in Tarantool is derived from SQLite, so subqueries like in your example are supported.

Unfortunately, the OUTPUT clause is not supported. You'll have to split your query into an UPDATE and SELECT. Since this is all in memory, the overhead won't be noticeable.

If you want those 2 queries to be atomic, you can do them both in a transaction. To learn more about MVCC in Tarantool, take a look at this answer: Can I yield during an transaction in Tarantool? .