Is MERGE thread safe?

Help
emillg
2012-10-10
2014-01-19
  • emillg

    emillg - 2012-10-10

    If the table is empty, and two threads are trying to execute the MERGE statement against the same PK, is it safe?

    Thanks!

     
  • Fred Toussi

    Fred Toussi - 2012-10-10

    HSQLDB is always thread safe. Each thread must use a separate connection. In this case, one connection is put in wait until the other commits. Then the other connection fails if there is a PK violation.

     
  • emillg

    emillg - 2012-10-10

    Thank you for your quick reply, fredt. Just want to be more specific to be clear.

    Say, I have the following statement and table t is empty. There are two concurrent threads trying to execute this same sql.

    MERGE INTO t USING (VALUES(1, 'conference table'), (14, 'sofa'), (5, 'coffee table'))
       AS vals(x,y) ON t.id = vals.x
       WHEN MATCHED THEN UPDATE SET t.description = 'Updated'
       WHEN NOT MATCHED THEN INSERT VALUES vals.x, vals.y

    So the first thread will insert (1, 'conference table'), (14, 'sofa'), (5, 'coffee table') into the table, and the second thread will ALWAYS execute UPDATE and the result will be (1, 'Updated'), (14, 'Updated'), (5, 'Updated'). It will never happen that both threads are trying to execute INSERT.

    Is it correct?

     
  • Fred Toussi

    Fred Toussi - 2012-10-10

    MVCC and LOCKS (2PL) transaction models works slightly differently.

    With 2PL, the table is locked when the first statement starts. The lock is not released until the connection commits. Therefore the two statements are executed separately in succession.

    With MVCC two or more connections may start at the same time and attempt to insert. At some point, there will be a conflict due to the PK, and one of the statements will be restarted after the other connection has committed. At restart, the statement will update.

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks