Assertions may be reused & dead lock

[Class-public]

Preflight checklist

• I could not find a solution in the existing issues, docs, nor discussions.
• I agree to follow this project's Code of Conduct.
• I have read and am following this repository's Contribution Guidelines.
• I have joined the Ory Community Slack.
• I am signed up to the Ory Security Patch Newsletter.

Ory Network Project

No response

Describe the bug

1、Assertions may be reused. In most databases, the CURRENT_TIMESTAMP function returns the current database system time, which is usually the local time based on the time zone of the operating system where the database server is located, rather than UTC time.
2、Using assertions in exchange for tokens, a deadlock problem occurs in this interface under concurrent conditions. The transaction isolation level of the database is read committed.
dead lock info:

2024-03-18 10:17:28 2163520 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2024-03-18 10:17:28 2163520 [Note] InnoDB:
*** (1) TRANSACTION:

TRANSACTION 44843587, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 2163522, OS thread handle 140058828142144, query id 18313939954 192.169.56.182 xxx Updating
DELETE FROM hydra_oauth2_jti_blacklist   WHERE nid = '5a91dfdb-c4e9-11ee-a7e2-5a07348f807e' AND expires_at < CURRENT_TIMESTAMP
2024-03-18 10:17:28 2163520 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 903 page no 5 n bits 80 index hydra_oauth2_jti_blacklist_nid_fk_idx of table `hydra_v2`.`hydra_oauth2_jti_blacklist` trx id 44843587 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 35613931646664622d633465392d313165652d613765322d356130373334; asc 5a91dfdb-c4e9-11ee-a7e2-5a0734; (total 36 bytes);
 1: len 30; hex 343865653031333365366632643331653363313930373035363563633362; asc 48ee0133e6f2d31e3c19070565cc3b; (total 64 bytes);

2024-03-18 10:17:28 2163520 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 44843599, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 2163520, OS thread handle 140063380387392, query id 18313939978 192.169.56.182 xxx Updating
DELETE FROM hydra_oauth2_jti_blacklist   WHERE nid = '5a91dfdb-c4e9-11ee-a7e2-5a07348f807e' AND expires_at < CURRENT_TIMESTAMP
2024-03-18 10:17:28 2163520 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 903 page no 5 n bits 80 index hydra_oauth2_jti_blacklist_nid_fk_idx of table `hydra_v2`.`hydra_oauth2_jti_blacklist` trx id 44843599 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 30; hex 35613931646664622d633465392d313165652d613765322d356130373334; asc 5a91dfdb-c4e9-11ee-a7e2-5a0734; (total 36 bytes);
 1: len 30; hex 343865653031333365366632643331653363313930373035363563633362; asc 48ee0133e6f2d31e3c19070565cc3b; (total 64 bytes);

2024-03-18 10:17:28 2163520 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 903 page no 3 n bits 80 index PRIMARY of table `hydra_v2`.`hydra_oauth2_jti_blacklist` trx id 44843599 lock_mode X locks rec but not gap waiting
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 30; hex 343865653031333365366632643331653363313930373035363563633362; asc 48ee0133e6f2d31e3c19070565cc3b; (total 64 bytes);
 1: len 30; hex 35613931646664622d633465392d313165652d613765322d356130373334; asc 5a91dfdb-c4e9-11ee-a7e2-5a0734; (total 36 bytes);
 2: len 6; hex 000002ac4243; asc     BC;;
 3: len 7; hex 5a000001432c12; asc Z   C, ;;
 4: len 4; hex 65f741c8; asc e A ;;

2024-03-18 10:17:28 2163520 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

func (p *Persister) SetClientAssertionJWT(ctx context.Context, jti string, exp time.Time) (err error) {
	ctx, span := p.r.Tracer(ctx).Tracer().Start(ctx, "persistence.sql.SetClientAssertionJWT")
	defer otelx.End(span, &err)

	// delete expired; this cleanup spares us the need for a background worker
	if err := p.QueryWithNetwork(ctx).Where("expires_at < CURRENT_TIMESTAMP").Delete(&oauth2.BlacklistedJTI{}); err != nil {
		return sqlcon.HandleError(err)
	}

	if err := p.SetClientAssertionJWTRaw(ctx, oauth2.NewBlacklistedJTI(jti, exp)); errors.Is(err, sqlcon.ErrUniqueViolation) {
		// found a jti
		return errorsx.WithStack(fosite.ErrJTIKnown)
	} else if err != nil {
		return err
	}

	// setting worked without a problem
	return nil
}

Reproducing the bug

1、Set the system time zone to Asia/Shanghai，Create assertionStr1 and exchange token，Create assertionStr2 and exchange token，then you will find that the records related to assertionStr1 in the hydra_oauth2_jti_blacklist table have been deleted，then you can use assertionStr1 again in exchange for the token.
2、Concurrent Execution: Assertion in Exchange for Token

Relevant log output

No response

Relevant configuration

No response

Version

v2.1.2

On which operating system are you observing this issue?

None

In which environment are you deploying?

Kubernetes with Helm

Additional Context

No response