SQL and locking strategies

[lvca]

It's time to start discussing about the record locking strategies we'll support in OrientDB. I prefer to open this thread to all the users here, instead of talk about it internally in Orient Technologies, because I'm confident we could receive good idea and contributions.

Even tough Optimistic approach (CAS like) is great for many use cases, sometimes users need a more "Pessimistic" approach by locking resources. This is the case of persistent queues or any other operation I need to be sure no other thread/client is fetching the same record.

Few of the issues related to this topic:

• Pessimistic TX: Support for pessimistic Transaction [moved] #12
• Non-TX Atomic ops: Support atomic operation (separate from ACID) [moved] #1056

Now we've the ORecordLockManager that extends the OLockManager (that has been developed more than 2 years ago, so probably it would need a refresh about better synchronization policies?) that does the job: locking RID in read or write mode.

We already use it internally, but I'd like to let to the user to expressly lock resources. I don't know if I'd like the classic RDBMS way to just use the transaction (or some variant of SQL like SELECT FOR UPDATE) or better to offer a more explicit way to lock resources giving more control/power to developers.

Below my initial proposal.

Support LOCK/UNLOCK directly in SELECT, UPDATE AND DELETE

Examples:

SELECT FROM Client WHERE city.name = 'London' LOCK SHARED
UPDATE Client SET local = true WHERE city.name = 'London' LOCK EXCLUSIVE
DELETE FROM Client WHERE local = true LOCK EXCLUSIVE

New SQL LOCK command

Syntax:

LOCK <cluster-name|rid|(select-statement)> SHARED|EXCLUSIVE

New SQL UNLOCK command

Syntax:

UNLOCK <cluster-name|rid|(select-statement)|*> SHARED|EXCLUSIVE

Where:

• "*" means release all the acquired locks

Automatic releasing of locks

Locks are kept by ODatabase instance that represents a client connection. Once the server disconnects a database it should close it. The ODatabase.close() should free all the acquired locks.

Real use cases: FIFO queue management

Even though we could provide better way to manage a queue, this would work. Push a message into a queue (class Queue):

INSERT INTO Queue SET payload = 'yeah', date = sysdate()

Pop the first message (returning is a new keyword part of the issue #1056):

DELETE FROM Queue ORDER BY date LIMIT 1 LOCK exclusive RETURNING before

This is also related to the issue #1056

Full thread on:
https://groups.google.com/forum/#!searchin/orient-database/Record$20Locking/orient-database/fD-UNnq9zmc/Gw1DiV6zK1EJ

[lvca]

Started working on the branch "newlocks"

[lvca]

After a reconsideration about pros/cons I think the most common use cases are:

• UPDATE records avoiding conflicts
• DELETE records avoiding conflicts
• PUSH/POP ELEMENT in atomic way

By letting to permanently lock records even outside the command is dangerous and requires more work to assure all is unlocked upon the client connection is closed. So I'd like to introduce this changes:

UPDATE ... [STRATEGY <NONE|LOCK|RETRY <TIMES>>]

Where:

• NONE: no lock, like now where the MVCC raises an exception and client app has to manage it
• LOCK: while query the record keep an EXCLUSIVE LOCK to release after the update
• RETRY: no lock, but in case of MVCC exception retry times

And the same with DELETE command:

DELETE FROM ... [STRATEGY <NONE|LOCK|RETRY <TIMES>>]

[kowalot]

+1 (especially for command locks not connection scope)
How wide the lock is going to be for range queries? per row or whole operation? I vote for a row :)

Would be nice if pessimistic locking could be used in TX_COMMIT, RECORD_UPDATE. In addition with introducing partial update it would be a killer feature.

[lvca]

It will be ROW level lock. About pessimistic locking on other operation would be easy to implement once this issue is implemented. About PARTIAL UPDATE how to manage removal of one field?

[kowalot]

This is very good question :), seems that field removal would be tricky to define. Because it's rare case it always can be adressed by non-partial update.
The other option is to expand TX_COMMIT by PARTIAL_UPDATES chunks so whole COMMIT would consist of INSERTS,UPDATES,PARTIAL_UPDATES,DELETES
PARTIAL UPDATE chunk could be defined like sub-syntax of SQL UPDATE command. Couple of examples
SET Field='asdfasdf'
or
INCREMENT Field = 2
or
Remove field
or
ADD Collection #12:0
or even
ADD Collection #12:0
INCREMENT Field=2
Remove Field2
I guess it's easy to implement by current update executor.
Thanks to that, all cases are adressed, including field removal, collections expanding and so on.

[lvca]

So probably it could be done with SQL UPDATE command?

[kowalot]

I think so. I said sub syntax because SQL UPDATE can be range command with WHERE keywords and so on. In that case all what is needed is only functionality equivalent to one record modifications like:

UPDATE #rid
SET something,REMOVE ..,ADD...

[lvca]

This syntax is already supported

[kowalot]

Yep that's true, therefore it may be a base of partial update and make TX_COMMIT useable in terms of pessimistic locking in transactions.

[lvca]

Supported and tested only on UPDATE

[lvca]

I've also created a test to measure on high-concurrency the benefit of LOCKS:

• optimistic sql update (no lock) -> 700ms
• pessimistic sql update (lock) -> 125ms

The test raise 10 threads that execute 100 cycles each (1000 operation in total) of SQL UPDATE

[lvca]

The RETURNING part has been moved on issue #1056

[lvca]

I'm changing the keyword STRATEGY <NONE|LOCK> in LOCK <NONE|RECORD>

[lvca]

Ok, close this one and put the rename into #1056.

[giggs131400]

can the lock or unlock statement use in orientdb?