Use SQL to implement simple distributed locks

Use SQL to implement simple distributed locks
The main difference between distributed locks and common locks is that they participate in different nodes. Therefore, node failure and network failure must be considered. To clarify the key points of the problem, you can use different things, such as Redis and ZooKeeper. However, it is very easy to use SQL. The following uses PostgreSQL as an example to describe it.

1. Method 1: Use the exclusive session level consultation lock in PostgreSQL.
Pg_advisory_lock (key bigint)
Pg_advisory_unlock (key bigint)
Pg_try_advisory_lock (key bigint)

Http://www.postgres.cn/docs/9.4/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS-TABLE

The lock is session-level. Before the lock is released, the winner of the lock must keep holding the session, that is, the connection. Otherwise, the lock will be released.
This feature naturally solves the issue of lock release when the lock winner fails.
However, for locks that need to be held for a long time, it will produce persistent connections, while database connections are resource-consuming, and generally thousands of locks are larger, this is something to note.
Another issue that needs to be considered is that when the network or node fails, the two ends of the connection may not be immediately aware. Therefore, TCP KeepAlive is required, fortunately, both the PostgreSQL client and the server support this setting.
The following are server parameters:
Tcp_keepalives_idle
Tcp_keepalives_interval
Tcp_keepalives_count

2. Method 2: The term lock object is persistent. To prevent the client that obtains the lock from crashing, the lock cannot be released. Each lock has an expiration period.
In PostgreSQL, You can implement the following methods:

Create a table

1. Postgres = # create table distlock (id int primary key, expired_time interval, owner text, ts timestamptz );
   
2. CREATE TABLE
   
3. S = # insert into distlock (id) values (1 );
   
4. INSERT 0 1

Lock and renew

1. S = # update distlock set owner = 'node1', ts = now (), expired_time = interval '20 second' where id = 1 and (owner = 'node1' or owner is null or now ()> ts + expired_time );
   
2. UPDATE 1

If the client that obtains the lock needs to hold the lock for a long time, it must regularly execute the same method to renew the lock; otherwise, the lock will be lost.

In this case, the locks of other clients will fail.

1. S = # update distlock set owner = 'node2', ts = now (), expired_time = interval '20 second' where id = 1 and (owner = 'node2' or owner is null or now ()> ts + expired_time );
   
2. UPDATE 0

The lock is successfully obtained after the lock expires.

1. S = # update distlock set owner = 'node2', ts = now (), expired_time = interval '20 second' where id = 1 and (owner = 'node2' or owner is null or now ()> ts + expired_time );
   
2. UPDATE 1

Release lock

1. Postgres = # update distlock set owner = null, ts = now () where id = 1 and owner = 'node2 ';
   
2. UPDATE 1

3. In summary, it is not complicated to implement distributed locks Using relational databases. In particular, the above table-based locks and reliable HA deployment can ensure the persistence and no loss of lock information. However, table update locks are relatively heavy after all, it is not suitable for scenarios with extremely high lock performance requirements.