Don't use multiple UNIQUE constraints; it will cause deadlocks

2 files changed, 8 insertions, 6 deletions

diff --git a/synapse/storage/schema/full_schemas/11/presence.sql b/synapse/storage/schema/full_schemas/11/presence.sql
index 273e61281a..00d803a5cd 100644
--- a/synapse/storage/schema/full_schemas/11/presence.sql
+++ b/synapse/storage/schema/full_schemas/11/presence.sql
@@ -17,20 +17,22 @@ CREATE TABLE IF NOT EXISTS presence(
   state VARCHAR(20),
   status_msg VARCHAR(150),
   mtime BIGINT, -- miliseconds since last state change
-  UNIQUE(user_id)
+  UNIQUE (user_id)
 ) ;
 
 -- For each of /my/ users which possibly-remote users are allowed to see their
 -- presence state
 CREATE TABLE IF NOT EXISTS presence_allow_inbound(
   observed_user_id VARCHAR(150) NOT NULL,
-  observer_user_id VARCHAR(150) -- a UserID,
+  observer_user_id VARCHAR(150) NOT NULL, -- a UserID,
+  UNIQUE (observed_user_id, observer_user_id)
 ) ;
 
 -- For each of /my/ users (watcher), which possibly-remote users are they
 -- watching?
 CREATE TABLE IF NOT EXISTS presence_list(
   user_id VARCHAR(150) NOT NULL,
-  observed_user_id VARCHAR(150), -- a UserID,
-  accepted BOOLEAN
+  observed_user_id VARCHAR(150) NOT NULL, -- a UserID,
+  accepted BOOLEAN NOT NULL,
+  UNIQUE (user_id, observed_user_id)
 ) ;
diff --git a/synapse/storage/schema/full_schemas/11/users.sql b/synapse/storage/schema/full_schemas/11/users.sql
index 0271de3526..ba0f42d455 100644
--- a/synapse/storage/schema/full_schemas/11/users.sql
+++ b/synapse/storage/schema/full_schemas/11/users.sql
@@ -35,8 +35,8 @@ CREATE TABLE IF NOT EXISTS user_ips (
     device_id VARCHAR(150),
     ip VARCHAR(150) NOT NULL,
     user_agent VARCHAR(150) NOT NULL,
-    last_seen BIGINT NOT NULL,
-    UNIQUE (user, access_token, ip, user_agent)
+    last_seen BIGINT NOT NULL
 ) ;
 
 CREATE INDEX IF NOT EXISTS user_ips_user ON user_ips(user);
+CREATE INDEX IF NOT EXISTS user_ips_user_ip ON user_ips(user, access_token, ip);